Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: DLookUp with double criteria - help need

    Hi,

    I have tbl tblEmployees with 2 fields in it as below
    strEmpName (text)
    strEmpPassword (Text)

    A form is created where in I placed one bound combo "CboUser " to select the user from the above table and one unbound text box called "txtPassword" is placed.

    For further action, I am selecting the user from combo and entering the password in txtPassword. In the after update event of txtPassword I placed below condition to go for ahead.

    If DLookup("strEmpPassword", "tblEmployees", "[strEmpName]='" & Forms!F_AdminPass!CboUser & "'" _
    And "strEmpPassword = '" & Forms!F_AdminPass!txtPassword & "'") Then

    The code is producing 'Run-time error 13' - Type mismatch.

    I did not understand why? Do I missed something here?

    Please advice.
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the third "criteria" part should be exactly like the WHERE part of SQL but without the word "WHERE".

    If DLookup("strEmpPassword", "tblEmployees", "[strEmpName]= '" & Forms!F_AdminPass!CboUser _
    & "'" And strEmpPassword = '" & Forms!F_AdminPass!txtPassword & "'") Then

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks Izy,

    In your code and my code there is diff of only (")

    If DLookup("strEmpPassword", "tblEmployees", "[strEmpName]= '" & Forms!F_AdminPass!CboUser _
    & "'" And strEmpPassword = '" & Forms!F_AdminPass!txtPassword & "'") Then

    If count (") they are 11 only. I think somewhere " is missed there.

    It produces syntax error at the bold character.

    Regards,
    With kind regards,
    Ashfaque

  4. #4
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello Ashfaque!

    Look at "DemoEmpPasswordA2000.mdb".
    Open Form1 and try. It is my way for this problem.
    Attached Files Attached Files

  5. #5
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks MStef,

    It is simply good. This way also can be done. I dont want third box on the form even hidden.

    Besides, what I am concern to is how to set DLookup with double criteria.

    But thanks for your lovely sweet trick.
    With kind regards,
    Ashfaque

  6. #6
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    You don't need a double criteria.
    Look at Demo a new one.
    Attached Files Attached Files

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by izyrider
    the third "criteria" part should be exactly like the WHERE part of SQL but without the word "WHERE".
    <snip>
    izy
    Izy just gave you the answer there.
    Code:
    WHERE  firstname = 'George'
    AND    username = 'GeorgeV'
    Becomes
    Code:
    Dim firstName As String
    Dim userName As String
    Dim whereClause As string
    
    firstName = "George"
    userName = "GeorgeV"
    
    whereClause = "firstname = '" & firstname & "' AND username = '" & userName & "'"
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Try this:

    Code:
    If DCount("strEmpPassword", "tblEmployees", "[strEmpName]= '" & Forms!F_AdminPass!cboUser & "' And strEmpPassword = '" & Forms!F_AdminPass!txtPassword & "'") > 0 Then
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Excellent StarTrekker...

    It works now....

    Thanks a lot.
    With kind regards,
    Ashfaque

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Where's the fun in giving the answer on a plate?
    George
    Home | Blog

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Ashfaque,

    - you are right, i had a quote extra
    StarTrekker has fixed that.

    take a close look at your original and you will see that you were inadvertantly attempting a bizarre boolean operation with that And

    blah-blah-with-missing-closing-) And some-more-stuff-with-extra-closing-)

    in this case, type-mismatch is Access' way of telling you that it hasn't got a clue what went wrong.

    sorry for my stupid typo this morning: as Trekker has said, it should have read:

    If DLookup("strEmpPassword", "tblEmployees", "[strEmpName]= '" & Forms!F_AdminPass!CboUser _
    & "' And strEmpPassword = '" & Forms!F_AdminPass!txtPassword & "'") Then

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by georgev
    Where's the fun in giving the answer on a plate?
    I guess I just like to keep s**t simple

    Life is too short to deliberately complicate matters.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Hi,
    Sorry for my late reply.

    I settled the same Izy, Thanks. The Dlookup example is smoothly working. Thats what I was looking for.

    The DCount also ok but I found it responses when there is only one user. But can play and set for him

    Anyhow thanks guys for your time.
    With kind regards,
    Ashfaque

Posting Permissions

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