DLookup multiple criteria

Bobadopolis

Bobadopolis

No I can't fix it dammit!
Local time
Today, 14:39
Joined
Oct 6, 2005
Messages
77
Hi everyone.

I appreciate that there are many threads on this subject but I have searched and puzzled and become desperate!!

I have an input form that automatically sets any blank fields (mostly combos) to "Not Available" if the user does not enter any data into them. On the form are some cascading combos. If the child combo (Technology) is left blank I need some code to make the "not available" ID correct depending on the value of the parent combo (Range). For this I am using a DLookUp (embedded in an IIf):

Code: 
DLookup("TechnologyID", "tblTechnology", "[RangeID] = '" & ![cboRange] & "' And [Technology] = '" & "Not Available" & " '")

Basically it looks up the "Not Available" ID for the Range that is the Range in the combo and where the text field [Technology] is equal to "Not Available".

However this gives the error "Data type mismatch in criteria or expression".

Any ideas? I'm sure it's a minor error but I'm really stumped!!

Thanks in advance,

Bobadopolis
 
If [RangeID] is numeric then remove the single quotes that surrounds the value:
Code: 
DLookup("TechnologyID", "tblTechnology", _
"[RangeID] = " & ![cboRange] & " And [Technology] = '" & "Not Available" & " '")
 
Last edited:
Genius!

Pure Genius!!

Thanks a lot RuralGuy!!!

Bobadopolis
 
One more thing...

Hey ppl,

just another quick question on this problem... what if I wanted to use a wildcard in this expression? Moreover the * wildcard following the word 'Unknown' in this code:

Code: 
DLookup("VehicleID", "tblVehicles", "[VehicleMakeID] = " & ![cboVehicleMake] & " And [VehicleModel] = '" & "Unknown" & " '")

I've tried it in a few places with no success including:
1) In the quotes that wrap "unknown"
2) In the quotes that wrap the apostrophe including various combinations of spaces etc.

Any thoughts?

Thanks in advance,

Bobadopolis
 
You need to replace the "=" with Like when you use the wildcard "*".
 
Hmmm

Thanks for the reply RuralGuy.

Ok, so I've done that:

Code: 
DLookup("VehicleID", "tblVehicles", "[VehicleMakeID] = " & !cboVehicleMake & " And [VehicleModel] Like '" & "Unknown*" & " '")

But as I have done all along I still get run-time error '3162':
"You tried to assign a Null value to a variable that is not a Variant data type".

Any thoughts?

Cheers,

Bobadopolis
 
Are you doing:
Variable = DLookup(...
DLookup returns a Null if no record was located.
 
Sorted (at last!!)

Hi RuralGuy,

Yes I was doing that so that must have been the behaviour i was experiencing (due to the error in my syntax). But after muchos fiddling I finally fixed it:

Code: 
DLookup("VehicleID", "tblVehicles", "[VehicleMakeID] =  " & !cboVehicleMake & " And [VehicleModel] Like '" & "Unknown*" & "' ")

I think the only difference is in the position of the space in the final quotations and apostrophe!! :mad: These quotes and nested quotes/strings nonsense is mind boggling!

Thanks for all your help, greatly appreciated - hopefully I've put this to bed now for a little while!!

Regards,

Bobadopolis
 
understand this is an old thread but looks like the help i am after

here is my current dlookup, which works fine

Code: 
=DCount("[Received]","main","Received =  'no'")

but i now have to add a second criteria and have looked at http://access.mvps.org/access/general/gen0018.htm and still scratching my head as get error or type faults

this is what i'm trying

Code: 
DLookup("[Received]", "main", "Received = 'no'" & Forms!main_pcc!txtPCC = 'PCC JS Owned'")

all help greatly appreciated
 
Remove the double quote after 'no' in your expression.
The second part needs to refer to a field name, not a control name
 
isladogs said:
Remove the double quote after 'no' in your expression.
The second part needs to refer to a field name, not a control name
Click to expand...

so something like this

Code: 
DLookup("[Received]", "main", "Received = 'no' & "[PCC]" = 'PCC JS Owned'")
 
Simplify it further....

Code: 
DLookup("Received", "main", "Received = 'no' & PCC = 'PCC JS Owned'")

BUT is that your 'real code' because as written its completely pointless.
The result will always be 'no'
 
isladogs said:
Simplify it further....

Code: 
DLookup("Received", "main", "Received = 'no' & PCC = 'PCC JS Owned'")

BUT is that your 'real code' because as written its completely pointless.
The result will always be 'no'
Click to expand...

yes that is my real code
 
murray83 said:
yes that is my real code
Click to expand...

i am an idiot, as i cant even read what i had typed as i have typed dcount ( thats what i have and need ) and somehow got confused and typed dlookup

so would the aforementioned code work with dcount
 
Yes it would work with DCount.
Or you can use DCount("*", …..) which may be marginally faster to process
 
isladogs said:
Yes it would work with DCount.
Or you can use DCount("*", …..) which may be marginally faster to process
Click to expand...

yeah but need to count if its pcc and if its been received or not, so have done as you laid out in your previous message and I'm not sure if its working as both counts show zero

which is good for the received but not for the not received this is my code

Not Received

Code: 
=DCount("Received","main","Received = 'No' & PCC = 'PCC JS Owned'")

Received
Code: 
=DCount("Received","main","Received = 'Yes' & PCC = 'PCC JS Owned'")

my old code had the square brackets, this dosent, I'm not questioning your code as its working with no error message, but are they not mandatory then ??
 
Using [] is necessary where field names have spaces or special characters or where you've used a reserved word. Otherwise its up to you.

Using DCount("*", TableName, filter criteria) and DCount("Received",....) should normally give EXACTLY the same result though that may not always be true

Using "*" allows Access to just count the records whereas using the field name requires it to count records in that field.

If the field is indexed, I expect the time difference will be negligible
If not indexed, I believe it will take a bit longer (not checked).
However even if I'm right, unless you have a lot of records, you may not notice the difference. By all means use the field name if you prefer. Using * is less to type!

However as both are giving zero, I expect the Received field is boolean (yes/no). If I'm correct, change it to False (without quotes)

Code: 
=DCount("Received","main","Received = False And PCC = 'PCC JS Owned'")
 
Last edited:
The ampersand is unlikely to be correct in the third argument (criteria) of the DLookup. The "&" operator relates to string concatenation but "Received= 'no'" is not a string. It is a Boolean expression. Nor is the "PCC = 'PCC JS Owned'" portion a string. Perhaps you want an "AND" in place of the ampersand because AND is the Boolean operator.

Stated another way, you have two comparisons in your criteria statement and used the ampersand as a conjunction. In English, "&" IS a valid conjunction, but in VBA it is not.
 
You must log in or register to reply here.

Similar threads

F
Dlookup using Yes/No field as criteria
Replies
6
Views
613
LarryE
LarryE
T
Solved Using multiple criteria in DCOUNT function using a comparison
Replies
1
Views
452
June7
June7
P
Problem with charts
Replies
5
Views
392
The_Doc_Man
The_Doc_Man
G
DLookup does not return correct value
Replies
9
Views
557
Minty
Minty
U
Adding up counted records to get a total
Replies
11
Views
476
UltimateNeo
U

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom