DLookup multiple criteria (1 Viewer)

Bobadopolis · Mar 6, 2006

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

RuralGuy · Mar 6, 2006

If [RangeID] is numeric then remove the single quotes that surrounds the value:

Code:

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

Bobadopolis · Mar 6, 2006

Genius!

Pure Genius!!

Thanks a lot RuralGuy!!!

Bobadopolis

RuralGuy · Mar 6, 2006

Glad I could help.

Bobadopolis · Mar 13, 2006

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

RuralGuy · Mar 13, 2006

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

Bobadopolis · Mar 14, 2006

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

RuralGuy · Mar 14, 2006

Are you doing:
Variable = DLookup(...
DLookup returns a Null if no record was located.

Bobadopolis · Mar 14, 2006

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!!

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

RuralGuy · Mar 14, 2006

Glad to help.

murray83 · Mar 19, 2019

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

isladogs · Mar 19, 2019

Remove the double quote after 'no' in your expression.
The second part needs to refer to a field name, not a control name

murray83 · Mar 19, 2019

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

so something like this

Code:

DLookup("[Received]", "main", "Received = 'no' & "[PCC]" = 'PCC JS Owned'")

isladogs · Mar 19, 2019

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'

murray83 · Mar 19, 2019

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'

yes that is my real code

murray83 · Mar 19, 2019

murray83 said:
yes that is my real code

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

isladogs · Mar 19, 2019

Yes it would work with DCount.
Or you can use DCount("*", …..) which may be marginally faster to process

murray83 · Mar 19, 2019

isladogs said:
Yes it would work with DCount.
Or you can use DCount("*", …..) which may be marginally faster to process

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 ??

isladogs · Mar 19, 2019

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'")

The_Doc_Man · Mar 19, 2019

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.

DLookup multiple criteria (1 Viewer)

No I can't fix it dammit!

AWF VIP

No I can't fix it dammit!

AWF VIP

No I can't fix it dammit!

AWF VIP

No I can't fix it dammit!

AWF VIP

No I can't fix it dammit!

AWF VIP

Games Collector

MVP / VIP

Games Collector

MVP / VIP

Games Collector

Games Collector

MVP / VIP

Games Collector

MVP / VIP

Immoderate Moderator

Similar threads

Users who are viewing this thread