Use of variables in SQL

[A. Turtle]

Will someone please help with a definitive answer as to how to indicate that that a word in an Update query is a variable, and that it is the content of that variable that is to be written to the required field?

I have a variable, strThisUser (the current user's name) - that I want to store in a field called Created_By in a table called tblOrg_Name. The variable exists, and holds the required value as proved using Debug.Print strThisUser. When the user name is stored I also want to store the current date and time in the field Created_On.

I tried to do this using:

DoCmd.RunSQL "UPDATE tblOrg_Name " & _
"SET " & _
"tblOrg_Name.Created_On = Now(), " & _
"tblOrg_Name.Created_By = strThisUser;"

and got a syntax error in update statement.

I removed the line with Now(), and still got the error. I replaced the line with Now() and removed the line with strThisUser, and it worked OK, apart from not storing the user name of course. The error is in the way I'm using the variable, it seems

Reading through various posts on this forum, and others, produced a bewildering array of things, and combinations of things, in which to enclose the variable name. These include parentheses, quotes single and double, square brackets, ampersands, chr(xx), plus a few others which I don't remember right now. I have tried all of them, and nothing works.

As I understand that different versions of Windows, and different versions of Access, can cause things to behave other than expected and these may need to be known, Access 2000 running under Windows 2003 server.

First aid for the skull damage caused by banging my head against the wall over this one would also be appreciated Not angry - bleeding!!

 

[KenHigg]

DoCmd.RunSQL is simply looking for a string so you just need to concatenate correctly:

DoCmd.RunSQL "UPDATE tblOrg_Name " & _
"SET " & _
"tblOrg_Name.Created_On = #" & Now() & "#, " & _
"tblOrg_Name.Created_By = '" & strThisUser & ";"

I think this is part of the problem...


ken

 

[Uncle Gizmo]

What about changing this :

"tblOrg_Name.Created_By strThisUser;"
to this:
"tblOrg_Name.Created_By = strThisUser;"

 

[A. Turtle]

To KenHigg

Many many thanks. That was one combination that I apparently hadn't tried during my perm(x) from y exercise. And - IT WORKS!! <VERY HAPPY GRIN>

To Uncle Gizmo

Ooops, it's that kind of a day. That was a typo, corrected later, but before I had updated my browser and seen your replies.

To both of you - thanks for the incredibly quick responses.

 

[KenHigg]

No problem. Send money.



ken

 

[A. Turtle]

No problem. Send money.



ken

Apologies Ken. It really is a bad day. I seem to have mislaid not only your address for the cheque, but your bank account details for credit transfer as well.

 

[KenHigg]

The account number is br549...

 

[boblarson]

The account number is br549...

Heee Heee Hawww Hawww

 

[KenHigg]

Carefull, you'll date yerself -

 

[boblarson]

Carefull, you'll date yerself -

And you didn't???

 

[A. Turtle]

The account number is br549...

That was a high one - I'm 6' 2" and it went right over my head!!

Maybe something to do with me living on the wrong side of the pond. I don't think that one has ever been shown over here.

 

[boblarson]

That was a high one - I'm 6' 2" and it went right over my head!!

Maybe something to do with me living on the wrong side of the pond. I don't think that one has ever been shown over here.

A couple of links if you are interested in finding out what it was:

http://www.tv.com/hee-haw/show/1784/summary.html?tag=tabs;summary

http://en.wikipedia.org/wiki/Hee_Haw

 

[A. Turtle]

A couple of links if you are interested in finding out what it was:

http://www.tv.com/hee-haw/show/1784/summary.html?tag=tabs;summary

http://en.wikipedia.org/wiki/Hee_Haw

Google gave me the wiki one, and also http://www.br549.org/ otherwise I'd have had no idea.

What did we do before the web and search engines existed?

 

[boblarson]

Google gave me the wiki one, and also http://www.br549.org/ otherwise I'd have had no idea.

What did we do before the web and search engines existed?

Go uninformed

Yes, I like having instant info at my fingertips.

 

[pbaldy]

To address the conceptual question, you basically want to include literal portions of the SQL inside the quotes, and things that have to be interpreted for their values (variables, form references) outside the quotes. Then you have to remember the bit about surrounding text values with single quotes and dates with #.

 

[A. Turtle]

To address the conceptual question, you basically want to include literal portions of the SQL inside the quotes, and things that have to be interpreted for their values (variables, form references) outside the quotes. Then you have to remember the bit about surrounding text values with single quotes and dates with #.

Thanks for the explanation but, if I can be forgiven for saying so, it does seem like complexity for complexity's sake. Other RDBMS's seem to manage with less convoluted methods - iirc.

 

[pbaldy]

if I can be forgiven for saying so

NO! Stone the heretic!