vba code question

R

ryan.fitzpatrick3

I have this code which I copied and pasted from allen browns search
example database. I changed the tbox code to reflect my code. When I
run the query it gives me a type mismatch error, any one know why?

Here's allen brown's code: (I deleted the some of the other code that
doesn't pertain.)

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.

'Date field example. Use the format string to add the # delimiters
and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate
+ 1, conJetDate) & ") AND "
End If

End Sub





Here is my code:




Private Sub cmdFilter_Click()

Dim strdoc As String
strdoc = "qryMakeTableQuickOrder"
DoCmd.Requery

'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.

'Date field example. Use the format string to add the # delimiters
and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([po_recpt_recdt] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([po_recpt_recdt] < " & Format
(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

End Sub


Why am I getting a type mismatch error?
 
R

ryan.fitzpatrick3

I'm thinking that the table has a time stamp on the date, would that
matter if I'm only looking for date and not date + time?


I have this code which I copied and pasted from allen browns search
example database. I changed the tbox code to reflect my code. When I
run the query it gives me a type  mismatch error, any one know why?

Here's allen brown's code: (I deleted the some of the other code that
doesn't pertain.)

Private Sub cmdFilter_Click()
    'Purpose:   Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      =only dates between (both
inclusive. _
                        Start date only = all dates from this one
onwards; _
                        End date only   = alldates up to (and
including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria
string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for
dates in a JET query string.

    'Date field example. Use the format string to add the # delimiters
and get the right international format.
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([EnteredOn] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
    End If

    'Another date field example. Use "less than the next day" since
this field has times as well as dates.
    If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
        strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate
+ 1, conJetDate) & ") AND "
    End If

End Sub

Here is my code:

Private Sub cmdFilter_Click()

Dim strdoc As String
strdoc = "qryMakeTableQuickOrder"
DoCmd.Requery

    'Purpose:   Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      =only dates between (both
inclusive. _
                        Start date only = all dates from this one
onwards; _
                        End date only   = alldates up to (and
including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria
string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for
dates in a JET query string.

    'Date field example. Use the format string to add the # delimiters
and get the right international format.
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([po_recpt_recdt] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
    End If

    'Another date field example. Use "less than the next day" since
this field has times as well as dates.
    If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
        strWhere = strWhere & "([po_recpt_recdt] < " & Format
(Me.txtEndDate + 1, conJetDate) & ") AND "
    End If

End Sub

Why am I getting a type mismatch error?
Click to expand...
 
J

JString

Dates and times use the same data type. A single value could be displayed as
'mm/dd/yyyy' or 'hh:mm:ss' depending on the format you specify in Access.

In case nobody else picks up on your problem, it might help to set a couple
of break points and step through the code to figure out which line is causing
the error.

[email protected] said:
I'm thinking that the table has a time stamp on the date, would that
matter if I'm only looking for date and not date + time?


I have this code which I copied and pasted from allen browns search
example database. I changed the tbox code to reflect my code. When I
run the query it gives me a type mismatch error, any one know why?

Here's allen brown's code: (I deleted the some of the other code that
doesn't pertain.)

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.

'Date field example. Use the format string to add the # delimiters
and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate
+ 1, conJetDate) & ") AND "
End If

End Sub

Here is my code:

Private Sub cmdFilter_Click()

Dim strdoc As String
strdoc = "qryMakeTableQuickOrder"
DoCmd.Requery

'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.

'Date field example. Use the format string to add the # delimiters
and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([po_recpt_recdt] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([po_recpt_recdt] < " & Format
(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

End Sub

Why am I getting a type mismatch error?
Click to expand...
Click to expand...
 
R

ryan.fitzpatrick3

I'm not savvy enough to know what I'm doing with that. I would require
more direct help. thanks though.

Dates and times use the same data type.  A single value could be displayed as
'mm/dd/yyyy' or 'hh:mm:ss' depending on the format you specify in Access. 

In case nobody else picks up on your problem, it might help to set a couple
of break points and step through the code to figure out which line is causing
the error.

[email protected] said:
I'm thinking that the table has a time stamp on the date, would that
matter if I'm only looking for date and not date + time?
I have this code which I copied and pasted from allen browns search
example database. I changed the tbox code to reflect my code. When I
run the query it gives me a type  mismatch error, any one know why?
Here's allen brown's code: (I deleted the some of the other code that
doesn't pertain.)
Private Sub cmdFilter_Click()
    'Purpose:   Build up the criteria string form the non-blanksearch
boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both
inclusive. _
                        Start date only = all dates from this one
onwards; _
                        End date only   =all dates up to (and
including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria
string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for
dates in a JET query string.
    'Date field example. Use the format string to add the # delimiters
and get the right international format.
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([EnteredOn] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
    End If
    'Another date field example. Use "less than the next day" since
this field has times as well as dates.
    If Not IsNull(Me.txtEndDate) Then   'Less than the next day..
        strWhere = strWhere & "([EnteredOn] < " & Format(Me..txtEndDate
+ 1, conJetDate) & ") AND "
    End If
End Sub
Here is my code:
Private Sub cmdFilter_Click()
Dim strdoc As String
strdoc = "qryMakeTableQuickOrder"
DoCmd.Requery
    'Purpose:   Build up the criteria string form the non-blanksearch
boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both
inclusive. _
                        Start date only = all dates from this one
onwards; _
                        End date only   =all dates up to (and
including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria
string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for
dates in a JET query string.
    'Date field example. Use the format string to add the # delimiters
and get the right international format.
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([po_recpt_recdt] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
    End If
    'Another date field example. Use "less than the next day" since
this field has times as well as dates.
    If Not IsNull(Me.txtEndDate) Then   'Less than the next day..
        strWhere = strWhere & "([po_recpt_recdt] < " & Format
(Me.txtEndDate + 1, conJetDate) & ") AND "
    End If
End Sub
Why am I getting a type mismatch error?
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Yes, it'll make a difference, but it shouldn't cause the Type Mismatch.

That having been said, I don't see where you're using strWhere anywhere in
running your query.

Not only that, but I don't believe your DoCmd.Requery statement is valid.
It's supposed to have a control name as a parameter.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm thinking that the table has a time stamp on the date, would that
matter if I'm only looking for date and not date + time?


I have this code which I copied and pasted from allen browns search
example database. I changed the tbox code to reflect my code. When I
run the query it gives me a type mismatch error, any one know why?

Here's allen brown's code: (I deleted the some of the other code that
doesn't pertain.)

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.

'Date field example. Use the format string to add the # delimiters
and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate
+ 1, conJetDate) & ") AND "
End If

End Sub

Here is my code:

Private Sub cmdFilter_Click()

Dim strdoc As String
strdoc = "qryMakeTableQuickOrder"
DoCmd.Requery

'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.

'Date field example. Use the format string to add the # delimiters
and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([po_recpt_recdt] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([po_recpt_recdt] < " & Format
(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

End Sub

Why am I getting a type mismatch error?
Click to expand...
 
R

ryan.fitzpatrick3

Here is Allen Brown's complete code:

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.


'***********************************************************************
'Look at each search box, and build up the criteria string from
the non-blank ones.

'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """)
AND "
End If

'Another text field example. Use Like to find anywhere in the
field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([MainName] Like ""*" &
Me.txtFilterMainName & "*"") AND "
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboFilterLevel) Then
strWhere = strWhere & "([LevelID] = " & Me.cboFilterLevel & ")
AND "
End If

'Yes/No field and combo example. If combo is blank or contains
"ALL", we do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([IsCorporate] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([IsCorporate] = False) AND "
End If

'Date field example. Use the format string to add the # delimiters
and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate
+ 1, conJetDate) & ") AND "
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub



Here is my complete code:

Private Sub cmdFilter_Click()

'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.



'***********************************************************************
'Look at each search box, and build up the criteria string from
the non-blank ones.

'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboxCompany) Then
strWhere = strWhere & "([gl_cmp_key] = """ & Me.cboxCompany &
""") AND "
End If

'Another text field example. Use Like to find anywhere in the
field.
If Not IsNull(Me.cboxBranch) Then
strWhere = strWhere & "([so_brnch_key] = """ & Me.cboxBranch &
""") AND "
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboxVendor) Then
strWhere = strWhere & "([en_vend_key] = """ & Me.cboxVendor &
""") AND "
End If

If Not IsNull(Me.cboxItem) Then
strWhere = strWhere & "([in_item_key] = """ & Me.cboxItem &
""") AND "
End If

'If Not IsNull(Me.cboxBuyer) Then
' strWhere = strWhere & "([en_phfmt_key] = """ & Me.cboxBuyer &
""") AND "
'End If

If Not IsNull(Me.cboxCommodity) Then
strWhere = strWhere & "([in_comcd_key] = """ &
Me.cboxCommodity & """) AND "
End If

'If Not IsNull(Me.cboxYear) Then
' strWhere = strWhere & "([Rec Date] = " & Me.cboxYear & ") AND
"
'End If

'Yes/No field and combo example. If combo is blank or contains
"ALL", we do nothing.
'If Me.cboxIngPack = 2 Then
' strWhere = strWhere & "([in_type_key] = " & Chr(34) & 2 & Chr
(34) & ") AND "
'ElseIf Me.cboxIngPack = 5 Then
' strWhere = strWhere & "([in_type_key] = " & Chr(34) & 5 & Chr
(34) & ") AND "
'End If

If Not IsNull(Me.txtFilterVendName) Then
strWhere = strWhere & "([en_vend_name] Like ""*" &
Me.txtFilterVendName & "*"") AND "
End If

If Not IsNull(Me.txtFilterItemName) Then
strWhere = strWhere & "([in_desc] Like ""*" &
Me.txtFilterItemName & "*"") AND "
End If
'Date field example. Use the format string to add the # delimiters
and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([po_recpt_recdt] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([po_recpt_recdt] < " & Format
(Me.txtEndDate + 1, conJetDate) & ") AND "
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub


I deleted the docmd.requery after i posted, i just realized that in
the code.

Yes, it'll make a difference,  but it shouldn't cause the Type Mismatch..

That having been said, I don't see where you're using strWhere anywhere in
running your query.

Not only that, but I don't believe your DoCmd.Requery statement is valid.
It's supposed to have a control name as a parameter.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


I'm thinking that the table has a time stamp on the date, would that
matter if I'm only looking for date and not date + time?

I have this code which I copied and pasted from allen browns search
example database. I changed the tbox code to reflect my code. When I
run the query it gives me a type mismatch error, any one know why?
Click to expand...
Here's allen brown's code: (I deleted the some of the other code that
doesn't pertain.)
Click to expand...
Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.
Click to expand...
'Date field example. Use the format string to add the # delimiters
and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
End If
Click to expand...
'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate
+ 1, conJetDate) & ") AND "
End If
Click to expand...
End Sub
Click to expand...
Here is my code:
Click to expand...
Private Sub cmdFilter_Click()
Click to expand...
Dim strdoc As String
strdoc = "qryMakeTableQuickOrder"
DoCmd.Requery
Click to expand...
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.
Click to expand...
'Date field example. Use the format string to add the # delimiters
and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([po_recpt_recdt] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
End If
Click to expand...
'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([po_recpt_recdt] < " & Format
(Me.txtEndDate + 1, conJetDate) & ") AND "
End If
Click to expand...
End Sub
Click to expand...
Why am I getting a type mismatch error?
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Are [gl_cmp_key], [so_brnch_key], [en_vend_key], [in_item_key],
[en_phfmt_key] and [in_comcd_key] all text fields?

If not, fix the double quotes:

If Not IsNull(Me.cboxCompany) Then
strWhere = strWhere & "([gl_cmp_key] = " & Me.cboxCompany &") AND "
End If

You've got the code Debug.Print strWhere in there. What's being written to
the Immediate Window? (Ctrl-G)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Here is Allen Brown's complete code:

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.


'***********************************************************************
'Look at each search box, and build up the criteria string from
the non-blank ones.

'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """)
AND "
End If

'Another text field example. Use Like to find anywhere in the
field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([MainName] Like ""*" &
Me.txtFilterMainName & "*"") AND "
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboFilterLevel) Then
strWhere = strWhere & "([LevelID] = " & Me.cboFilterLevel & ")
AND "
End If

'Yes/No field and combo example. If combo is blank or contains
"ALL", we do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([IsCorporate] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([IsCorporate] = False) AND "
End If

'Date field example. Use the format string to add the # delimiters
and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate
+ 1, conJetDate) & ") AND "
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub



Here is my complete code:

Private Sub cmdFilter_Click()

'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.



'***********************************************************************
'Look at each search box, and build up the criteria string from
the non-blank ones.

'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboxCompany) Then
strWhere = strWhere & "([gl_cmp_key] = """ & Me.cboxCompany &
""") AND "
End If

'Another text field example. Use Like to find anywhere in the
field.
If Not IsNull(Me.cboxBranch) Then
strWhere = strWhere & "([so_brnch_key] = """ & Me.cboxBranch &
""") AND "
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboxVendor) Then
strWhere = strWhere & "([en_vend_key] = """ & Me.cboxVendor &
""") AND "
End If

If Not IsNull(Me.cboxItem) Then
strWhere = strWhere & "([in_item_key] = """ & Me.cboxItem &
""") AND "
End If

'If Not IsNull(Me.cboxBuyer) Then
' strWhere = strWhere & "([en_phfmt_key] = """ & Me.cboxBuyer &
""") AND "
'End If

If Not IsNull(Me.cboxCommodity) Then
strWhere = strWhere & "([in_comcd_key] = """ &
Me.cboxCommodity & """) AND "
End If

'If Not IsNull(Me.cboxYear) Then
' strWhere = strWhere & "([Rec Date] = " & Me.cboxYear & ") AND
"
'End If

'Yes/No field and combo example. If combo is blank or contains
"ALL", we do nothing.
'If Me.cboxIngPack = 2 Then
' strWhere = strWhere & "([in_type_key] = " & Chr(34) & 2 & Chr
(34) & ") AND "
'ElseIf Me.cboxIngPack = 5 Then
' strWhere = strWhere & "([in_type_key] = " & Chr(34) & 5 & Chr
(34) & ") AND "
'End If

If Not IsNull(Me.txtFilterVendName) Then
strWhere = strWhere & "([en_vend_name] Like ""*" &
Me.txtFilterVendName & "*"") AND "
End If

If Not IsNull(Me.txtFilterItemName) Then
strWhere = strWhere & "([in_desc] Like ""*" &
Me.txtFilterItemName & "*"") AND "
End If
'Date field example. Use the format string to add the # delimiters
and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([po_recpt_recdt] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([po_recpt_recdt] < " & Format
(Me.txtEndDate + 1, conJetDate) & ") AND "
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub


I deleted the docmd.requery after i posted, i just realized that in
the code.

Yes, it'll make a difference, but it shouldn't cause the Type Mismatch.

That having been said, I don't see where you're using strWhere anywhere in
running your query.

Not only that, but I don't believe your DoCmd.Requery statement is valid.
It's supposed to have a control name as a parameter.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


I'm thinking that the table has a time stamp on the date, would that
matter if I'm only looking for date and not date + time?

I have this code which I copied and pasted from allen browns search
example database. I changed the tbox code to reflect my code. When I
run the query it gives me a type mismatch error, any one know why?
Click to expand...
Here's allen brown's code: (I deleted the some of the other code that
doesn't pertain.)
Click to expand...
Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.
Click to expand...
'Date field example. Use the format string to add the # delimiters
and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
End If
Click to expand...
'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate
+ 1, conJetDate) & ") AND "
End If
Click to expand...
End Sub
Click to expand...
Here is my code:
Click to expand...
Private Sub cmdFilter_Click()
Click to expand...
Dim strdoc As String
strdoc = "qryMakeTableQuickOrder"
DoCmd.Requery
Click to expand...
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.
Click to expand...
'Date field example. Use the format string to add the # delimiters
and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([po_recpt_recdt] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
End If
Click to expand...
'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([po_recpt_recdt] < " & Format
(Me.txtEndDate + 1, conJetDate) & ") AND "
End If
Click to expand...
End Sub
Click to expand...
Why am I getting a type mismatch error?
Click to expand...
Click to expand...
 
R

ryan.fitzpatrick3

Yes, those are text fields. Nothing pops up in immediate window. the
full error is, error type 13, type mismatch now

Are [gl_cmp_key], [so_brnch_key], [en_vend_key], [in_item_key],
[en_phfmt_key] and [in_comcd_key] all text fields?

If not, fix the double quotes:

  If Not IsNull(Me.cboxCompany) Then
    strWhere = strWhere & "([gl_cmp_key] = " & Me.cboxCompany &")AND "
  End If

You've got the code Debug.Print strWhere in there. What's being written to
the Immediate Window? (Ctrl-G)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


Here is Allen Brown's complete code:

Private Sub cmdFilter_Click()
    'Purpose:   Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      =only dates between (both
inclusive. _
                        Start date only = all dates from this one
onwards; _
                        End date only   = alldates up to (and
including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria
string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for
dates in a JET query string.

'***********************************************************************
    'Look at each search box, and build up the criteria string from
the non-blank ones.

'***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtFilterCity) Then
        strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """)
AND "
    End If

    'Another text field example. Use Like to find anywhere in the
field.
    If Not IsNull(Me.txtFilterMainName) Then
        strWhere = strWhere & "([MainName] Like ""*" &
Me.txtFilterMainName & "*"") AND "
    End If

    'Number field example. Do not add the extra quotes.
    If Not IsNull(Me.cboFilterLevel) Then
        strWhere = strWhere & "([LevelID] = " & Me.cboFilterLevel & ")
AND "
    End If

    'Yes/No field and combo example. If combo is blank or contains
"ALL", we do nothing.
    If Me.cboFilterIsCorporate = -1 Then
        strWhere = strWhere & "([IsCorporate] = True) AND "
    ElseIf Me.cboFilterIsCorporate = 0 Then
        strWhere = strWhere & "([IsCorporate] = False) AND "
    End If

    'Date field example. Use the format string to add the # delimiters
and get the right international format.
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([EnteredOn] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
    End If

    'Another date field example. Use "less than the next day" since
this field has times as well as dates.
    If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
        strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate
+ 1, conJetDate) & ") AND "
    End If

'***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ")
to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove
the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line..
Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere

        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

Here is my complete code:

Private Sub cmdFilter_Click()

    'Purpose:   Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      =only dates between (both
inclusive. _
                        Start date only = all dates from this one
onwards; _
                        End date only   = alldates up to (and
including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria
string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for
dates in a JET query string.

'***********************************************************************
    'Look at each search box, and build up the criteria string from
the non-blank ones.

'***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.cboxCompany) Then
        strWhere = strWhere & "([gl_cmp_key] = """ & Me.cboxCompany &
""") AND "
    End If

    'Another text field example. Use Like to find anywhere in the
field.
    If Not IsNull(Me.cboxBranch) Then
        strWhere = strWhere & "([so_brnch_key] = """ & Me.cboxBranch &
""") AND "
    End If

    'Number field example. Do not add the extra quotes.
    If Not IsNull(Me.cboxVendor) Then
        strWhere = strWhere & "([en_vend_key] = """ & Me.cboxVendor &
""") AND "
    End If

    If Not IsNull(Me.cboxItem) Then
        strWhere = strWhere & "([in_item_key] = """ & Me.cboxItem &
""") AND "
    End If

    'If Not IsNull(Me.cboxBuyer) Then
    '    strWhere = strWhere & "([en_phfmt_key] = """ & Me.cboxBuyer &
""") AND "
    'End If

    If Not IsNull(Me.cboxCommodity) Then
        strWhere = strWhere & "([in_comcd_key] = """ &
Me.cboxCommodity & """) AND "
    End If

    'If Not IsNull(Me.cboxYear) Then
    '    strWhere = strWhere & "([Rec Date] = " & Me.cboxYear& ") AND
"
    'End If

    'Yes/No field and combo example. If combo is blank or contains
"ALL", we do nothing.
    'If Me.cboxIngPack = 2 Then
    '    strWhere = strWhere & "([in_type_key] = " & Chr(34) & 2 & Chr
(34) & ") AND "
    'ElseIf Me.cboxIngPack = 5 Then
    '    strWhere = strWhere & "([in_type_key] = " & Chr(34) & 5 & Chr
(34) & ") AND "
    'End If

    If Not IsNull(Me.txtFilterVendName) Then
        strWhere = strWhere & "([en_vend_name] Like ""*" &
Me.txtFilterVendName & "*"") AND "
    End If

    If Not IsNull(Me.txtFilterItemName) Then
        strWhere = strWhere & "([in_desc] Like ""*" &
Me.txtFilterItemName & "*"") AND "
    End If
    'Date field example. Use the format string to add the # delimiters
and get the right international format.
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([po_recpt_recdt] >= " & Format
(Me.txtStartDate, conJetDate) & ") AND "
    End If

    'Another date field example. Use "less than the next day" since
this field has times as well as dates.
    If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
        strWhere = strWhere & "([po_recpt_recdt] < " & Format
(Me.txtEndDate + 1, conJetDate) & ") AND "
    End If

'***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ")
to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove
the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line..
Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere

        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

I deleted the docmd.requery after i posted, i just realized that in
the code.

Yes, it'll make a difference, but it shouldn't cause the Type Mismatch.
Click to expand...
That having been said, I don't see where you're using strWhere anywherein
running your query.
Click to expand...
Not only that, but I don't believe your DoCmd.Requery statement is valid.
It's supposed to have a control name as a parameter.
Click to expand...
<[email protected]> wrote in message
Click to expand...
I'm thinking that the table has a time stamp on the date, would that
matter if I'm only looking for date and not date + time?
Click to expand...
On Jan 12, 11:01 am, (e-mail address removed) wrote:
Click to expand...

...

read more »
Click to expand...
 
R

ryan.fitzpatrick3

this is the line it gets stuck at

strWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
 
R

ryan.fitzpatrick3

I copied the same code as Allen Brown's, I just changed the text
fields to my names.

this is the line it gets stuck at

strWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "

Are [gl_cmp_key], [so_brnch_key], [en_vend_key], [in_item_key],
[en_phfmt_key] and [in_comcd_key] all text fields?
Click to expand...
If not, fix the double quotes:
Click to expand...
  If Not IsNull(Me.cboxCompany) Then
    strWhere = strWhere & "([gl_cmp_key] = " & Me.cboxCompany &") AND "
  End If
Click to expand...
You've got the code Debug.Print strWhere in there. What's being writtento
the Immediate Window? (Ctrl-G)
Click to expand...
Click to expand...
 
J

John W. Vinson

this is the line it gets stuck at

strWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
Click to expand...

What's conJetDate? Does it include the # delimiters required for a date/time
field? If not, try

strWhere = strWhere & "([Date] < #" & Format(Me.txtEndDate + 1,
conJetDate) & "#) AND "

or

strWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1,
"\#mm/dd/yyyy\#") & ") AND "
 
R

ryan.fitzpatrick3

Yes it does, it's allen brown's vba code. i'll try those options
below. thanks.

this is the line it gets stuck at
Click to expand...
strWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
Click to expand...

What's conJetDate? Does it include the # delimiters required for a date/time
field? If not, try

strWhere = strWhere & "([Date] < #" & Format(Me.txtEndDate + 1,
conJetDate) & "#) AND "

or

strWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1,
"\#mm/dd/yyyy\#") & ") AND "
Click to expand...
 
R

ryan.fitzpatrick3

same error again, the type mismatch 13

this is the line it gets stuck at
Click to expand...
strWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
Click to expand...

What's conJetDate? Does it include the # delimiters required for a date/time
field? If not, try

strWhere = strWhere & "([Date] < #" & Format(Me.txtEndDate + 1,
conJetDate) & "#) AND "

or

strWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1,
"\#mm/dd/yyyy\#") & ") AND "
Click to expand...
 
J

John W. Vinson

same error again, the type mismatch 13

this is the line it gets stuck at
Click to expand...
strWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
Click to expand...

What's conJetDate? Does it include the # delimiters required for a date/time
field? If not, try

strWhere = strWhere & "([Date] < #" & Format(Me.txtEndDate + 1,
conJetDate) & "#) AND "

or

strWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1,
"\#mm/dd/yyyy\#") & ") AND "
Click to expand...
Click to expand...

What is the datatype of the [Date] field?

I'd really recommend coming up with another name for the field: it's a
reserved word and Access can and will get confused.

Perhaps you could post a few lines of code for context, such as the definition
of conJetDate and the rest of the definition of strWhere. I don't have Allen's
sample database handy.
 
R

ryan.fitzpatrick3

I changed date to Datereceived. Here's his URL

http://allenbrowne.com/ser-62.html

in the table the Datereceived is a date/time format.

same error again, the type mismatch 13
On Mon, 12 Jan 2009 13:41:26 -0800 (PST), (e-mail address removed) wrote:
this is the line it gets stuck at
strWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
What's conJetDate? Does it include the # delimiters required for a date/time
field? If not, try
strWhere = strWhere & "([Date] < #" & Format(Me.txtEndDate + 1,
conJetDate) & "#) AND "
or
strWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1,
"\#mm/dd/yyyy\#") & ") AND "
Click to expand...
Click to expand...

What is the datatype of the [Date] field?

I'd really recommend coming up with another name for the field: it's a
reserved word and Access can and will get confused.

Perhaps you could post a few lines of code for context, such as the definition
of conJetDate and the rest of the definition of strWhere. I don't have Allen's
sample database handy.
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

Require a field in search form 1
Listbox coding problem 8
Filter data by using a form and open a report based on this data 1
Search Form with a SubForm 3
Help with VBA with an unbound form 4
Allen Browne's Search Form - Debugging help needed 2
type mismatch error 13 1
Form filter- numbers rather than text 2

Top