UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA Between Query, Any Version    
 
   
mrdrjay
post May 19 2017, 05:13 AM
Post#1



Posts: 69
Joined: 27-June 14



Hello All,

Have a current issue and wanted your assistance with the following code:
CODE
"HAVING ((([TEAM LIST].[Required Date]) Between = '" & "[Forms]![LST_INDV]![Begin-Date]" And "[Forms]![LST_INDV]![End-Date]" & "'"") AND (([TEAM LIST].Received)<>-1));"
as when ran I get a Run Time Error 13 Code Type Mismatch error. How ever When I use the the between '#date#' and '#date#' it works. Request you assistance as I am not sure when the syntax error is. Thanks.
Go to the top of the page
 
bouncee
post May 19 2017, 06:29 AM
Post#2



Posts: 73
Joined: 5-August 05
From: Eastbourne, UK


You say it works when you use '#', so use that instead!

You also shouldn't have '=' after 'between'.
Go to the top of the page
 
orange999
post May 19 2017, 06:43 AM
Post#3



Posts: 1,709
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


I agree with bouncee. See https://support.office.com/en-us/article/Be...31-999432ae8223

--------------------
Good luck with your project!
Go to the top of the page
 
mrdrjay
post May 19 2017, 03:35 PM
Post#4



Posts: 69
Joined: 27-June 14



Thanks for the update.

When I use
CODE
"HAVING ((([TEAM LIST].[Required Date]) Between [Forms]![LST_INDV]![Begin-Date] And [forms]![LST_INDV]![End-Date]) AND (([TEAM LIST].Received)<>-1));"
I get a Run-Time error '3061' Too few parameters. Expected 2. Request you assistance as will like to use the forms to pull dates. However when I use static dates like
CODE
"HAVING ((([TEAM LIST].[Required Date]) Between ""2016-11"" And ""2017-05"") AND (([TEAM LIST].Received)<>-1));"
it works. Thanks

Go to the top of the page
 
mrdrjay
post May 19 2017, 03:35 PM
Post#5



Posts: 69
Joined: 27-June 14



Thanks for the update.

When I use
CODE
"HAVING ((([TEAM LIST].[Required Date]) Between [Forms]![LST_INDV]![Begin-Date] And [forms]![LST_INDV]![End-Date]) AND (([TEAM LIST].Received)<>-1));"
I get a Run-Time error '3061' Too few parameters. Expected 2. Request you assistance as will like to use the forms to pull dates. However when I use static dates like
CODE
"HAVING ((([TEAM LIST].[Required Date]) Between ""2016-11"" And ""2017-05"") AND (([TEAM LIST].Received)<>-1));"
it works. Thanks

Go to the top of the page
 
cheekybuddha
post May 20 2017, 05:43 AM
Post#6


UtterAccess VIP
Posts: 9,269
Joined: 6-December 03
From: Telegraph Hill


Hi mrdrjay,

A few questions:

1. Is this an aggregate query (contains a GROUP BY clause)?

2. What datatype is field [Required Date] in table [TEAM LIST]?

3. What exactly do you normally type in [Forms]![LST_INDV]![Begin-Date] and [forms]![LST_INDV]![End-Date]? Do you ever leave either of the controls empty?

4. Please post the whole SQL of the query.

tia,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mrdrjay
post May 20 2017, 06:59 AM
Post#7



Posts: 69
Joined: 27-June 14



Hi Cheekybuddha,

Please see the following:

1. Is this an aggregate query (contains a GROUP BY clause)? No

2. What datatype is field [Required Date] in table [TEAM LIST]? It contains YYYY-MM dates and is a text field.

3. What exactly do you normally type in [Forms]![LST_INDV]![Begin-Date] and [forms]![LST_INDV]![End-Date]? Those are fields that holds dates.

