Results 1 to 7 of 7

Thread: Dlookup (97)

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dlookup (97)

    HELP!!!!
    I have a DLookUp function that isn't working properly, it returns the error "data type mismatch" and I'm not sure why. All fields are set to "text"/"string"...there shouldn't be a mismatch.

    Below is the code I'm using, and if someone could help me with this, I would really appreciate it.

    Thanks,

    Roberta

    Dim strPPA As String
    Dim strEIN As String
    strEIN = Forms!frmTracker!EIN

    DoCmd.GoToRecord acForm, "frmPhyChanges", acNewRec
    strPPA = DLookup("[ProPlanArea]", "tblProvInfo", "[ProEIN] = " & strEIN & "")
    Me!ProPlanArea = strPPA
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dlookup (97)

    Hi Roberta,

    You're missing quotes around your criteria in the DLookup statement.
    Try this:
    strPPA = Dlookup("[ProPlanArea]","tblProvInfo","[ProEIN]=" & Chr(34) & strEIN & Chr(34))

    Chr(34) is the ASCII equivalent for quotes (")

    HTH <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dlookup (97)

    Hi Mark,

    Thanks for the quick response....below is what I now have only with a new error:

    strPPA = DLookup("[ProPlanArea]", "tblProvInfo", "[ProEIN] =" & " & strEIN & ")

    The new error is : Syntax error missing in query expression.

    Any other suggestions?

    Thanks again.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Dlookup (97)

    Hi Roberta,
    You need to actually use exactly what Mark posted to effectively get the quotes passed into the criteria string - i.e. use the Chr(34) notation rather than replacing that with quotes yourself.
    You could also use:
    strPPA = DLookup("[ProPlanArea]", "tblProvInfo", "[ProEIN] ='" & strEIN & "'")
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dlookup (97)

    Good Morning Rory,

    Well this is turning into a nightmare...lol.

    I tried yours and Mark's exactly and both return the error...invalid use of Null. If I put my cursor over the "strEIN" in both the set statement and the function, it returns the value I'm looking for.

    Hmmmmmm....

    I know everyone is busy helping with others, but if you have a minute to take another look at this, I would really appreciate it.

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Dlookup (97)

    Hmm, the mention of Null makes me think that Dlookup isn't finding a match. Is there definitely a record matching the criteria in question? Is there an apostrophe in the strEIN string that you're searching for - that can cause problems.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dlookup (97)

    Thank you so much (both of you).

    I am working in a test db and was putting test data in the EIN field rather than putting a true value. Once I did that that fixed the problem...Rory..thanks for picking up on that...one I won't soon forget.

    Again Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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