DLookup as an if condition - Access 2000 9.0

[Guest]

This worked for me in 2003 but isn't working in 2000. Here is what I'm
currently doing.

If strBatch = DLookup("Batch", "tblBatchIsIn", "Batch = '" & txtBatch & "'")
= "all" Then

Do this
Else
Do that
End If

In Access 2003 when I used the DLookup command in an IF Statement, it would
return the value and base it on the comparison, doing this in Access 2000
yields nothing.

What I need to do is have an If statement pull a value from a table and if
it's the correct match, go on to do the first part of the If statemnt.

The Batch value will be unique per Queried tabled, so if there is and all in
the table it will be the only one.

Is it possible to use an SQL statement in an IF statement? I didn't try it
because I know in order to get SQL statements and queries to work that you
have to use the DoCmd.RunSQL command and that I don't know how (if it's
possible) to return a value from an SQL statement to a variable jus for an IF
statement.

TIA.

 

[RobFMS]

Based on the way you entered the information into this posting, it is a
little unclear on the "code".

Try this instead:

Dim strBatch as String

strBatch =Nz( DLookup("Batch", "tblBatchIsIn", "Batch = '" & txtBatch &
"'") , "")

if strBatch = "all" Then
' do something

else
' do this instead

end if



HTH

--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

 

[Guest]

Regardless of Access version, your code is an error waiting to happen. If
the DLookup does not find a match, it returns Null. Only variant data types
can accept Null. You could either change the variable to a variant data
type, or use the Nz function to return a zero length string:

If strBatch = Nz(DLookup("Batch", "tblBatchIsIn", "Batch = '" & txtBatch &
"'"), "") = "all" Then
+
As to why it is not working in 2000, I could not tell you. It should work.
If there is some version difference I don't know about, you could always:

strAnotherVariable = Nz(DLookup("Batch", "tblBatchIsIn", "Batch = '" &
txtBatch & "'"), "" )
If strBatch = strAnotherVariable Then


Another tip. Instead of
DoCmd.RunSQL
use
CurrentDb.Execute(strSQL), dbFailOnError
It is much, much faster.