4. Please post the whole SQL of the query:
CODE
"SELECT [TEAM LIST].PLACE, [TEAM LIST].[TITLE], [TEAM LIST].Name, [TEAM LIST].[Required Date], [TEAM LIST].Reason, [Training_Update(Update)].[Email Address], Information.LEAD, Information.SEC_LEAD, Information.[ENG_LEAD(P)], Information.[ENG_LEAD(A)], Information.[UCC(P)], Information.[UCC(A)], Information.[LEAD-Email_Address(H)], Information.[SEC_LEAD-Email_Address(H)], Information.[ENG_LEAD-Email_Address(H)-P], Information.[ENG_LEAD-Email_Address(H)-A], Information.[UCC-Email_Address(H)-P], Information.[UCC-Email_Address(H)-A], [TEAM LIST].Received " & vbCrLf & _
"FROM Information RIGHT JOIN ([Training_Update(Update)] INNER JOIN [TEAM LIST] ON [Training_Update(Update)].Name = [TEAM LIST].Name) ON Information.[(R)PLACE] = [Training_Update(Update)].PLACE " & vbCrLf & _
"GROUP BY [TEAM LIST].PLACE, [TEAM LIST].[TITLE], [TEAM LIST].Name, [TEAM LIST].[Required Date], [TEAM LIST].Reason, [Training_Update(Update)].[Email Address], Information.LEAD, Information.SEC_LEAD, Information.[ENG_LEAD(P)], Information.[ENG_LEAD(A)], Information.[UCC(P)], Information.[UCC(A)], Information.[LEAD-Email_Address(H)], Information.[SEC_LEAD-Email_Address(H)], Information.[ENG_LEAD-Email_Address(H)-P], Information.[ENG_LEAD-Email_Address(H)-A], Information.[UCC-Email_Address(H)-P], Information.[UCC-Email_Address(H)-A], [TEAM LIST].Received " & vbCrLf & _
"HAVING ((([TEAM LIST].[Required Date]) Between [FORMS]![LST_INDV]![Begin-Date] And [forms]![LST_INDV]![End-Date]) AND (([TEAM LIST].Received)<>-1));"

Go to the top of the page
 
cheekybuddha
post May 20 2017, 08:17 AM
Post#8


UtterAccess VIP
Posts: 9,269
Joined: 6-December 03
From: Telegraph Hill


OK, more follow-up questions:

1. Is this an aggregate query (contains a GROUP BY clause)? No
OK, You use a 'HAVING' clause in your query - these are only used for aggregate queries. Really, you should use 'WHERE'. We can deal with that later.
*** EDIT *** Just studied your SQL more closely, and it is an aggregate query, though I'm not sure why!

2. What datatype is field [Required Date] in table [TEAM LIST]? It contains YYYY-MM dates and is a text field.
Just to confirm, this field is defined as 'TEXT' datatype in the table, and just contains strings formatted in YYYY-MM? It is not a Date datatype? Or is it a Date datatype simply formatted as YYYY-MM?

3. What exactly do you normally type in [Forms]![LST_INDV]![Begin-Date] and [forms]![LST_INDV]![End-Date]? Those are fields that holds dates.
Are these fields bound to a table? If so, are they Date datatype? Do they contain complete dates with the day portion, or are they in YYYY-MM format.
If the controls are not bound to table fields then do you just type in YYYY-MM free text? If so, do you ever leave either of these fields empty?

--------------------


Regards,

David Marten
Go to the top of the page
 
mrdrjay
post May 20 2017, 06:52 PM
Post#9



Posts: 69
Joined: 27-June 14



I have modified so it will have
CODE
"WHERE ((([TEAM LIST].[Required Date]) Between [FORMS]![LST_INDV]![Begin-Date] And [forms]![LST_INDV]![End-Date]) AND (([TEAM LIST].Received)<>-1)) " & vbCrLf & _


1. Is this an aggregate query (contains a GROUP BY clause)? No
OK, You use a 'HAVING' clause in your query - these are only used for aggregate queries. Really, you should use 'WHERE'. We can deal with that later.
*** EDIT *** Just studied your SQL more closely, and it is an aggregate query, though I'm not sure why!

Modified it to have the WHERE Clause.


2. What datatype is field [Required Date] in table [TEAM LIST]? It contains YYYY-MM dates and is a text field.
Just to confirm, this field is defined as 'TEXT' datatype in the table, and just contains strings formatted in YYYY-MM? It is not a Date datatype? Or is it a Date datatype simply formatted as YYYY-MM?

I change the [Required Date] field in the Table to a date field which put the dates from 2017-01 to 2017-01-01 and on the form to choose the between dates I change it from a textbox to a combo box and selected the distinct dates both in [Begin-Date] and [End-Date]. The is no format option for example Medium, short dates so I put @. In addition I select dates in both fields to give me my pool of records.

Thanks

Mr Drjay

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd November 2017 - 09:52 AM