How do I filter by "" in a vba SQL string?

Question

I need to filter by fields containing a zero length string.  I can make it work when writing the SQL in the SQL view of a query but I can't seem to get the syntax right when building the SQL in VBA - it never quite returns "" which is what works in the query.  Can someone help?  I've tried """" and '""' and "''" etc. Some combinations confuse the concatenation of the strings in vba too.

Answer 1

I need to filter by fields containing a zero length string.  I can make it work when writing the SQL in the SQL view of a query but I can't seem to get the syntax right when building the SQL in VBA - it never quite returns "" which is what works in the query.  Can someone help?  I've tried """" and '""' and "''" etc. Some combinations confuse the concatenation of the strings in vba too.

Probably the easiest thing to do is use single quotes (') to delimit the zero-length string inside the doubel-quoted string literal.  That avoids any confusion.  Something like this:

    Dim strSQL As String

    strSQL = "SELECT * FROM MyTable WHERE MyStringField = '';"

I put in the semicolon at the end of the statement, even though it's not required, just so you could clearly see the single-quotes.  Note that it may not be obvious that those are single-quotes, unless you display the statement in a fixed-pitch font.

---

Dirk Goldgar, MS Access MVP

Access tips: www.datagnostics.com/tips.html

Answer 2

Dirk

I could have sworn that I had tried that!  Smack on!  Well done!

Marked as answered.

Regards

Geoff

Answer 3

Hello, Bob

The entire code is very long as it is examining a lengthy login permissions criteria and then sorting the viewable information from the result.  However, the 'phrase' that is giving me grief is:

...AND ((T3_Company_Details.T3_AccManager Is Null) or (T3_Company_Details.T3_AccManager = 'DDO') or (T3_Company_Details.T3_AccManager ="")) AND...

This is a complete 'statement' within a lengthy WHERE clause.  Typed into the query like this, it works fine.

The vba to create above (apart from the double speech marks) looks like this (where 'Initials' is obviously a variable):

" AND ((T3_Company_Details.T3_AccManager Is Null) or (T3_Company_Details.T3_AccManager = '" & Initials & "') or (T3_Company_Details.T3_AccManager = ????))"

Thanks for your interest

Geoff

Answer 4

A great tool for converting SQL statement from the QBE to VBA can be found at http://allenbrowne.com/ser-71.html

That said if your SQL statement criteria is normally

    WHERE ((([YourFieldName])=""))

then the resulting VBA would be

    "WHERE ((([YourFieldName])=""""))"

I hope this helps,

Daniel Pineault

http://www.cardaconsultants.com

MS Access Tips and Code Samples: http://www.devhut.net

Answer 5

Hi

Can you post your VBA code and the SQL from the query?

---

Bob Fitz BizSoftware