How to set up a quoted blank in an SQL ststement

[mud2]

I want to delete records in a table whose Field3 is either blank, "", or " ".
The SQL view in a delete query is

DELETE Table1.*
FROM Table1
WHERE (((Table1.Field3)=(Chr(34) & " " & Chr(34))));

But This does not work in an Sql definition in VBA, i.e,

Dim strSql As String
StrSql = "DELETE Table1.* FROM Table1 WHERE (((Table1.Field3)=(Chr(34) & " " & Chr(34))));"

DoCmd.Run Sql strSql

doesn't recognize the quoted blank (" "). I've tried several Chr(34)'s and &'s, but my flailing attenmpts do not work.??

[xCav8r]

Try this ugly thing:

WHERE Table1.Field3 ='" & """" & " " & """" & "' OR Table1.Field3 ='" & """"" & "';"

[xCav8r]

Here are a two tips about quotation marks in VBA.

First, for SQL, you can make your code easier to read and reduce mistakes by creating two very simple functions for string and date/time criteria that enclose the criteria in single quotation marks and pound signs (#), respectively.

Second, triple quotation marks can be used at the beginning or end of a string as the equivalent of including Chr(34). For example: """John""" is the same thing as Chr(34) & "John" & Chr(34). It's quicker, obviously, but potentially confusing for anyone unfamiliar with it. I personally prefer to create constants for things like blank spaces, empty strings, single quotation marks, double quotation marks, pound signs, etc, because they make the code easier to read.

Those things said, assuming that Field3 is a text field, I think your problem was not enclosing your string criterion in single quotation marks. At least, that's my best guess until you give me more info.

[michelle]

Dear Mud2 and xCav8r,



This is not a critical note!



Blank for a field can also mean that it was never filled in before.

If you write down for example:

where Table1.Field3 = ''

all records with field Field3 what never filled in, will still exist!



Will you also remove these records use



where (Table1.Field3 IS NULL)



Nice regards,



Michelle.

[xCav8r]

It's a good suggestion to couple that expression with = " ". I probably shouldn't have assumed that mud2 was familiar with it either. At any rate, I find IsNull a bit buggy in queries, so I generally use redundant criteria (="") for peace of mind. Has anyone else experienced funky results using null in Access queries?