Very very simple SQL in VB syntax question.

[Whizzo]

Hi folks;
I've only really started to use VB for important stuff quite recently, and SQL is still quite alien to me. I'm finding the syntax a real pain to get the hang of, at the moment it's just a case of shifting the inverted commas around and hoping for the best!

I have an OpenForm operation attached to the On Click property of a button. The WHERE clause generated by Access when you place a command button works fine. Here it is, I'm sure you're all familiar...

stLinkCriteria = "[CustIds]=" & Me![CustomerID]

Now, I want to add something to that clause along the lines of:

stLinkCriteria = "[CustIds]=" & Me![CustomerID] AND [CustomerName] Is Not Null

Its function is pretty straightforward, I don't feel I need to explain it to you lot but I'm having a very difficult time explaining it to Access! I keep getting Type Mismatch and "Access cannot find the field " | "..." errors, as well as plain old Syntax Errors. Can anyone help, or point me in the direction of a good resource?

Thanks!

[ADezii]

@Whizzo
Try:

stLinkCriteria = "[CustIds]= " & Me![CustomerID] & " And [CustomerName] Is Not Null"

[ChipR]

I can't seem to find a good guide that has everything together. Just remember to put & between every part of the string you are making. You have a string, a variable, and another string so:

stLinkCriteria = "[CustIds] = " & Me![CustomerID] & " AND [CustomerName] Not Null"

Remember the # around dates. For example:

stLinkCriteria = "[TransactionDate] = #" & txtDateBox & "#"

And remember ' ' around strings. Like:

stLinkCriteria = "[CustomerName] = '" & txtNameBox & "'"

You can also use "" in place of ' like:

stLinkCriteria = "[CustomerName] = """ & txtNameBox & """"

If you aren't using the debugger already, there is any easy way to check and make sure you're doing it right. Once you make the string, right before it's used, do:

MsgBox stLinkCriteria

That way you can see the resulting string, and know what you messed up if you're getting errors.

Also, + can be used to concatenate strings, but any string + null = null, while any string & null = that string still.

[Whizzo]

Great stuff guys, many thanks! It's a long way from

10 print "I rule!!"
20 goto 10
run

but I'm sure I'll get the hang of it!