the correct way to pass Is Null back to an access query from VBA

[batwings]

Hi there

I am struggling with the correct syntax for using "Is Null" , "Is Not Null" and "" when using them in a variable to pass back to an Access Query

I have code, just a segment of a query below using "Is Not Null"

Dim strmystring As String
strmystring = "Is Not Null"

Only problem when I look at my Query in access is that in the criteria field the result is as below.

AND mytable.[field]= " & Chr(34) & strmystring & Chr(34) & ";"

Ends up like this "Is Not Null"

AND mytable.[field]= ' " &  strmystring & " ' ;"

Ends up as 'Is Not Null'

Access doesn't seem to like either of these, what am I doing wrong here.

Batwings

 

[boblarson]

Have you tried this:

AND mytable.[field]= " & strmystring & ";"

 

[batwings]

Bob

Thanks for the reply

I tried your suggestion but I may have messed up as i am receiving a run-time error '3075' now.

Here is the whole Select statement

I am trying to use Is Null, Is Not Null or "*" to produce results from queries where mytable.[field7] rows have Data, don't data and when the rows are mixed.

mySQL = "SELECT DISTINCT Mytable.[Field1], " & _
        "Mytable.[Field2], " & _
        "Mytable.[Field3], " & _
        "Mytable.[Field4], " & _
        "Mytable.[Field5], " & _
        "Mytable.[Field6], " & _
        "Mytable.[Filed7], " & _
        "Mytable.[Field8] " & _
        "FROM Mytable " & _
        "WHERE Mytable.[Field6]='OK' " & _
        "AND Mytable.[Field7]= " & strstatus & ";"

thanks

Batwings