DLookup Problem

  • Thread starter Thread starter Jone
  • Start date Start date
J

Jone

Hi,
How do I write two conditions in DLookup? I wrote this
*IsNull(DLookup("BookName", "Trans", "BookName='" & I & "'"))*
But this is only one condition I want that it should also have to check
IsNull(DLookup("MemberId", "Trans", "MemberId='" & Code & "'"))
Please Help!
 
Jone said:
Hi,
How do I write two conditions in DLookup? I wrote this
*IsNull(DLookup("BookName", "Trans", "BookName='" & I & "'"))*
But this is only one condition I want that it should also have to check
IsNull(DLookup("MemberId", "Trans", "MemberId='" & Code & "'"))
Please Help!
Click to expand...

Only one way to code that:

If IsNull(DLookup("BookName", "Trans", "BookName='" & I & "'")) Or _
IsNull(DLookup("MemberId", "Trans", "MemberId='" & Code & "'")) Then


There's no way to combine the two lookups. Both return a different field's
value. Both have individual selection requirements
 
Yes but how do I make that both have to be true? because this is a subform
and I have a member ID and a BookName I want to make a msgbox if both have
that value
 
Jone said:
Yes but how do I make that both have to be true? because this is a subform
and I have a member ID and a BookName I want to make a msgbox if both have
that value
Click to expand...

Just change the 'Or' to 'And'. The line following 'Then' will only execute
if both values are Null, so:

If IsNull(DLookup("BookName", "Trans", "BookName='" & I & "'")) And _
IsNull(DLookup("MemberId", "Trans", "MemberId='" & Code & "'")) Then
MsgBox "<whatever>"
End If

What the 'If' statement is saying is: "If the 1st expression evaluates to
True AND the 2nd expression evaluates to True Then execute the code
following"
 
I get this error,

Run-time error '3464':
Data type mismatch in criteria expression.

I think maybe because the first DLookuo is only a number field and the
second DLookup starts with letters, if thats the problem please tell me how
to change it ASAP
Thanks!
 
Jone said:
I get this error,

Run-time error '3464':
Data type mismatch in criteria expression.

I think maybe because the first DLookuo is only a number field and the
second DLookup starts with letters, if thats the problem please tell me
how
to change it ASAP
Thanks!
Click to expand...

If IsNull(DLookup("BookName", "Trans", "BookName=" & I)) And _
IsNull(DLookup("MemberId", "Trans", "MemberId='" & Code & "'")) Then
MsgBox "<whatever>"
End If
 
OF course if either BookName exists or MemberID exists then the section of
code will NOT execute.

I am a bit puzzled as to what the poster wants to accomplish. I might use the
one of the following.

IF DCount("*","Trans",""BookName=" & I & " and MemberId='" & Code & "'") = 0 Then
'No record exists with bookname equal to I and MemberID equal to code


IF DCount("*","Trans",""BookName=" & I & " OR MemberId='" & Code & "'") = 0 Then
'No record exists with bookname equal to I OR MemberID equal to code




John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
It does not work I get an error when I compile "syntax error

About the DLookup, I think it would work but I think because the first
DLookup (BookName) and the second DLookup (MemberID) is a text so maybe we
have to change something there

If IsNull(DLookup("BookName", "Trans", "BookName='" & I & "'")) And _
IsNull(DLookup("MemberId", "Trans", "MemberId='" & Code & "'")) Then
MsgBox "<whatever>"
End If
 
Sorry, I should have been clear that you would use onr or the other of the two
if statements, if you want to use both then you would need something more
along the lines of the following. Also, note that the newreader wrapped one
line into two lines.


IF DCount("*","Trans",""BookName=" & I & _
" and MemberId='" & Code & "'") = 0 Then
'No record exists with bookname equal to I and MemberID equal to code


ElseIF DCount("*","Trans",""BookName=" & I _
& " OR MemberId='" & Code & "'") = 0 Then
'No record exists with bookname equal to I OR MemberID equal to code

Else
'A record exists with bookname equal to I OR MemberID equal to code

End If

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
I get an error when I used
IF DCount("*","Trans",""BookName=" & I & _
" and MemberId='" & Code & "'") = 0 Then
'No record exists with bookname equal to I and MemberID equal to code

Compile error
"Expected: List separator or)"

Maybe if you have a minute or two you can contact me by e-mail at
smilebesthello at gmail dot com
Thaks Alot!
 
Back
Top