Question Syntax Error

[teric2]

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]

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]

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

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

 

[Mark_]

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

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]

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]

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]

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.

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

 

[teric2]

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