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.

strFieldName = "postalcode" so I would expect:

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


I assume this is possible to do.

-paulw

PW wrote:
with the string, I'm not sure why you would expect that. It might b e what
you _wanted", but ...
I'm assuming lngFrom is also a variable ... ?

to be executed to a variable for easy debugging:

dim sSQL as string

'no need to qualify the wildcard as there's only one table
'in the FROM clause - no chance of ambiguity
'also, there is no point at all in that DISTINCTROW keyword

sSQL="DELETE * FROM [tblQBFTemp] WHERE " & _
strFieldName & "< " & lngFrom
debug.print sSQL
DoCmd.RunSQL sSQL:

Is postalcode really a numeric field? If not, then you need to delimit the
content of lngFrom:


sSQL="DELETE * FROM [tblQBFTemp] WHERE " & _
strFieldName & "< """ & lngFrom & """"
debug.print sSQL

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

WHERE " & strFieldName & " < " & lngFrom


--
Bob Q.
PA is y I've altered my address.

On Thu, 4 Aug 2011 19:16:08 -0400, "Bob Barrows"
<reb01501 (AT) NOSPAMyahoo (DOT) com> wrote:

I used the prefix lng for long variables, and str for string
variables. I loaded strFieldName with the contents of a text field in
the table that I am reading through. I assigned the contents of a
numeric field from that table to the declared variable lngFrom that
contains a numeric value.


I just don't get the syntax rational after all these years! The Getz
97 handbook continues to confuse me further!

Yes.


Thanks,

-pw

On Thu, 04 Aug 2011 23:19:33 GMT, Bob Quintal <rquintal (AT) sPAmpatico (DOT) ca>
wrote:

That works Bob. What would I do with an integer and especially a date
field?

Thanks!

-paul

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

do the same thing, basically.
The principle is to have the compiler concatenate the variable by
leaving it outside the quote marks
ex: Docmd Whatever "text" & variable & " moreText " & othervariable



--
Bob Q.
PA is y I've altered my address.

PW wrote:
I showed you what to do if postcode was a Text field: concatenate quotes
around the value.
For a date field, you have to concatenate octothorpes (#) around the value:

" ... WHERE datefield > #" & datval & "#"

On Fri, 05 Aug 2011 12:47:36 GMT, Bob Quintal <rquintal (AT) sPAmpatico (DOT) ca>
wrote:

I get so confused as when to use single quotes, double quotes, &,
etc...!!

Thanks,

-paulw

<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>



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:

WHERE " & strFieldName & " < " & lngFrom


--
Bob Q.
PA is y I've altered my address.

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

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

-paulw