Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mismatch (Access 2003)

    The following gives me: Data type mismatch in criteria expression.

    Private Sub prvRptC3_Click()
    Dim strID As String
    Dim rpt As Access.Report
    Dim strFilter As String
    strID = Me![SRT_CN ]
    strFilter = "[SRT_CN ] = " & Chr$(39) & strID & Chr$(39)
    Debug.Print "Filter: " & strFilter
    DoCmd.OpenReport "rpt 01 LogIn - Warranty", acViewPreview
    Set rpt = [Reports]![rpt 01 LogIn - Warranty]
    rpt.FilterOn = True
    rpt.Filter = strFilter
    rpt.Caption = "Warranty Information for this CN " & strID
    ErrorHandlerExit:
    Exit Sub
    ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit
    End Sub

    I realize that it's probably due to the fact that the SRT_CN is numeric based on an Autonumber, and I realize that I should be, I think, dropping the quotation marks in:

    strFilter = [SRT_CN ] = & Chr$(39) & strID & Chr$(39)

    but this gives me a compile error.

    I tried dropping the various combinations of &'s, but as they used to say in English Class, "It got Worser and Worser"

    I'd appreciate any suggestions as to where I'm going programatically astray,


    Cheers, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Cheers,
    Andy

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Mismatch (Access 2003)

    Instead of:
    strID = Me![SRT_CN ]
    strFilter = "[SRT_CN ] = " & Chr$(39) & strID & Chr$(39)

    Just use:
    strFilter = "SRT_CN=" & Me!SRT_CN
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mismatch (Access 2003)

    Mark,

    Thanks kindly for the quick and totally accurate reply. I figured it would be something almost as simple as I.

    Cheers <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Cheers,
    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mismatch (Access 2003)

    For future reference:

    If SRT_CN is a number, currency or yes/no field:

    strFilter = "SRT_CN=" & Me!SRT_CN

    If SRT_CN is a date/time field

    strFilter = "SRT_CN=#" & Format(Me!SRT_CN, "mm/dd/yyyy") & "#"

    If SRT_CN is a text or memo field:

    strFilter = "SRT_CN=" & Chr(34) & Me!SRT_CN & Chr(34)

    The latter will work as long as the value you want to filter on (Me!SRT_CN) doesn't contain double quotes ". If it does, you can use Chr(39) instead of Chr(34):

    strFilter = "SRT_CN=" & Chr(39) & Me!SRT_CN & Chr(39)

    This will work as long as Me!SRT_CN doesn't contain single quotes '. If Me!SRT_CN may contain both single quotes ' and double quotes ", it is more difficult to handle.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mismatch (Access 2003)

    Hans,

    Just caught your invaluable note. You really should write an Access HowTo book. Mind you, it would probable go to six volumes, but I'd be the first to buy,

    Cheers <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Cheers,
    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •