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

On 22 jan, 22:04, Robin <Ro...@discussions.microsoft.com> 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

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


Groeten,

Peter
http://access.xps350.com

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

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" wrote:

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

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

Robin wrote:
> 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" wrote:
>
>> 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
>> .
>>

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" wrote:

> 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
>
> Robin wrote:
> > 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" wrote:
> >
> >> 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
> >> .
> >>
> .
>