Question Syntax Error (1 Viewer)

teric2 · Dec 5, 2017

Can someone tell me what's wrong with this?

strRowSource = "SELECT [colorant_Change_Date] FROM colorant_lot_Changes WHERE [Press_No]=' ' " & Me!Combo0 & """"
Me!Combo2.RowSource = strRowSource

[colorant_Change_Date] field is date/time format
[Press_No] field is numeric format

I'm getting "syntax error (missing operator) in query expression"

I'm using the after update event on a combo box to set the row source of another combo box on the same form.

Ranman256 · Dec 5, 2017

too many quotes:
strRowSource = "SELECT [colorant_Change_Date] FROM colorant_lot_Changes WHERE [Press_No]='" & Me!Combo0 & "'"

surround the string in single quotes.
Instead of SQL , you can just make a query and not need any quotes:

SELECT [colorant_Change_Date] FROM colorant_lot_Changes WHERE [Press_No]=forms!myForm!Combo0

Minty · Dec 5, 2017

Actually if [Press_No] is numeric you don't need the quotes at all:

Code:

strRowSource = "SELECT [colorant_Change_Date] FROM colorant_lot_Changes WHERE [Press_No]=" & Me.Combo0 & " ;"

Mark_ · Dec 5, 2017

In the future, to assist with debugging these types of issues, I'd use MSGBOX to show exactly what your SQL statement looks like

If you put

Code:

MsgBox "SQL is " & strRowSource

immediately after you created your SQL string you'd be able to see EXACTLY what is being passed. This helps when you notice you did/didn't format something and that is causing a problem.

teric2 · Dec 6, 2017

Thanks to all that replied.
I used Minty's solution and that worked.
Thank you so much Minty and thanks for the tip Mark!

teric2 · Dec 6, 2017

So now I'm trying to add a third combo box and set it's row source based on the other two.
[Colorant_Change_Time] is date/time format
[Colorant_Change_Date] is date/time format
[Press_No] is numeric

"SELECT [Colorant_Change_Time] FROM Colorant_Lot_Changes WHERE [Press_No]=" & Me.Combo0 & " & [Colorant_Change_Date]=" & Me.Combo2 & ""

Using Marks tip of using a msgbx to display the SQL it looks correct but I get a "data type mismatch" error.

Thanks!

Minty · Dec 6, 2017

Have a look at the links in my signature for DLookup methods, the syntax's are very similar.

Dates are delimited by # # marks and would need to be in mm/dd/yyyy format if you are building them as a vba string.

You also need to add an AND not an & to make the criteria correct for the query.

Code:

"SELECT [Colorant_Change_Time] FROM Colorant_Lot_Changes WHERE [Press_No]=" & Me.Combo0 & " AND [Colorant_Change_Date]= #" & format(Me.Combo2,"mm/dd/yyyy") & "#"

teric2 · Dec 6, 2017

Fantastic, got it straightened out using your info.
Thanks Again Minty!

Question Syntax Error (1 Viewer)

teric2

Registered User.

Ranman256

Well-known member

Minty

AWF VIP

Mark_

Longboard on the internet

teric2

Registered User.

teric2

Registered User.

Minty

AWF VIP

teric2

Registered User.

Similar threads

Users who are viewing this thread