Using the contents of a variable instead of the variable name itself

#11

Should have read a little closer! Rather than RunSQL, I find it's better to
use the Execute method;

CurrentDb.Execute "DELETE * FROM [tblQBFTemp] " & _
"WHERE [" & strFieldName & "] < " & lngFrom, dbFailOnError

See what Allen Browne has at http://www.allenbrowne.com/ser-60.html for a
discussion of why.

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: "Access Solutions", published by Wiley
(no e-mails, please!)

"PW" wrote in message news:er0p371fs16i3ksffss1oo6396htnj76ea (AT) 4ax (DOT) com...

On Fri, 5 Aug 2011 17:25:48 -0400, "Douglas J. Steele"
<NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote:

Quote:

picky
Just in case there's ever a change hat strFieldname might contain blanks,
it
would be safer to use

DoCmd.RunSQL "DELETE DISTINCTROW [tblQBFTemp].* FROM [tblQBFTemp]
WHERE [" & strFieldName & "] < " & lngFrom
/picky

lol! No, not picky! Good to know! I like to cover all bases too.

-paulw

Quote:

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: "Access Solutions", published by Wiley
(no e-mails, please!)

"Bob Quintal" wrote in message
news:Xns9F37C4981A1E0BQuintal (AT) 69 (DOT) 16.185.252...

PW <emailaddyinsig (AT) ifIremember (DOT) com> wrote in
news:u0pl37pvrlq32cas4036qhan81v1svcdiu (AT) 4ax (DOT) com:

Hi,

I want to delete some rows in a table depending on the contents of
a
string (which contains the field name in the table).

For Instance:

DoCmd.RunSQL "DELETE DISTINCTROW [tblQBFTemp].* FROM [tblQBFTemp]
WHERE &[strFieldName] < " & lngFrom

Obviously &[strFieldName] generates and error and if I use
[strFieldName] I will also get an error because strFieldName isn't
a
field in the table.

DoCmd.RunSQL "DELETE DISTINCTROW [tblQBFTemp].* FROM [tblQBFTemp]
WHERE " & strFieldName & " < " & lngFrom

#12

On Sat, 6 Aug 2011 16:57:30 -0400, "Douglas J. Steele"
<NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote:

Quote:

Should have read a little closer! Rather than RunSQL, I find it's better to
use the Execute method;

CurrentDb.Execute "DELETE * FROM [tblQBFTemp] " & _
"WHERE [" & strFieldName & "] < " & lngFrom, dbFailOnError

See what Allen Browne has at http://www.allenbrowne.com/ser-60.html for a
discussion of why.

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: "Access Solutions", published by Wiley
(no e-mails, please!)

I am not sure if I have tried the excecute method!

Thanks,

-paulw

Quote:

"PW" wrote in message news:er0p371fs16i3ksffss1oo6396htnj76ea (AT) 4ax (DOT) com...

On Fri, 5 Aug 2011 17:25:48 -0400, "Douglas J. Steele"
NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote:

picky
Just in case there's ever a change hat strFieldname might contain blanks,
it
would be safer to use

DoCmd.RunSQL "DELETE DISTINCTROW [tblQBFTemp].* FROM [tblQBFTemp]
WHERE [" & strFieldName & "] < " & lngFrom
/picky

lol! No, not picky! Good to know! I like to cover all bases too.

-paulw

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: "Access Solutions", published by Wiley
(no e-mails, please!)

"Bob Quintal" wrote in message
news:Xns9F37C4981A1E0BQuintal (AT) 69 (DOT) 16.185.252...

PW <emailaddyinsig (AT) ifIremember (DOT) com> wrote in
news:u0pl37pvrlq32cas4036qhan81v1svcdiu (AT) 4ax (DOT) com:

Hi,

I want to delete some rows in a table depending on the contents of
a
string (which contains the field name in the table).

For Instance:

DoCmd.RunSQL "DELETE DISTINCTROW [tblQBFTemp].* FROM [tblQBFTemp]
WHERE &[strFieldName] < " & lngFrom

Obviously &[strFieldName] generates and error and if I use
[strFieldName] I will also get an error because strFieldName isn't
a
field in the table.

DoCmd.RunSQL "DELETE DISTINCTROW [tblQBFTemp].* FROM [tblQBFTemp]
WHERE " & strFieldName & " < " & lngFrom

#13

PW <emailaddyinsig (AT) ifIremember (DOT) com> wrote in
news:sdhu37p0ekcn4823fpe9q49tcu5l8u8bel (AT) 4ax (DOT) com:

Quote:

I am not sure if I have tried the excecute method!

If you want to get away from DoCmd.RunSQL (and you should), Google
me and SQLRun(), which is intended as a near drop-in replacement for
DoCmd.RunSQL, and is based on the Execute method.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#14

On Wed, 10 Aug 2011 19:35:42 +0000 (UTC), "David-W-Fenton"
<NoEmail (AT) SeeSignature (DOT) invalid> wrote:

Quote:

PW <emailaddyinsig (AT) ifIremember (DOT) com> wrote in
news:sdhu37p0ekcn4823fpe9q49tcu5l8u8bel (AT) 4ax (DOT) com:

I am not sure if I have tried the excecute method!

If you want to get away from DoCmd.RunSQL (and you should),

Why?

Quote:

Google
me and SQLRun(), which is intended as a near drop-in replacement for
DoCmd.RunSQL, and is based on the Execute method.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Using the contents of a variable instead of the variable name itself

comp.databases.ms-access comp.databases.ms-access

Re: Using the contents of a variable instead of the variable name itself - 08-06-2011 , 03:57 PM

Re: Using the contents of a variable instead of the variable name itself - 08-07-2011 , 09:11 PM

Re: Using the contents of a variable instead of the variable name itself - 08-10-2011 , 02:35 PM

Re: Using the contents of a variable instead of the variable name itself - 11-23-2011 , 11:07 AM