DLookup with date gives Type mismatch

  • Thread starter Thread starter Evi
  • Start date Start date
E

Evi

My database records how often some bowling greens are treated (eg for
fungus, top dressing).

One table tells me how many months until the next treatment and a query (not
my Report's one) shows the latest (max) date when a particular treatment
will be required for a particular green.

My report contains all the dates for the year next to each of the Bowling
Greens
and I want to colour a date next to that Green if it is a date that appears
in the query. This is the code I have in the report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim MyLook As Variant

Dim MyWhite As Long

Dim MyRed As Long

MyWhite = 16777215

MyRed = 8421631

MyLook = Nz(DLookup("[TDate]", "QryLastTreatmentDue", "[MaxOfRptDue]= #" &
Me.TDate & "# AND [GreenID] = " & Me.GreenID))

If MyLook <> 0 Then

'the date is in the report

Me.TAbr.BackColor = MyRed

Else

Me.TAbr.BackColor = MyWhite

End If

End Sub

I get a type mismatch if the date is found. How can I overcome this?

Evi
 
I never use Nz() without providing two arguments. The second is to specify
the value if the first expression is Null. Also, Nz() returns a variant so
it might be best to Use
MyLook=Nz(DCount("[TDate]", "QryLastTreatmentDue", "[MaxOfRptDue]= #" &
Me.TDate & "# AND [GreenID] = " & Me.GreenID),0)
 
Thanks Duane. That's great.

Evi

Duane Hookom said:
I never use Nz() without providing two arguments. The second is to specify
the value if the first expression is Null. Also, Nz() returns a variant so
it might be best to Use
MyLook=Nz(DCount("[TDate]", "QryLastTreatmentDue", "[MaxOfRptDue]= #" &
Me.TDate & "# AND [GreenID] = " & Me.GreenID),0)


--
Duane Hookom
MS Access MVP


Evi said:
My database records how often some bowling greens are treated (eg for
fungus, top dressing).

One table tells me how many months until the next treatment and a query (not
my Report's one) shows the latest (max) date when a particular treatment
will be required for a particular green.

My report contains all the dates for the year next to each of the Bowling
Greens
and I want to colour a date next to that Green if it is a date that appears
in the query. This is the code I have in the report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim MyLook As Variant

Dim MyWhite As Long

Dim MyRed As Long

MyWhite = 16777215

MyRed = 8421631

MyLook = Nz(DLookup("[TDate]", "QryLastTreatmentDue", "[MaxOfRptDue]= #" &
Me.TDate & "# AND [GreenID] = " & Me.GreenID))

If MyLook <> 0 Then

'the date is in the report

Me.TAbr.BackColor = MyRed

Else

Me.TAbr.BackColor = MyWhite

End If

End Sub

I get a type mismatch if the date is found. How can I overcome this?

Evi
Click to expand...
Click to expand...
 
Back
Top