DLookup type mismatch (1 Viewer)

[sroot]

I am having an issue using a Dlookup this is the line that it is failing on.

If DLookup("loc_code", "SOSLOCS", "loc_code = Text4") Then

It seems to be doing what i want if the code i put in doesn't match, but if it does match whats in the SOSLOCS query then it gives me a type mismatch error. Any ideas? Thanks!

 

[plog]

The Dlookup is working fine, its the If you have messed up. Think of If as a function that requires a True or False value inside it. You have not done that in the code above.

The Dlookup is inside your If. DLookup does not return True or False, it returns whatever value is in loc_code. You need to make whatever the is inside the If return a True or False value.

Normally this is done via a comparison:

IF (Dlookup() = Something) then ...

Either the Dlookup equals Something (which means True) or it doesn't (which means false).

 

[pbaldy]

This should help with the error you're getting:

http://access.mvps.org/access/general/gen0018.htm

 

[sroot]

i only posted the line that is failing... the whole code is

Private Sub Text4_AfterUpdate()

If DLookup("loc_code", "SOSLOCS", "loc_code = Text4") Then
Call DoCmd.OpenQuery("SOSAppend")
DoCmd.RunSQL "DELETE * FROM SOSInput;"
Me.Requery
Call DoCmd.OpenQuery("SOSAppend")
DoCmd.RunSQL "DELETE * FROM SOSInput;"
Me.Requery
Else
MsgBox ("Location code in not valid! Please check the location and try again")
DoCmd.RunSQL "DELETE * FROM SOSInput;"
Me.Text4 = ""
Me.Text4.SetFocus
Me.Requery
DoCmd.RunSQL "DELETE * FROM SOSInput;"
End If
End Sub

 

[pbaldy]

Plog's suggestion is still valid, and my link still holds the solution to your error.

 

[JHB]

Did you look at the link pbaldy gave you?
Because this is the problem part, "loc_code = Text4"
If you want to compare if loc_code contain Text4 then it need to be surrounded by single quotes, "loc_code = 'Text4'"
But I suppose Text4 is a control, so if it should be number then:

DLookup("loc_code", "SOSLOCS", "loc_code =[B][COLOR=Red]" &[/COLOR][/B] Text4)

If text then:

DLookup("loc_code", "SOSLOCS", "loc_code =[B][COLOR=Red]'" &[/COLOR][/B] Text4 [B][COLOR=Red]& "'"[/COLOR][/B])

 

[pbaldy]

There's your fish.

 

[sroot]

That is how i had it originally but i thought i had it wrong because when i do it that was it goes to the else part and tells me the code is not valid... so i am not sure why that isn't working

 

[pbaldy]

You haven't addressed plog's post. You aren't testing the value returned against anything. If you're testing for the existence of that code, use DCount() and test for "= 0", or test whether the DLookup() is returning Null.

 

[plog]

I ran some tests and I think if he gets the DLookup corrected it will function as he intends without a comparison. Doesn't mean its a good practice to not have a comparison there, but I think it will evalulate properly without one.

If Dlookup returns a value it will be evaluated as true:
If ("Test") -> True
If ("2") -> True

The function If will evaluate to true if any value is returned from the Dlookup. If no value is returned it will evaluate false:

If(NULL) -> False

Saying all that, I think the best practice would be to use a DCount.

 

[sroot]

Thanks guys. I changed it to a DCount and it worked perfect. I just thought i could use a Dlookup since i did that with something else... not sure if that worked because it was numbers and this is text. But thanks for the help!