I want to delete all records where the state equals "MO" for instance
but yet I am getting states that people have left out. The field in
the database is a text field.

This is the line of code:

DoCmd.RunSQL "DELETE DISTINCTROW [tblQBFTemp].* FROM [tblQBFTemp]
WHERE " & [strFieldName] & " <> '" & strText & "'"
End If

In debug, strText = "MO" or whatever and the field "strFieldName" is a
text field in the database

I get lots of records in the table where the field contains "MO" but
yet I also get blank entries for that field.

What am I doing wrong this time :-)

-paulw

The SQL you have will delete all records where the state is NOT equal to
"MO" (for example), and this will include all records where strFieldName is
null, since that also meets the criterion you've set. Change the condition
from <> to = to delete records which match the string in strText.

I'm also puzzled as to why you include the DISTINCTROW clause in the SQL.
And, for simplicity, you don't need the table name to qualify the *. The
following should do what you want:
DoCmd.RunSQL "DELETE * FROM [tblQBFTemp]
WHERE " & [strFieldName] & " = '" & strText & "'"

Rob


"PW" <emailaddyinsig (AT) ifIremember (DOT) com> wrote

On Fri, 2 Sep 2011 16:09:00 +1000, "Rob Parker"
<NOSPAMrobpparker (AT) optusnet (DOT) com.au.FORME> wrote:

I *want* to delete all records that are not equal to what state the
user picked. That was on purpose. If the user selected "MO", why
would I want to delete those records??????

Something I probably found in a book years ago!

Simplicity is good!

I am still getting records where the state field is blank. Which was
the subject of this post.

Doing an additional delete where the field is null took care of the
problem.

DoCmd.RunSQL "DELETE * FROM [tblQBFTemp] WHERE IsNull(" &
[strFieldName] & ")"


Thanks

PW wrote:
Errrr .... Because of what you said here :-) :
DoCmd.RunSQL "DELETE * FROM [tblQBFTemp]
WHERE Nz(" & [strFieldName] & ",'" & _
strText & "') <> '" & strText & "'"

Untested. You need to set your code up so you can inspect the result of the
concatenation to verify that it is a valid sql statement when debugging. The
key to this is assigning the result of the concatenation to a variable:

dim sql as string
sql="DELETE * FROM [tblQBFTemp] " & _
WHERE Nz(" & [strFieldName] & ",'" & _
strText & "') <> '" & strText & "'"
debug.print sql
DoCmd.RunSQL sql

"Rob Parker" <NOSPAMrobpparker (AT) optusnet (DOT) com.au.FORME> wrote in
news:4e607305$0$13394$afc38c87 (AT) news (DOT) optusnet.com.au:

....since DISTINCTROW has no effect whatsoever on any SQL statement
that has only one table in the FROM clause.

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

On Fri, 2 Sep 2011 14:06:08 -0400, "Bob Barrows"
<reb01501 (AT) NOSPAMyahoo (DOT) com> wrote:

Gosh Darnit! The opposite of I wanted to say! Sorry about that!


Thanks Bob. What Rob suggested seems to be working so far.

-paul

On Sat, 3 Sep 2011 20:36:15 +0000 (UTC), "David-W-Fenton"
<NoEmail (AT) SeeSignature (DOT) invalid> wrote:


Thanks for setting me straight David. I must have somehow gotten that
from some book and I wasn't aware of why they were using it.

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

Or, the SQL dates back to Access 2 or before, which defaulted to
DISTINCTROW in all queries (unless you turned it off).

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

On Wed, 7 Sep 2011 23:39:18 +0000 (UTC), "David-W-Fenton"
<NoEmail (AT) SeeSignature (DOT) invalid> wrote:


That must be the reason. Not that I have been using Access since
before Access 97 (Is that 2?), but maybe the authors of the books I
have still use it.

Thanks