Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi all,

    I am looking for a little syntax advice when using multiple criteria in a dlookup function. I have the following VBA:

    Me![Text228] = DLookup("[CLIN_DESCRIP]", "Cams_Piin_Data2", "[PIIN_CODE] = '" & Forms![supmod -> Mod]![PIIN_CODE] & "'" And "[CLIN] = '" & Me![CLIN] & "'")

    It will compile with no error but when run I get a run time error 13( type mismatch). Both PIIN_CODE and CLIN are text fields in the table.

    If i drop one of the criteria it works fine so I thought I could just add an AND with a second criteria but it appears that there is more to it than that.


    Can someone point me in the right direction?

    Thanks you
    Kevin
    Kevin

  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
    [quote name='mcneilkm' post='792124' date='04-Sep-2009 09:24']Hi all,

    I am looking for a little syntax advice when using multiple criteria in a dlookup function. I have the following VBA:

    Me![Text228] = DLookup("[CLIN_DESCRIP]", "Cams_Piin_Data2", "[PIIN_CODE] = '" & Forms![supmod -> Mod]![PIIN_CODE] & "'" And "[CLIN] = '" & Me![CLIN] & "'")

    It will compile with no error but when run I get a run time error 13( type mismatch). Both PIIN_CODE and CLIN are text fields in the table.

    If i drop one of the criteria it works fine so I thought I could just add an AND with a second criteria but it appears that there is more to it than that.


    Can someone point me in the right direction?

    Thanks you
    Kevin[/quote]
    It is hard to read, but I think you have a problem with apostrophes in the WHERE portion. It should be:

    , "[PIIN_CODE] = '" & Forms![supmod -> Mod]![PIIN_CODE] & "' And [CLIN] = '" & Me![CLIN] & "'")

    or this:

    , "[PIIN_CODE] = " & chr(34) & Forms![supmod -> Mod]![PIIN_CODE] & chr(34) & " And [CLIN] = " & chr(34) & Me![CLIN] & chr(34))

    (note: chr(34) is a double-quote.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='MarkLiquorman' post='792151' date='04-Sep-2009 16:08']It is hard to read, but I think you have a problem with apostrophes in the WHERE portion. It should be:

    , "[PIIN_CODE] = '" & Forms![supmod -> Mod]![PIIN_CODE] & "' And [CLIN] = '" & Me![CLIN] & "'")

    or this:

    , "[PIIN_CODE] = " & chr(34) & Forms![supmod -> Mod]![PIIN_CODE] & chr(34) & " And [CLIN] = " & chr(34) & Me![CLIN] & chr(34))

    (note: chr(34) is a double-quote.[/quote]


    Thank you,

    That worked perfectly. Looks like I had a few too many quotes.
    Kevin

Posting Permissions

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