CODEWITHSUNDEEP
vbams-accessms-access-2010
Mgogan
Mgogan

Reputation: 55

Dlookup for date returing Type Mismatch error

I can't quite figure out what is wrong with this code that is causing a "Type Mismatch" error. I have a feeling it has to do with all the ' and " but I try all sorts of combinations and still no luck. I have tried Dim duedate As String but no change. Where am I going wrong with this one?

Private Sub Report_Load()

Cat1 = Me.Text175.Value
AssetNum1 = Me.Text177.Value

Dim duedate As Date
duedate = FormatDateTime(Nz(DLookup("[Calibration Due Date]", "[Calibration Data]", "[ID] = " & AssetNum1 And "[Category] = " & Cat1), ""), vbShortDate)

If duedate = "" Then
Me.Text26 = "Oopps"
Else
Me.Text26 = duedate
End If
End Sub

Upvotes: 0

Views: 1043

Answers (2)

adlee-dev
adlee-dev

Reputation: 151

VBlades's answer addresses one issue with your code that needs to be fixed, but there is another reason that you will get type mismatch errors with the posted code.

When the DLookup() in the Nz() evalutes to null, Nz() will pass "" (empty string) as the value to be formatted with FormatDateTime(); FormatDateTime() requires a date expression as its first argument, and an empty string is not a valid date.

Additionally, the following piece of code is not valid:

If duedate = "" Then
'stuff
End If

because you are attempting to evaluate whether duedate is an empty string. duedate is defined as a date value in your code and cannot be an empty string (this is likely where the actual error you are encountering is coming from assuming the DLookup() function has not yet returned null).

The Nz() function in your code will need to evaluate to something that FormatDateTime() can understand. As a simple workaround, Nz() could return a value that has no meaning in your database, but can be evaluated as a date; for example 1. One evaluates to 12/31/1899 when formatted as vbShortDate. Your validation block would then be:

If duedate = #12/31/1899# Then
    Me.Text26 = "Oopps"
Else
    Me.Text26 = duedate
End If

Note that you could use 0, but 0 is both #12/30/1899# AND #12:00:00 AM# (and the IDE will in fact convert #12/30/1899# to #12:00:00 AM# when compiling). #12:00:00 AM# or 0 or #12/30/1899#, though, could potentially have meaning so it's best not to use it as the placeholder value.

EDIT:

If AssetNum1 or Cat1 could be null, you can achieve what you want using the following:

Dim Cat1 as string
Dim AssetNum1 as Long
Dim duedate As Date

If IsNull(Me.Text175.Value) or IsNull(Me.Text177.Value) Then
    Me.Text26 = "Oopps"
Else
    Cat1 = Me.Text175.Value
    AssetNum1 = Me.Text177.Value
    duedate = FormatDateTime(Nz(DLookup("[Calibration Due Date]","[Calibration Data]","[ID] = " & AssetNum1 & " And [Category] = """ & Cat1 & """"),1),vbShortDate)
    If duedate = #12/31/1899# Then
        Me.Text26 = "Oopps"
    Else
        Me.Text26 = duedate
    End If
End If

Upvotes: 1

VBlades
VBlades

Reputation: 2251

You have your double quote in the wrong place and need another concatenation operator, it seems. Assuming Cat1 is numeric:

duedate = FormatDateTime(Nz(DLookup("[Calibration Due Date]", "[Calibration Data]", "[ID] = " & AssetNum1 & " And [Category] = " & Cat1), ""), vbShortDate)

If Cat1 is text:

duedate = FormatDateTime(Nz(DLookup("[Calibration Due Date]", "[Calibration Data]", "[ID] = " & AssetNum1 & " And [Category] = '" & Cat1 & "'"), ""), vbShortDate)

Upvotes: 0

Related Questions