Reputation: 55
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
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
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