If Then with DLookups - Not working

[Tim Miller]

The code below is working if I only have ONE of my DLookup variants in the
If Then. Either one.
As I have it below, using AND, It allows varExDate to be Not Null and it
works properly, but if varExBranch is Not Null, then it's Type Mismatch
regardless of whether or not varExDate is Null. (varExDate is a Date field
formated as yyyy / mm; varExBranch is a 2 digit text field)

I've tried many renditions of this code. I tried putting an If within If
(If Not IsNull(varExDate) then ... If Not IsNull(varExBranch) then... In
this case I had no response from the code at all. But again, if I only use
one IF (with either variant) then it works perfect.

I'd appreciate any help I could get!!


Dim strDate As String
Dim strBranch As String
Dim Message As Variant
Dim varExDate As Variant
Dim varExBranch As Variant

strDate = [cboYear] & " / " & [cboMonth]
strBranch = [Forms]![Varnet Numbers]![cboBranch]
varExDate = DLookup("[Month]", "Varnet Numbers", "[Month] = #" & strDate &
"#")
varExBranch = DLookup("[Branch]", "Varnet Numbers", "[Branch] = '" &
strBranch & "'")

If Not IsNull(varExDate And varExBranch) Then
Message = MsgBox("An entry for " & strBranch & " already exists for " &
strDate & ". Please check your date, or ask Tim for help in checking the
database for accuracy. This record has not been posted.", 0 + 16,
"Somethings screwed up")
Else
[Forms]![Varnet Numbers]![Month] = strDate
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord acForm, "Varnet Numbers", acNewRec
End If

 

[Sandra Daigle]

Hi Tim,

This expression:

If Not IsNull(varExDate And varExBranch) Then

is actually resulting in a bitwise And between varExDate and varExBranch and
then testing the result of the And for Null. It would work if the variants
were being cast as the same datatype but it fails because the first variant
is being cast as a date and the second is being cast as a string. Even if
the two were the same datatype, the logic is a little faulty because it just
so happens that when you perform any logical operation on a Null you get
Null (if you didn't get the datatype mismatch error).

The correct logical expression would actually be:

if not IsNull(varExDate) and not IsNull(varExBranch) then

Now, having said that and looking further at your code - based on your
error message you are trying to see whether there is a record that has both
the Branch and Date in question. This should all be done on a single Dlookup
statement. The way you have it, if there is any record for Branch, or any
record for ExDate, the message box will be displayed. I'm not certain that
that is what you intended.

Perhaps you should have one Dlookup that includes both fields as criteria.
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

The code below is working if I only have ONE of my DLookup variants
in the If Then. Either one.
As I have it below, using AND, It allows varExDate to be Not Null and
it works properly, but if varExBranch is Not Null, then it's Type
Mismatch regardless of whether or not varExDate is Null. (varExDate
is a Date field formated as yyyy / mm; varExBranch is a 2 digit
text field)

I've tried many renditions of this code. I tried putting an If
within If (If Not IsNull(varExDate) then ... If Not
IsNull(varExBranch) then... In this case I had no response from
the code at all. But again, if I only use one IF (with either
variant) then it works perfect.

I'd appreciate any help I could get!!


Dim strDate As String
Dim strBranch As String
Dim Message As Variant
Dim varExDate As Variant
Dim varExBranch As Variant

strDate = [cboYear] & " / " & [cboMonth]
strBranch = [Forms]![Varnet Numbers]![cboBranch]
varExDate = DLookup("[Month]", "Varnet Numbers", "[Month] = #" &
strDate & "#")
varExBranch = DLookup("[Branch]", "Varnet Numbers", "[Branch] = '" &
strBranch & "'")

If Not IsNull(varExDate And varExBranch) Then
Message = MsgBox("An entry for " & strBranch & " already exists
for " & strDate & ". Please check your date, or ask Tim for help in
checking the database for accuracy. This record has not been
posted.", 0 + 16, "Somethings screwed up")
Else
[Forms]![Varnet Numbers]![Month] = strDate
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord acForm, "Varnet Numbers", acNewRec
End If

 

[Tim Miller]

That was the ticket Jen....
Thank you very much!!

If I understand you correctly, try doing the following:


If Not IsNull(varExDate) And If Not IsNull(varExBranch)
Then
....do stuff
Else
....

End If

-----Original Message-----
The code below is working if I only have ONE of my DLookup variants in the
If Then. Either one.
As I have it below, using AND, It allows varExDate to be Not Null and it
works properly, but if varExBranch is Not Null, then it's Type Mismatch
regardless of whether or not varExDate is Null. (varExDate is a Date field
formated as yyyy / mm; varExBranch is a 2 digit text field)

I've tried many renditions of this code. I tried putting an If within If
(If Not IsNull(varExDate) then ... If Not IsNull (varExBranch) then... In
this case I had no response from the code at all. But again, if I only use
one IF (with either variant) then it works perfect.

I'd appreciate any help I could get!!


Dim strDate As String
Dim strBranch As String
Dim Message As Variant
Dim varExDate As Variant
Dim varExBranch As Variant

strDate = [cboYear] & " / " & [cboMonth]
strBranch = [Forms]![Varnet Numbers]![cboBranch]
varExDate = DLookup("[Month]", "Varnet Numbers", "[Month] = #" & strDate &
"#")
varExBranch = DLookup("[Branch]", "Varnet Numbers", "[Branch] = '" &
strBranch & "'")

If Not IsNull(varExDate And varExBranch) Then
Message = MsgBox("An entry for " & strBranch & " already exists for " &
strDate & ". Please check your date, or ask Tim for help in checking the
database for accuracy. This record has not been posted.", 0 + 16,
"Somethings screwed up")
Else
[Forms]![Varnet Numbers]![Month] = strDate
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord acForm, "Varnet Numbers", acNewRec
End If


.