Run Time Error 13 - Type mismatch, Something to with Date?? (1 Viewer)

M

mickey_lin_uk

Registered User.
Local time
Today, 00:21
Joined
Sep 22, 2004
Messages
24
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.

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 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
 
Mile-O

Mile-O

Back once again...
Local time
Today, 00:21
Joined
Dec 10, 2002
Messages
11,316
You can't use wildcards with dates and you are treating dates as strings. Dates should be delimited with # and not ' or ""
 
M

mickey_lin_uk

Registered User.
Local time
Today, 00:21
Joined
Sep 22, 2004
Messages
24
Thanks for the quick reply.

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

do you mean:

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

Michelle
 
Mile-O

Mile-O

Back once again...
Local time
Today, 00:21
Joined
Dec 10, 2002
Messages
11,316
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.
 
M

mickey_lin_uk

Registered User.
Local time
Today, 00:21
Joined
Sep 22, 2004
Messages
24
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.

Code: 
'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

Mile-O

Back once again...
Local time
Today, 00:21
Joined
Dec 10, 2002
Messages
11,316
Looking at your code I don't think you've written it well:

This would be my changes to your code:

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.
 
M

mickey_lin_uk

Registered User.
Local time
Today, 00:21
Joined
Sep 22, 2004
Messages
24
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

Mile-O

Back once again...
Local time
Today, 00:21
Joined
Dec 10, 2002
Messages
11,316
mickey_lin_uk said:
as i said before I haven't been writing VB code for very long thats why its a bit hit & miss..
Click to expand...

Don't worry about it. You'll get there. ;)
 
Mile-O

Mile-O

Back once again...
Local time
Today, 00:21
Joined
Dec 10, 2002
Messages
11,316
mickey_lin_uk said:
as i said before I haven't been writing VB code for very long thats why its a bit hit & miss..
Click to expand...

Don't worry about it. You'll get there. ;)
 
M

mickey_lin_uk

Registered User.
Local time
Today, 00:21
Joined
Sep 22, 2004
Messages
24
Hi All,

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

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

Mile-O

Back once again...
Local time
Today, 00:21
Joined
Dec 10, 2002
Messages
11,316
mickey_lin_uk said:
thought I'd let you know that i've got it working
Click to expand...

That code works? :confused:

I ask primarlily because of this line:
Code: 
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#
 
C

cable

Access For My Sins
Local time
Today, 00:21
Joined
Mar 11, 2002
Messages
228
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?
 
M

mickey_lin_uk

Registered User.
Local time
Today, 00:21
Joined
Sep 22, 2004
Messages
24
im sure you understand it better than me but i works so im happy with that at the moment.

Michelle
 
You must log in or register to reply here.

Similar threads

C
Invalid Use of Null
2
Replies
20
Views
519
chuckcoleman
C
B
Overlapping dates with some null data
Replies
7
Views
381
bilakos93
B
oxicottin
Cant get form filter to work correctly
Replies
8
Views
198
plog
P
oxicottin
Solved Export search form data to excel
Replies
11
Views
589
oxicottin
oxicottin
oxicottin
Solved Run-time error '3061'. Too few parameters. Expected 1
Replies
7
Views
776
theDBguy
theDBguy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom