'Runtime error 13 - Type Mismatch' something to do with date?

[mickeyuk]

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.

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 StrBusiness 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

 

[xlbo]

Michelle - which line do you get the error on ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[mickeyuk]

I get the error in :

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

 

[xlbo]

You have declared Date_Due as a date but then try and assign a string to it - that can't be done

Try declaring Date_Due as VARIANT

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[xlbo]

You have declared Due_Date as a date but then try and assign a string to it - that can't be done

Try declaring DueDate as VARIAANT

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[mickeyuk]

I declared it as a Variant & when i typed:

Beginning Date: 10/1/2004
End Date: 10/30/2004

I got an error msg:

'Data Type mismatch in criteria expression'

However when I typed just

Beginning date: 10/1/2004
It returned all records.

Michelle

 

[xlbo]

ok - you got a bit of an issue - you need a DATE data type to make the query work but you need to assign a STRING to it in some instances

What you are going to need to do is use 2 variables - 1 string and 1 date and choose which to utilise, based on your test.

If IsNull(Me.txtbegdate.Value) Then
 'Use string var
   bln_Use_Str = TRUE
else
   bln_Use_Str = FALSE
end if
...
...
...
...& " AND [Date Due] " & if bln_Use_Str = TRUE then "Like '*'" else "='" & Me.txtenddate.Value & "'"

Just use logic to build the string

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[PHV]

Replace this:
Date_Due = "='" & Me.txtbegdate.Value & "'"
Bye this:
Date_Due = "=#" & Me.txtbegdate.Value & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Can you not just use date literals instead of string literals?

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

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[mickeyuk]

Hi all,

Thanks for all your help, i've managed to get it working using the followng code for date criteria:

  '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

Michelle