Run-time Error '3464' Data Type Mismatch

R

rwilliams616

I have a form called QueryForm that allows the user to choose values from 10
combo boxes. I have also included Stephen Lebans' calendar to let the user
filter by dates as well. I get the run-time error 3464 at the DLOOKUP line
near the bottom. All of the combo boxes refer to text data types except for
LogNo, which is a number. I assume the error has something to do with me
needing to place quotation marks somewhere around the text references (such
as Me.cmbPartNo), but whatever I try gives me other errors.

Some other info..."LogNo" is the primary key in the "FRACAS Nov3" table.
"cmdSearch" is the command button that is to be pressed to execute the filter.

Can anyone offer any suggestions? TIA

Private Sub cmdSearch_Click()
Dim varWhere As Variant
varWhere = Null
If Not IsNothing(Me.txtDateFrom) Then
If Not IsDate(Me.txtDateFrom) Then
MsgBox "The value in Date From is not a valid date.",
vbCritical, "Invalid Date"
Exit Sub
End If
If Not IsNothing(Me.txtDateTo) Then
If Not IsDate(Me.txtDateTo) Then
MsgBox "The value in Date To is not a valid date.",
vbCritical, "Invalid Date"
Exit Sub
End If
If Me.txtDateTo < Me.txtDateFrom Then
MsgBox "Date To must be greater than or equal to Date
From.", vbCritical, "Invalid Date"
Exit Sub
End If
Else
If Not IsNothing(Me.txtDateTo) Then
If Not IsDate(Me.txtDateTo) Then
MsgBox "The value in Date To is not a valid date.",
vbCritical, "Invalid Date"
Exit Sub
End If
End If
End If
End If
If Not IsNothing(Me.txtDateFrom) Then
varWhere = (varWhere + " AND ") & "[EventDate] >= #" &
Me.txtDateFrom & "#"
End If
If Not IsNothing(Me.txtDateTo) Then
varWhere = (varWhere + " AND ") & "[EventDate] < #" &
CDate(Me.txtDateTo) + 1 & "#"
End If
If Not IsNothing(Me.cmbPartNo) Then
varWhere = (varWhere + " AND ") & "[PartNo] = " & Me.cmbPartNo
End If
If Not IsNothing(Me.cmbPartNomen) Then
varWhere = (varWhere + " AND ") & "[Nomenclature] = " &
Me.cmbPartNomen
End If
If Not IsNothing(Me.cmbTLPartNo) Then
varWhere = (varWhere + " AND ") & "[NHA_PN] = " & Me.cmbTLPartNo
End If
If Not IsNothing(Me.cmbTLNomen) Then
varWhere = (varWhere + " AND ") & "[NHA_Nomenclature] = " &
Me.cmbTLNomen
End If
If Not IsNothing(Me.cmbLogNo) Then
varWhere = (varWhere + " AND ") & "[LogNo] = " & Me.cmbLogNo
End If
If Not IsNothing(Me.cmbCustomer) Then
varWhere = (varWhere + " AND ") & "[Customer] = " & Me.cmbCustomer
End If
If Not IsNothing(Me.cmbAircraft) Then
varWhere = (varWhere + " AND ") & "[Aircraft] = " & Me.cmbAircraft
End If
If Not IsNothing(Me.cmbWhenOccurred) Then
varWhere = (varWhere + " AND ") & "[WhenOccurred] = " &
Me.cmbWhenOccurred
End If
If Not IsNothing(Me.cmbActionTaken) Then
varWhere = (varWhere + " AND ") & "[ActionTaken] = " &
Me.cmbActionTaken
End If
If Not IsNothing(Me.cmbClosed) Then
varWhere = (varWhere + " AND ") & "[EventClosed] = " & Me.cmbClosed
End If
If IsNothing(varWhere) Then
MsgBox "You must enter at least one search criteria.",
vbInformation, "FRACAS"
Exit Sub
End If
If IsNothing(DLookup("LogNo", "[FRACAS Nov3]", varWhere)) Then
MsgBox "No records meet your criteria.", vbInformation, "No records"
Exit Sub
End If
DoCmd.OpenForm "FRACAS", WhereCondition:=varWhere
DoCmd.Close acForm, Me.name
End Sub
 
D

Douglas J. Steele

IsNothing isn't the appropriate function to be using: you should be using
IsNull, especially in conjunction with the DLookup.

Post back if changing all the IsNothing calls to IsNull doesn't fix the
problem.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


rwilliams616 said:
I have a form called QueryForm that allows the user to choose values from
10
combo boxes. I have also included Stephen Lebans' calendar to let the
user
filter by dates as well. I get the run-time error 3464 at the DLOOKUP
line
near the bottom. All of the combo boxes refer to text data types except
for
LogNo, which is a number. I assume the error has something to do with me
needing to place quotation marks somewhere around the text references
(such
as Me.cmbPartNo), but whatever I try gives me other errors.

Some other info..."LogNo" is the primary key in the "FRACAS Nov3" table.
"cmdSearch" is the command button that is to be pressed to execute the
filter.

Can anyone offer any suggestions? TIA

