DCount works/DLookup doesn't work - same criteria

R

Robin

Hello, I need some help with what's probably obvious.

for variables intRFIo As Integer and stRFIs As String

This works:
intRFIo = DCount("*", "ClientEngagementNotes", "[ClientID]= '" & Me.ClientID
& _
"' AND [EngagementID] = '" & Me.EngagementID & _
"' AND [EngagementYr] = '" & Me.EngagementYr & _
"' AND [RFI]= " & True & _
" AND IsNull([RFIRec])")
This does not work:
stRFIs = DLookup("StaffID", "ClientEngagementNotes", "[ClientID]= '" &
Me.ClientID & _
"' AND [EngagementID] = '" & Me.EngagementID & _
"' AND [EngagementYr] = '" & Me.EngagementYr & _
"' AND [RFI]= " & True & _
" AND IsNull([RFIRec])")

Can someone tell me what's wrong?

Thank you,
Robin
 
X

XPS350

Hello,  I need some help with what's probably obvious.

for variables intRFIo As Integer and stRFIs As String

This works:
intRFIo = DCount("*", "ClientEngagementNotes", "[ClientID]= '" & Me.ClientID
& _
        "' AND [EngagementID] = '" & Me.EngagementID & _
        "' AND [EngagementYr] = '" & Me.EngagementYr & _
        "' AND [RFI]= " & True & _
        " AND IsNull([RFIRec])")
This does not work:
stRFIs = DLookup("StaffID", "ClientEngagementNotes", "[ClientID]= '" &
Me.ClientID & _
            "' AND [EngagementID] = '" & Me.EngagementID & _
            "' AND [EngagementYr] = '" & Me.EngagementYr & _
            "' AND [RFI]= " & True & _
            " AND IsNull([RFIRec])")

Can someone tell me what's wrong?

Thank you,
Robin
Click to expand...

What does "does not work" means in your case?


Groeten,

Peter
http://access.xps350.com
 
J

John Spencer

DCount will return zero if no records are found
DLookup will return NULL if no records are found

Assigning NULL to stRFIs will generate an error since string variables cannot
accept NULL values.

You can try the following which should set stRFIs to "" when there are no
records found by the DLookup function.

stRFIs = NZ(DLookup("StaffID", "ClientEngagementNotes", "[ClientID]= '" &
Me.ClientID & _
"' AND [EngagementID] = '" & Me.EngagementID & _
"' AND [EngagementYr] = '" & Me.EngagementYr & _
"' AND [RFI]= " & True & _
" AND IsNull([RFIRec])"),VbNullString)

By the way, it helps if you tell us what error you are getting. I could be
guessing wrong based on your rather uninformative "Can some tell me what's wrong?"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
R

Robin

Sorry for the brevity. I got ahead of myself.

The error is Runtime Error 94
Invalid use of Null

The intRFIo is correctly returning '2' because there are 2 of 68 records in
which RFI=True AND RFIRec is null. (A 'request for information' "RFI" has
been sent but is still outstanding because the RFIRec (a "received" date
field) has not been completed.
Therefore, the DLookup would find a record in this instance.
The entire stRFIs... gets highlighted yellow when the error pops up and I
hit the Debug button.
(I do realize that the DLookup can only find one record but that's ok for
our purposes because the StaffID associated with either of those records will
be an acceptable value.)

Again, sorry I didn't provide more info. I DO know better!

Thank you,
Robin

John Spencer said:
DCount will return zero if no records are found
DLookup will return NULL if no records are found

Assigning NULL to stRFIs will generate an error since string variables cannot
accept NULL values.

You can try the following which should set stRFIs to "" when there are no
records found by the DLookup function.

stRFIs = NZ(DLookup("StaffID", "ClientEngagementNotes", "[ClientID]= '" &
Me.ClientID & _
"' AND [EngagementID] = '" & Me.EngagementID & _
"' AND [EngagementYr] = '" & Me.EngagementYr & _
"' AND [RFI]= " & True & _
" AND IsNull([RFIRec])"),VbNullString)

By the way, it helps if you tell us what error you are getting. I could be
guessing wrong based on your rather uninformative "Can some tell me what's wrong?"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hello, I need some help with what's probably obvious.

for variables intRFIo As Integer and stRFIs As String

This works:
intRFIo = DCount("*", "ClientEngagementNotes", "[ClientID]= '" & Me.ClientID
& _
"' AND [EngagementID] = '" & Me.EngagementID & _
"' AND [EngagementYr] = '" & Me.EngagementYr & _
"' AND [RFI]= " & True & _
" AND IsNull([RFIRec])")
This does not work:
stRFIs = DLookup("StaffID", "ClientEngagementNotes", "[ClientID]= '" &
Me.ClientID & _
"' AND [EngagementID] = '" & Me.EngagementID & _
"' AND [EngagementYr] = '" & Me.EngagementYr & _
"' AND [RFI]= " & True & _
" AND IsNull([RFIRec])")

Can someone tell me what's wrong?

Thank you,
Robin
Click to expand...
.
Click to expand...
 
J

John Spencer

If you are still getting the error, then either no record matches or the
StaffID field in the record that is being returned is Null. Those are the
only two cases I can think of that would cause the error in your situation.

In your DCount function you are counting the records returned. You are not
counting the number of non-null values in a field. If you suspect that the
field could be null, you can try adding that to your criteria.
... AND StaffID is not null

You could add that to both the DCount and the DLookup and see if your results
change.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Sorry for the brevity. I got ahead of myself.

The error is Runtime Error 94
Invalid use of Null

The intRFIo is correctly returning '2' because there are 2 of 68 records in
which RFI=True AND RFIRec is null. (A 'request for information' "RFI" has
been sent but is still outstanding because the RFIRec (a "received" date
field) has not been completed.
Therefore, the DLookup would find a record in this instance.
The entire stRFIs... gets highlighted yellow when the error pops up and I
hit the Debug button.
(I do realize that the DLookup can only find one record but that's ok for
our purposes because the StaffID associated with either of those records will
be an acceptable value.)

Again, sorry I didn't provide more info. I DO know better!

Thank you,
Robin

John Spencer said:
DCount will return zero if no records are found
DLookup will return NULL if no records are found

Assigning NULL to stRFIs will generate an error since string variables cannot
accept NULL values.

You can try the following which should set stRFIs to "" when there are no
records found by the DLookup function.

stRFIs = NZ(DLookup("StaffID", "ClientEngagementNotes", "[ClientID]= '" &
Me.ClientID & _
"' AND [EngagementID] = '" & Me.EngagementID & _
"' AND [EngagementYr] = '" & Me.EngagementYr & _
"' AND [RFI]= " & True & _
" AND IsNull([RFIRec])"),VbNullString)

By the way, it helps if you tell us what error you are getting. I could be
guessing wrong based on your rather uninformative "Can some tell me what's wrong?"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hello, I need some help with what's probably obvious.

for variables intRFIo As Integer and stRFIs As String

This works:
intRFIo = DCount("*", "ClientEngagementNotes", "[ClientID]= '" & Me.ClientID
& _
"' AND [EngagementID] = '" & Me.EngagementID & _
"' AND [EngagementYr] = '" & Me.EngagementYr & _
"' AND [RFI]= " & True & _
" AND IsNull([RFIRec])")
This does not work:
stRFIs = DLookup("StaffID", "ClientEngagementNotes", "[ClientID]= '" &
Me.ClientID & _
"' AND [EngagementID] = '" & Me.EngagementID & _
"' AND [EngagementYr] = '" & Me.EngagementYr & _
"' AND [RFI]= " & True & _
" AND IsNull([RFIRec])")

Can someone tell me what's wrong?

Thank you,
Robin
Click to expand...
.
Click to expand...
Click to expand...
 
R

Robin

Thank You John,

I did get it to work but I'm not sure how. Either you were right and there
was no StaffID associated with one or both of those records (I did a lot of
data cleanup in the interim) or a line I inserted changed something but I
don't understand how. Before the stRFIs DLookup, I added: If intRFIo > 0
Then...

I don't know why that would have done anything assuming consistent data.

Again thank you for your valuable time,
Robin

John Spencer said:
If you are still getting the error, then either no record matches or the
StaffID field in the record that is being returned is Null. Those are the
only two cases I can think of that would cause the error in your situation.

In your DCount function you are counting the records returned. You are not
counting the number of non-null values in a field. If you suspect that the
field could be null, you can try adding that to your criteria.
... AND StaffID is not null

You could add that to both the DCount and the DLookup and see if your results
change.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Sorry for the brevity. I got ahead of myself.

The error is Runtime Error 94
Invalid use of Null

The intRFIo is correctly returning '2' because there are 2 of 68 records in
which RFI=True AND RFIRec is null. (A 'request for information' "RFI" has
been sent but is still outstanding because the RFIRec (a "received" date
field) has not been completed.
Therefore, the DLookup would find a record in this instance.
The entire stRFIs... gets highlighted yellow when the error pops up and I
hit the Debug button.
(I do realize that the DLookup can only find one record but that's ok for
our purposes because the StaffID associated with either of those records will
be an acceptable value.)

Again, sorry I didn't provide more info. I DO know better!

Thank you,
Robin

John Spencer said:
DCount will return zero if no records are found
DLookup will return NULL if no records are found

Assigning NULL to stRFIs will generate an error since string variables cannot
accept NULL values.

You can try the following which should set stRFIs to "" when there are no
records found by the DLookup function.

stRFIs = NZ(DLookup("StaffID", "ClientEngagementNotes", "[ClientID]= '" &
Me.ClientID & _
"' AND [EngagementID] = '" & Me.EngagementID & _
"' AND [EngagementYr] = '" & Me.EngagementYr & _
"' AND [RFI]= " & True & _
" AND IsNull([RFIRec])"),VbNullString)

By the way, it helps if you tell us what error you are getting. I could be
guessing wrong based on your rather uninformative "Can some tell me what's wrong?"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Robin wrote:
Hello, I need some help with what's probably obvious.

for variables intRFIo As Integer and stRFIs As String

This works:
intRFIo = DCount("*", "ClientEngagementNotes", "[ClientID]= '" & Me.ClientID
& _
"' AND [EngagementID] = '" & Me.EngagementID & _
"' AND [EngagementYr] = '" & Me.EngagementYr & _
"' AND [RFI]= " & True & _
" AND IsNull([RFIRec])")
This does not work:
stRFIs = DLookup("StaffID", "ClientEngagementNotes", "[ClientID]= '" &
Me.ClientID & _
"' AND [EngagementID] = '" & Me.EngagementID & _
"' AND [EngagementYr] = '" & Me.EngagementYr & _
"' AND [RFI]= " & True & _
" AND IsNull([RFIRec])")

Can someone tell me what's wrong?

Thank you,
Robin
.
Click to expand...
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DSum with multiple criteria 4
Repost:DSum w/multiple criteria (Invalid Syntax) 7
that didn't work 1
Problem with Dlookup function 24
How to get DLookup working correctly to check if a record already exists in the database 2
DLookup Criteria 2
DCount with mathematical criteria, DateDiff function 2
Help with multiple criteria DLookup 4

Top