Syntax Error

[lovelydan]

im trying to run this code from a button but i get an error "syntax error(missing operator)in a query expression": where do i go wrong?

DoCmd.OpenForm "frmNotes", , , "SerialNumber='" & Me.SerialNumber.Value & "'" & "ntsTime='" & Me.ntsTime.Value & "'"

[Rabbit]

If you evaluate your filter string, you get

SerialNumber='some serial number'ntsTime='some time'

[lovelydan]

Yes... the code brings the error with the correct result but wont open the form im trying to filter due to the error.

[Rabbit]

Right, so you need to fix your filter string.

[lovelydan]

an example will do or are you saying the code is okay?

[NeoPa]

Neither. He's saying that he's highlighted the issue and you now need to resolve it. If you can see nothing obviously wrong with the string value in post #2 then you need to find out some very basics. The Help system is where you should be looking for that. See Context-Sensitive Help.

[Frinavale]

Excuse me for asking (this is not my area of expertise) but isn't the problem with the "where" parameter (as opposed to the filter parameter)

Just looking at the MSDN article about the OpenForm Method (Access 2003 VBA Reference)...it looks like the 4th parameter is the "where condition".

The "where" condition is incorrect.
You need a SQL operator for the where condition...

For example:

SerialNumber='some serial number' AND ntsTime='some time'

Here's another example:

SerialNumber='some serial number' OR ntsTime='some time'

The AND and the OR part of the where clause are operators :)

There are other operators that you can use...look up the SQL Where clause for more info.

[Rabbit]

Yes sorry, I meant where. I mix the two up sometimes.

[NeoPa]

Actually, the WhereCondition parameter is also known as the Filter string. This should not be confused with the FilterName parameter of course, which is a reference to a filtering query instead.

[lovelydan]

this works fine when tested:

Public Sub frmNotes()
DoCmd.OpenForm "frmNotes", , , "ntsTime=#" & Me.ntsTime.Value & "#"
End Sub

this also works when tested separately:

DoCmd.OpenForm "frmNotes", , , "equipGovSerialNumber='" & Me.equipGovSerialNumber.Value & "'"

now the problem comes when im trying to mix them together like:

DoCmd.OpenForm "frmNotes", , , "equipGovSerialNumber='" & Me.equipGovSerialNumber.Value & "'" AND "ntsTime=#" & Me.ntsTime.Value & "#"

i get a (Type mismatch)error, im a learner and this is confusing me.

[Narender Sagar]

Type mismatch means : when you are comparing/joining any two tables, the "data type" must be same (You can not join "Text" data type with numeric Data Type.)
So please check Data type of joining fields.

[lovelydan]

okay... here i want to filter a form by Time(ntsTime) and serial number is Text data type and thats where i get stuck with the coding the two in one code/Line.

[NeoPa]

That sort of response is fine Dan. It has errors, but clearly you've made an attempt from what's been posted already.

Your (technical) mistake was to leave the "AND" part of the string outside of the string itself (See How to Debug SQL String for tips on how to avoid such issues in future).

Your code should look like :

Public Sub frmNotes()
    Dim strWhere As String
 
    strWhere = "([ntsTime]=#" & Me.ntsTime & "#) AND " & _
               "([equipGovSerialNumber]='" & Me.equipGovSerialNumber & "')"
    DoCmd.OpenForm "frmNotes", , , strWhere
End Sub

[lovelydan]

thanks Neopa.. its works wonders, i didn't even change a thing. thanks again. till we meet again. my regards