Defeated again by quotation marks in SQL Statement

[Frankenoid]

Arggghh! I'll never figure these things out!

I need to let our file clerk designate a "blank" designation for a client matter if necessary. I'm cool if its a brand new client; however, on an existing client, I need to determine if such a generic "blank" designation (a zero-length string) exists, before offering the option of creating one. I don't think I need to go into any more detail here.

The current version of the string generates a "syntax error in string"; other versions of the placement of single and double quotation marks have given either the same error, or a too few parameters error. This is what I have at this point:

strSQL = "Select * FROM tblCaseClient WHERE fldClient = " & Me.cboClient & " And fldCase = '" & """" & ""

The error message as displayed indicates that I do have a double-quote being read, but still says there is a syntax error.

HELP!!

 

[jimbrooking]

I might try replacing the phrase

" And fldCase = '" & """" & ""

with someting like

" And (Not IsNull(fldCase)) And (Len(fldCase)=0)"

Jim

 

[Frankenoid]

Nope... gives me a "too few parameters" error. But thanks for the try.

Any other suggestions? -- Franki

 

[Frankenoid]

Is replying to your own post like talking to yourself?

Never could get the SQL statement to work; even tried using a stored query, which would give me a too few parameters error (although the query itself would show the correct filtering). Weird.

Ended up using a variation on the Len(fldCase) idea, bypassing the SQL statement altogether. It works because the recordsource of cboCase is sorted by fldCase, which means if a blank entry exists, it's at the top:

intCase = Len(Me.cboCase.Column(1, 0))
If IsNull(Me.cboCase) = True And Me.cboCase.ListCount > 0 And intCase <> 0 Then....