Private Sub cmdSearch_Click()
Dim varWhere As Variant
varWhere = Null
If Not IsNothing(Me.txtDateFrom) Then
If Not IsDate(Me.txtDateFrom) Then
MsgBox "The value in Date From is not a valid date.",
vbCritical, "Invalid Date"
Exit Sub
End If
If Not IsNothing(Me.txtDateTo) Then
If Not IsDate(Me.txtDateTo) Then
MsgBox "The value in Date To is not a valid date.",
vbCritical, "Invalid Date"
Exit Sub
End If
If Me.txtDateTo < Me.txtDateFrom Then
MsgBox "Date To must be greater than or equal to Date
From.", vbCritical, "Invalid Date"
Exit Sub
End If
Else
If Not IsNothing(Me.txtDateTo) Then
If Not IsDate(Me.txtDateTo) Then
MsgBox "The value in Date To is not a valid date.",
vbCritical, "Invalid Date"
Exit Sub
End If
End If
End If
End If
If Not IsNothing(Me.txtDateFrom) Then
varWhere = (varWhere + " AND ") & "[EventDate] >= #" &
Me.txtDateFrom & "#"
End If
If Not IsNothing(Me.txtDateTo) Then
varWhere = (varWhere + " AND ") & "[EventDate] < #" &
CDate(Me.txtDateTo) + 1 & "#"
End If
If Not IsNothing(Me.cmbPartNo) Then
varWhere = (varWhere + " AND ") & "[PartNo] = " & Me.cmbPartNo
End If
If Not IsNothing(Me.cmbPartNomen) Then
varWhere = (varWhere + " AND ") & "[Nomenclature] = " &
Me.cmbPartNomen
End If
If Not IsNothing(Me.cmbTLPartNo) Then
varWhere = (varWhere + " AND ") & "[NHA_PN] = " & Me.cmbTLPartNo
End If
If Not IsNothing(Me.cmbTLNomen) Then
varWhere = (varWhere + " AND ") & "[NHA_Nomenclature] = " &
Me.cmbTLNomen
End If
If Not IsNothing(Me.cmbLogNo) Then
varWhere = (varWhere + " AND ") & "[LogNo] = " & Me.cmbLogNo
End If
If Not IsNothing(Me.cmbCustomer) Then
varWhere = (varWhere + " AND ") & "[Customer] = " & Me.cmbCustomer
End If
If Not IsNothing(Me.cmbAircraft) Then
varWhere = (varWhere + " AND ") & "[Aircraft] = " & Me.cmbAircraft
End If
If Not IsNothing(Me.cmbWhenOccurred) Then
varWhere = (varWhere + " AND ") & "[WhenOccurred] = " &
Me.cmbWhenOccurred
End If
If Not IsNothing(Me.cmbActionTaken) Then
varWhere = (varWhere + " AND ") & "[ActionTaken] = " &
Me.cmbActionTaken
End If
If Not IsNothing(Me.cmbClosed) Then
varWhere = (varWhere + " AND ") & "[EventClosed] = " & Me.cmbClosed
End If
If IsNothing(varWhere) Then
MsgBox "You must enter at least one search criteria.",
vbInformation, "FRACAS"
Exit Sub
End If
If IsNothing(DLookup("LogNo", "[FRACAS Nov3]", varWhere)) Then
MsgBox "No records meet your criteria.", vbInformation, "No
records"
Exit Sub
End If
DoCmd.OpenForm "FRACAS", WhereCondition:=varWhere
DoCmd.Close acForm, Me.name
End Sub
Click to expand...
 
R

rwilliams616

Hi Doug,

Thanks for the suggestion...all "IsNothing" calls have been changed to
"IsNull". I still get the error however. The filter does work correctly
when I use the date filter and/or the LogNo filter (LogNo is the only NUMBER
data type out of all the combo boxes). So that leads me to believe that it
is something is wrong with the other combo boxes that reference TEXT data
types.
 
D

Douglas J. Steele

Text values need to be enclosed in quotes.

If Not IsNull(Me.cmbPartNomen) Then
varWhere = (varWhere + " AND ") & _
"[Nomenclature] = """ & Me.cmbPartNomen & """"
End If

By the way, in the future it would be useful not to trim so much from your
message: it's a pain to have to go back to find your original code.
 
R

rwilliams616

Thanks Doug, that fixed it!

Douglas J. Steele said:
Text values need to be enclosed in quotes.

If Not IsNull(Me.cmbPartNomen) Then
varWhere = (varWhere + " AND ") & _
"[Nomenclature] = """ & Me.cmbPartNomen & """"
End If

By the way, in the future it would be useful not to trim so much from your
message: it's a pain to have to go back to find your original code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


rwilliams616 said:
Hi Doug,

Thanks for the suggestion...all "IsNothing" calls have been changed to
"IsNull". I still get the error however. The filter does work correctly
when I use the date filter and/or the LogNo filter (LogNo is the only
NUMBER
data type out of all the combo boxes). So that leads me to believe that
it
is something is wrong with the other combo boxes that reference TEXT data
types.
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

Deleting a single field of a single record 3
searching and finding records from separate tables 2
filrter several reports 2
Filter Subform from Main form 3
search form 6
Help with WHERE filter on opening report from dialog 5
Syntax Error 4
Search Form Syntax 2

Top