Runtime error 13 Type mismatch - something to do with date??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I'm trying to write some VB code in Access 2002, the user selects data using
a form & when they hit ok it brings up a report displaying all the records
that match.

The form has three combo boxes & two text boxes.

Commercial (combo)
Customer (Combo)
Status (combo)
Beginning Date (txt)
End Date (Txt)

So the user could be really specific & use all of the criteria to narrow
down the records shown by selecting data in all options or just one/two combo
boxes/text boxes.

E.g user selected Commercial 'Angie',
Beginning Date '10/1/2004'
End Date '10/30/2004'

I'm having a problem with a 'Run Time Error 13' 'Type mismatch'. I think it
is something to with declaring the date in my Dim statement.

code:
Option Compare Database

Private Sub CmdApplyfilter_Click()

Dim StrCommercial As String
Dim StrCustomer As String
Dim Date_Due As Date
Dim StrStatus As String
Dim StrFilter As String

Date_Due = Date

'Code to automatically open report
If SysCmd(acSysCmdGetObjectState, acReport, "rptRFQ Receipt to Tender
Sent") <> acObjStateOpen Then
DoCmd.OpenReport "rptRFQ Receipt to Tender Sent", acViewPreview,
StrFilter
End If

'Build Criteria string for Commercial Staff
If IsNull(Me.Cbocommercial.Value) Then
StrCommercial = "Like '*'"
Else
StrCommercial = "='" & Me.Cbocommercial.Value & "'"
End If


'Build Criteria string for Customer
If IsNull(Me.CboCustomer.Value) Then
StrCustomer = "Like '*'"
Else
StrCustomer = "='" & Me.CboCustomer.Value & "'"
End If

'Build criteria for Date due for Beginning Date
If IsNull(Me.txtbegdate.Value) Then
Date_Due = "Like '*'"
Else
Date_Due = "='" & Me.txtbegdate.Value & "'"
End If

'Build criteria for Date due for End Date
If IsNull(Me.txtenddate.Value) Then
Date_Due = "Like '*'"
Else
Date_Due = "='" & Me.txtenddate.Value & "'"
End If


'Build Criteria string for Status
If IsNull(Me.CboStatus.Value) Then
StrStatus = "Like '*'"
Else
StrStatus = "='" & Me.CboStatus.Value & "'"
End If

'Combine criteria strings into WHERE clause for the filter
StrFilter = " [Commercial] " & StrCommercial & " AND [Customer] " &
StrCustomer & " AND [Date Due] " & Date_Due & " AND [Order Status] " &
StrStatus

'Apply the filter and switch on
With Reports![rptRFQ Receipt to Tender Sent]
.Filter = StrFilter
.FilterOn = True

End With

End Sub

Any help would be greatly appreciated!

Thanks in advanced

Michelle
 
mickeylin said:
Hi all,
I'm trying to write some VB code in Access 2002, the user selects data using
a form & when they hit ok it brings up a report displaying all the records
that match.

The form has three combo boxes & two text boxes.

Commercial (combo)
Customer (Combo)
Status (combo)
Beginning Date (txt)
End Date (Txt)

So the user could be really specific & use all of the criteria to narrow
down the records shown by selecting data in all options or just one/two combo
boxes/text boxes.

E.g user selected Commercial 'Angie',
Beginning Date '10/1/2004'
End Date '10/30/2004'

I'm having a problem with a 'Run Time Error 13' 'Type mismatch'. I think it
is something to with declaring the date in my Dim statement.

code:
Option Compare Database

Private Sub CmdApplyfilter_Click()

Dim StrCommercial As String
Dim StrCustomer As String
Dim Date_Due As Date
Dim StrStatus As String
Dim StrFilter As String

Date_Due = Date

'Code to automatically open report
If SysCmd(acSysCmdGetObjectState, acReport, "rptRFQ Receipt to Tender
Sent") <> acObjStateOpen Then
DoCmd.OpenReport "rptRFQ Receipt to Tender Sent", acViewPreview,
StrFilter
End If

'Build Criteria string for Commercial Staff
If IsNull(Me.Cbocommercial.Value) Then
StrCommercial = "Like '*'"
Else
StrCommercial = "='" & Me.Cbocommercial.Value & "'"
End If


'Build Criteria string for Customer
If IsNull(Me.CboCustomer.Value) Then
StrCustomer = "Like '*'"
Else
StrCustomer = "='" & Me.CboCustomer.Value & "'"
End If

'Build criteria for Date due for Beginning Date
If IsNull(Me.txtbegdate.Value) Then
Date_Due = "Like '*'"
Else
Date_Due = "='" & Me.txtbegdate.Value & "'"
End If

'Build criteria for Date due for End Date
If IsNull(Me.txtenddate.Value) Then
Date_Due = "Like '*'"
Else
Date_Due = "='" & Me.txtenddate.Value & "'"
End If


'Build Criteria string for Status
If IsNull(Me.CboStatus.Value) Then
StrStatus = "Like '*'"
Else
StrStatus = "='" & Me.CboStatus.Value & "'"
End If

'Combine criteria strings into WHERE clause for the filter
StrFilter = " [Commercial] " & StrCommercial & " AND [Customer] " &
StrCustomer & " AND [Date Due] " & Date_Due & " AND [Order Status] " &
StrStatus

'Apply the filter and switch on
With Reports![rptRFQ Receipt to Tender Sent]
.Filter = StrFilter
.FilterOn = True

End With

End Sub

Any help would be greatly appreciated!

Thanks in advanced

Michelle
Click to expand...

Hi Michelle,

Unfortunately you seem not to have indicated which line the error occurs at,
but I can see a problem with the way you are handling dates. Date literals
need to be delimited by "#" characters. You have delimited them with quote
marks, which will make Access treat them as strings rather than dates, hence
type mismatch.

Also, because dates are not strings, you cannot use wildcards with dates.
To have your criteria allow any date, you will need to ensure that the date
criterion is completely absent from your filter string.

Furthermore, as your code stands at the moment, the beginning date is
redundant. You are determining a value for Date_Due based on the beginning
date, and then you are immediately REPLACING it with a value based on the
ending date!
 
Thanks for your suggestions.
Is there a way to search between two dates in one field?

i.e. Date due & then the user wanting to search for due date between two date?

I have a query with the same search facilities and that works fine. As I
want to display the result of the query in a report, i though i had to write
the code for the combo/txt boes in VB. When I tried to write in SQL it
brought up the parameter boxes because some of the SQL had [].

Im new to all of this so sorryif im being slow!

Thanks
Michelle
 
Sounds like you want something similar to:

'Build criteria for Date due for Beginning Date and/or Ending Date
If IsNull(Me.txtbegdate.Value) Then
If IsNull(Me.txtenddate.Value) Then
Date_Due = ""
Else
Date_Due = "<= " & Format$(Me.txtenddate.Value,
"\#mm\/dd\/yyyy\#")
End If
Else
If IsNull(Me.txtenddate.Value) Then
Date_Due = ">= " & Format$(Me.txtbegdate.Value,
"\#mm\/dd\/yyyy\#")
Else
Date_Due = "Between " & Format$(Me.txtbegdate.Value,
"\#mm\/dd\/yyyy\#") & _
" And " & Format$(Me.txtenddate.Value, "\#mm\/dd\/yyyy\#")
End If
End If

'Combine criteria strings into WHERE clause for the filter
StrFilter = " [Commercial] " & StrCommercial & " AND [Customer] " &
StrCustomer & " AND [Order Status] " & StrStatus
If Len(DateDue) > 0 Then
StrFilter = StrFilter & " AND [Date Due] " & Date_Due
End If

What I'm doing is allowing for either (or both) of txtbegdate and txtenddate
to be null. If you specify only txtbegdate, it'll look for everything on or
after that date. If you specify only txtenddate, it'll look for everything
on or before that date. If you specify both, it looks only within that
range. If you specify neither, there's no need to put any reference in the
filter, since you want all of the records. (There's no such thing as a
wildcard for dates or numbers)
 
Hi all,
Just thought i'd let you know i'd got it working! Thanks for all your help,
Douglas your suggestion with a little change finally made it work!

The code is:

'Build criteria for Date due for Beginning / End Date
If IsNull(Me.txtbegdate.Value) Then
If IsNull(Me.txtenddate.Value) Then
dteDate_Due = "= #"
Else
dteDate_Due = "<=" & Format$(Me.txtenddate.Value,
"\#mm\/dd\/yyyy\#")
End If

Else

If IsNull(Me.txtenddate.Value) Then
dteDate_Due = ">= " & Format$(Me.txtbegdate.Value,
"\#\/mm\/dd\/yyyy\#")
Else
dteDate_Due = "Between" & Format$(Me.txtbegdate.Value,
"\#mm\/dd\/yyyy\#") & "And" & Format$(Me.txtenddate.Value, "\#mm\/dd\/yyyy\#")
End If
End If

Many Thanks

Michelle
 
Back
Top