DLookup multiple criteria (1 Viewer)

isladogs · Mar 19, 2019

Oops. How did I miss that ampersand?
My only excuse is that I copied from Murray's post but that's a feeble excuse.

I'm going to correct my last post now. Thanks Doc

murray83 · Mar 19, 2019

isladogs said:
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.

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 & PCC = 'PCC JS Owned'")

cheers i tried the latest code you suggested but still no dice see attached my table design

if that helps and a copy of the database, i also tried using AND as well as an ampersand

isladogs · Mar 19, 2019

Works for me
Not received = 15 ; Received = 0
See attached

In future, it would be appreciated if uploaded files had all irrelevant items removed (we only needed the table & form)
Also, all security features removed (as I have done in my updated version)

Shecky · Aug 25, 2022

Hoping this thread is still valid as It seems my issue is comparable...
I get a type mismatch on the following. Truth is I never came to understand all the double quotes and quotes. I am working from similar statements but apparently not similar enough...

Code:

Me.[Salesperson] = DLookup("Salesperson", "CustomerPricing", "SKU = '" & Me.[SKU] & "'" And "CompanyName = '" & Me.Parent.[CompanyName] & "'")

Salesperson: shorttext 5 char
SKU: Shortext
Company Name: Shortext

The function is looking up the salesperson code from table customer pricing where the SKU code is the same as the SKU code entered into the field and the company name matches the control on the parent form.

Any help on this one would be great. Thanks

Gasman · Aug 25, 2022

Put all the criteria into a string variable, and debug.print that.
When you get it correct, then use that in the DlookUp()

At a quick glance it looks OK, for the data types you have specified?
However try

Code:

Me.[Salesperson] = DLookup("Salesperson", "CustomerPricing", "SKU = '" & Me.[SKU] & "' And CompanyName = '" & Me.Parent.[CompanyName] & "'")

Shecky · Aug 25, 2022

Its a one shot, one kill. Thanks Man. I did try removing one set of " before Company name but was getting syntax error, coouldnt figure out which other " needed to be removed. Thanks so much!

Gasman · Aug 25, 2022

Again, debug.print would get you there, a bit at a time.

DLookup multiple criteria (1 Viewer)

isladogs

MVP / VIP

murray83

Games Collector

Attachments

isladogs

MVP / VIP

Attachments

Shecky

New member

Gasman

Enthusiastic Amateur

Shecky

New member

Gasman

Enthusiastic Amateur

Similar threads

Users who are viewing this thread