Query to vba

[giddyhead]

Hello All,

Request your help to use forms with where clause. When I use

"WHERE ((([CDB LIST].[Required Date]) Between #11/1/2016# And #5/1/2017#) AND (([CDB LIST].Received)<>-1)) " & vbCrLf & _ It works However when I use

"WHERE ((([CDB LIST].[Required Date]) Between #[Forms]![CDB_LIST_FORM]![Begin-Date]# And #[Forms]![CDB_LIST_FORM]![End-Date]#) AND (([CDB LIST].Received)<>-1)) " & vbCrLf & _

I get a run time error 3075. How can I modify this where clause to use forms? Thanks.

 

[Ranman256]

You don't use # sign Inside quotes.
Nor the Forms!form!field path. It must be outside the quotes to intret the value.
If you make this SQL a query you'll see it does not use quotes.
Try to use a query,not vb SQL, it never gets the SQL wrong.

Also, x<>-1 is also , x=false

 

[Orthodox Dave]

The # signs are put round expressions such as 02/01/2017 to force Access to treat it as a date. It only works that way round - if it is already a date in access (like your [Forms]![CDB_LIST_FORM]![Begin-Date]), there is no translation needed, so enclosing it in ## will produce an error.

In fact you can include the # inside a string - Microsoft give the example:
Forms!Employees.Filter = "[BirthDate] >= #1-1-60#"
in one of their language reference pages.

Your criteria expression just needs the #'s removed and it should work.

"WHERE ((([CDB LIST].[Required Date]) Between [Forms]![CDB_LIST_FORM]![Begin-Date] And [Forms]![CDB_LIST_FORM]![End-Date]) AND (([CDB LIST].Received)<>-1)) "

 

[giddyhead]

Thanks for the update. When I removed the #s and run the it I get a Run-Time error '3061': Too few parameters. Expected 2. I tried using = after between but was not sure if that was correct. In addition the code is in a module and part of a recordset. Thanks.

 

[Minty]

I think we need to see your whole procedure code to get the context correct.

 

[giddyhead]

Minty

The following is the procedure code:

"SELECT [CDB LIST].LOCATION, [CDB LIST].[TITLE], [CDB LIST].Name, [CDB LIST].[Required Date], [CDB LIST].Reason, [Training_Update(Update)].[Email Address], Information.T-LEAD, Information.A-LEAD, Information.[E-LEAD(P)], Information.[E-LEAD(A)], Information.[UCC(P)], Information.[UCC(A)], Information.[T-LEAD-Email_Address(H)], Information.[A-LEAD-Email_Address(H)], Information.[E-LEAD-Email_Address(H)-P], Information.[E-LEAD-Email_Address(H)-A], Information.[UCC-Email_Address(H)-P], Information.[UCC-Email_Address(H)-A], [CDB LIST].Received " & vbCrLf & _
"FROM Information RIGHT JOIN ([Training_Update(Update)] INNER JOIN [CDB LIST] ON [Training_Update(Update)].Name = [CDB LIST].Name) ON Information.[(R)LOCATION] = [Training_Update(Update)].LOCATION " & vbCrLf & _
"WHERE ((([CDB LIST].[Required Date]) Between Forms![CDB_LIST_FORM]![Begin-Date] And Forms![CDB_LIST_FORM]![End-Date]) AND (([CDB LIST].Received)<>-1)) " & vbCrLf & _
"GROUP BY [CDB LIST].LOCATION, [CDB LIST].[TITLE], [CDB LIST].Name, [CDB LIST].[Required Date], [CDB LIST].Reason, [Training_Update(Update)].[Email Address], Information.T-LEAD, Information.A-LEAD, Information.[E-LEAD(P)], Information.[E-LEAD(A)], Information.[UCC(P)], Information.[UCC(A)], Information.[T-LEAD-Email_Address(H)], Information.[A-LEAD-Email_Address(H)], Information.[E-LEAD-Email_Address(H)-P], Information.[E-LEAD-Email_Address(H)-A], Information.[UCC-Email_Address(H)-P], Information.[UCC-Email_Address(H)-A], [CDB LIST].Received;"

 

[Cronk]

I think you'll find that because the date field is in the selected list, the clause should be HAVING ..... between .....
and it should be after the group clause.

Paste the generated string into the sql view of the query design window.

Also, the vbCrlf is superfluous, and calling a table field 'Name' is not a good idea as that is a reserved word in Access.

 

[isladogs]

Minty

The following is the procedure code:

"SELECT [CDB LIST].LOCATION, [CDB LIST].[TITLE], [CDB LIST].Name, [CDB LIST].[Required Date], [CDB LIST].Reason, [Training_Update(Update)].[Email Address], Information.T-LEAD, Information.A-LEAD, Information.[E-LEAD(P)], Information.[E-LEAD(A)], Information.[UCC(P)], Information.[UCC(A)], Information.[T-LEAD-Email_Address(H)], Information.[A-LEAD-Email_Address(H)], Information.[E-LEAD-Email_Address(H)-P], Information.[E-LEAD-Email_Address(H)-A], Information.[UCC-Email_Address(H)-P], Information.[UCC-Email_Address(H)-A], [CDB LIST].Received " & vbCrLf & _
"FROM Information RIGHT JOIN ([Training_Update(Update)] INNER JOIN [CDB LIST] ON [Training_Update(Update)].Name = [CDB LIST].Name) ON Information.[(R)LOCATION] = [Training_Update(Update)].LOCATION " & vbCrLf & _
"WHERE ((([CDB LIST].[Required Date]) Between Forms![CDB_LIST_FORM]![Begin-Date] And Forms![CDB_LIST_FORM]![End-Date]) AND (([CDB LIST].Received)<>-1)) " & vbCrLf & _
"GROUP BY [CDB LIST].LOCATION, [CDB LIST].[TITLE], [CDB LIST].Name, [CDB LIST].[Required Date], [CDB LIST].Reason, [Training_Update(Update)].[Email Address], Information.T-LEAD, Information.A-LEAD, Information.[E-LEAD(P)], Information.[E-LEAD(A)], Information.[UCC(P)], Information.[UCC(A)], Information.[T-LEAD-Email_Address(H)], Information.[A-LEAD-Email_Address(H)], Information.[E-LEAD-Email_Address(H)-P], Information.[E-LEAD-Email_Address(H)-A], Information.[UCC-Email_Address(H)-P], Information.[UCC-Email_Address(H)-A], [CDB LIST].Received;"

You might find my VBA SQL converter useful. See this thread:

https://www.access-programmers.co.uk/forums/showthread.php?t=293372

 

[JHB]

The problem is the reference to the form in the query, you need to use the value from that form.

"WHERE ((([CDB LIST].[Required Date]) Between #[B][COLOR=Red]"[/COLOR][/B] [COLOR=Red][B]&[/B][/COLOR] [Forms]![CDB_LIST_FORM]![Begin-Date] [B][COLOR=Red]&[/COLOR][/B] [B][COLOR=Red]"[/COLOR][/B]# And #[B][COLOR=Red]" [/COLOR][COLOR=Red]&[/COLOR][/B] [Forms]![CDB_LIST_FORM]![End-Date] [B][COLOR=Red]&[/COLOR][/B] [B][COLOR=Red]"[/COLOR][/B]#) AND (([CDB LIST].Received)<>-1)) " & vbCrLf & _