Query to vba (1 Viewer)

giddyhead · May 22, 2017

Hello All,

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

Code:

"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

Code:

"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 · May 22, 2017

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 · May 22, 2017

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.

Code:

"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 · May 23, 2017

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 · May 23, 2017

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

giddyhead · May 23, 2017

Minty

The following is the procedure code:

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 · May 23, 2017

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 · May 23, 2017

giddyhead said:

Minty

The following is the procedure code:

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 · May 23, 2017

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

Code:

"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 & _

Query to vba (1 Viewer)

giddyhead

Registered User.

Ranman256

Well-known member

Orthodox Dave

Home Developer

giddyhead

Registered User.

Minty

AWF VIP

giddyhead

Registered User.

Cronk

Registered User.

isladogs

MVP / VIP

JHB

Have been here a while

Similar threads

Users who are viewing this thread