Run Time Error 13 - Type mismatch, Something to with Date??

[mickey_lin_uk]

Hi all,
I'm trying to write some VB code, 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

 

[mickey_lin_uk]

just to say i'm using Access 2002

 

[Mile-O]

You can't use wildcards with dates and you are treating dates as strings. Dates should be delimited with # and not ' or ""

 

[mickey_lin_uk]

Thanks for the quick reply.

Sorry im new to writing code, how would i write the #?

do you mean:

If IsNull(Me.txtenddate.Value) Then
        Date_Due = "Like #
    Else
        Date_Due = "='" & Me.txtenddate.Value & #
    End If

Michelle

 

[Mile-O]

With your testing for isnull, there is no need to check if the control is Null ergo you can just ignore it as everything will be caught anyway. If it has a value then you can add criteria.

If Not IsNull(Me.txtenddate) Then
Date_Due = "=#" & Me.txtenddate & "#"
End If

There's no need to use the .Value property as it is the default property for a control.

 

[mickey_lin_uk]

Thanks! I'll try that.

Michelle

 

[mickey_lin_uk]

If I select/type

Commercial- Angie
Beginning Date -10/1/2004
End Date -10/30/2004

It brings up the error 13 msg & if you click debug it highlights the below line.

'Build criteria for Date due
    If Not IsNull(Me.txtbegdate) Then
        [COLOR=Orange]Date_Due = "=#" & Me.txtbegdate & "#"[/COLOR]
    End If

However if I just select:
Commercial- Angie

I get 'Syntax error (missing operator) in query expression.

Any ideas?

thanks

Michelle

 

[Mile-O]

Looking at your code I don't think you've written it well:

This would be my changes to your code:

Private Sub CmdApplyfilter_Click()

    Const ReportName As String = "rptRFQ Receipt to Tender Sent"

    Dim strCriteria As String

    'Code to automatically open report
    If SysCmd(acSysCmdGetObjectState, acReport, ReportName) <> acObjStateOpen Then
       DoCmd.OpenReport ReportName, acViewPreview, strFilter
    End If
    
    'Build Criteria string for Commercial Staff
    If Not IsNull(Me.cboCommercial) Then
        strCriteria = strCriteria & "[Commercial] = """ & Me.cboCommercial & """ AND "
    End If
    
    'Build Criteria string for Customer
    If Not IsNull(Me.CboCustomer) Then
        strCriteria = strCriteria & "[Customer] = """ & Me.CboCustomer & """ AND "
    End If
    
    'Build criteria for Date due for Beginning Date
    If Not IsNull(Me.txtbegdate) Then
        strCriteria = strCriteria & "[Date Due] >= #" & Me.txtbegdate & "# AND "
    End If
    
    'Build criteria for Date due for End Date
    If Not IsNull(Me.txtEndDate) Then
        strCriteria = strCriteria & "[Date Due] <= #" & Me.txtEndDate & "# AND "
    End If
    
    'Build Criteria string for Status
    If Not IsNull(Me.CboStatus) Then
        strCriteria = strCriteria & "[Order Status] = """ & Me.CboStatus & """ AND "
    End If
    
    If Len(strCriteria) <> 0 Then
        strCriteria = Left(strCriteria, Len(strCriteria) - 5)
    End If
        
    'Apply the filter and switch on
    With Reports(ReportName)
        .Filter = strFilter
        .FilterOn = True
    End With
    
End Sub

Personally, I'd build a QueryDef rather than use Filters and have the report bound to the ever-changing QueryDef by default.

 

[mickey_lin_uk]

thanks for your suggestions, as i said before I haven't been writing VB code for very long thats why its a bit hit & miss..

Michelle

 

[Mile-O]

as i said before I haven't been writing VB code for very long thats why its a bit hit & miss..

Don't worry about it. You'll get there.

 

[Mile-O]

as i said before I haven't been writing VB code for very long thats why its a bit hit & miss..

Don't worry about it. You'll get there.

 

[mickey_lin_uk]

Hi All,

Thanks for all your help, thought I'd let you know that i've got it working by using the following code:

Dim Date_Due As Date

  '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

 

[Mile-O]

thought I'd let you know that i've got it working

That code works?

I ask primarlily because of this line:

dteDate_Due = "Between" & Format$(Me.txtbegdate.Value, "\#mm\/dd\/yyyy\#") & "And" & Format$(Me.txtenddate.Value, "\#mm\/dd\/yyyy\#")

There's not space in the criteria and it will read as:

Between#01/01/05#And#04/01/05#

instead of:

Between #01/01/05# And #04/01/05#

 

[cable]

probly the #'s, the parse finds those and gives them to whatever actually does the between...although it can't be parseing 'between' based on the full word can it, maybe it only looks for part?

 

[mickey_lin_uk]

im sure you understand it better than me but i works so im happy with that at the moment.

Michelle