SQL syntax 2

[momon]

I have the following

Set rs = CurrentDb.OpenRecordset("SELECT [DRAWING NUMBER] FROM CORE WHERE [DRAWING # PREFIX]=" & "" & Me!prefix2 & "&quot

It gives the error:

---
Run-time error '3075'

Syntax error (missing operator) in query expression '[DRAWING # PREFIX]=C1234 A 0056'.
---

[DRAWING # PREFIX] is text
[DRAWING NUMBER] is a long integer

Does anyone know how to fix this?
Thanks

 

[jrbarnett]

I think that this is because the # character is being interpreted in the VBA code as a date/time delimiter.
Possible workarounds are to create a query and save it, then open the query from the recordset instead, or rename the column in the table (not sure how much work that would be, as it depends on being used elsewhere).

John

 

[momon]

Thanks John

I also thought that # character was interpreted as some type of delimiter.

But it's funny that this code works.

Set rs = CurrentDb.OpenRecordset("SELECT [DRAWING # PREFIX] FROM CORE WHERE [DRAWING NUMBER]=" & Me!prefix2 )

Notice that I have switched the location of [DRAWING # PREFIX] and [DRAWING NUMBER].

I can't change the name of the field, because of dependecies. I don't want to use queries either. The form is unbounded.

More suggestions please.

 

[TonyJollans]

Hi momon,

You are missing some quotes. You want the SQL to be:

[DRAWING # PREFIX]="C1234 A 0056"

so you need to add some more in your VBA string:

Set rs = CurrentDb.OpenRecordset("SELECT [DRAWING NUMBER] FROM CORE WHERE [DRAWING # PREFIX]=" & """" & Me!prefix2 & """&quot

Enjoy,
Tony

 

[momon]

yay tony... you fixed it...