String Problem Using QueryDef

[prgmrgirl]

Ok, here's an interesting little problem (I'm using Access 2000).

I have a string variable named strAcctKey. I am trying to create a querydef that I can reuse because I need to create a loop and the value of strAcctKey changes as the loop goes through the records. Here's what I have:

Set qdf = dbs.CreateQueryDef("qryCustItem", _
SELECT * qrySomeQuery WHERE AcctKey = " & strAcctKey)

Unfortunately, what it's doing is it's putting the value of strAcctKey in the criteria but not using QUOTES so Access is saying "Wrong Datatype, Dummy!"

Can anyone tell me how I can get the &!%*&@! thing to put the quotes in and run the query?

Thanks!

prgmrgirl

 

[johnwm]

Try:
SELECT * qrySomeQuery WHERE AcctKey = '" & strAcctKey) & "'"

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[prgmrgirl]

Hi Johnwm,

Tried it to no avail. I'm getting a compile error (syntax error).

When I do it the regular way {ie: SELECT * qrySomeQuery WHERE AcctKey = " & strAcctKey} I get "Run-Time Error 3464, Data type mismatch in criteria expression."

Could it be a referencing issue? All the books I've looked at have the SQL statement set up exactly as I do....I'm totally bef*ckled.

prgmr girl

 

[PHV]

Have you tried this ?
Set qdf = dbs.CreateQueryDef("qryCustItem", _
"SELECT * FROM qrySomeQuery WHERE AcctKey='" & strAcctKey & "'")

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi prgmrgirl,

It is a quotes problem ..

[blue]Set qdf = dbs.CreateQueryDef("qryCustItem", _
    [highlight]"[/highlight]SELECT * qrySomeQuery WHERE AcctKey = " & strAcctKey)[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[prgmrgirl]

Yup, tried putting in " ' " (my spaces) but it doesn't work, I get a syntax error.

At wits end, pulling hair out! Argh.

prgmr girl

 

[PHV]

prgmrgirl, can you please post your code with syntax error ?
BTW, have you tried to copy/paste my last post ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[prgmrgirl]

Got it guys,

Thank you so much!!!

prgmr girl