Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2016
    Posts
    29
    Provided Answers: 1

    Answered: DLookup errors in Access VBA

    Hi again,

    I'm running into similar issues with DLookup as I was with DCount the other day. I have data I'm trying to extract from a data table. I've tried a variety of different syntax formats, but I keep getting either Run Time errors 2471 or 3464 whatever I try.. here's the relevant code:

    Dim var_counter As Integer
    Dim var_active_ID As Integer
    Dim var_num As Integer

    Set rec_source_data = db.OpenRecordset("select * from TBL_DATA_Combined order by ID_Number")

    var_num = DCount("ID_Number", "TBL_DATA_Combined", "filter = True")
    var_counter = rec_source_settings![f_assump_counter]
    var_active_ID = var_counter - var_num + 2

    var_salary = DLookup("Salary", "TBL_DATA_Combined", "ID_Number = ' " & var_active_ID & " ' ") 'I initially had the var_active_ID equation in the DLookup, but after the first error, I thought that might be a problem, so I broke up the steps

    Everything I've tried, be it making it [Salary] and [ID_Number], "ID_Number = '" & var_active_ID & "'", "ID_Number = " & var_active_ID, or a half-dozen other possibilities mentioned online, nothing has worked.. I'm hoping someone on here has a solution to my conundrum again, as I seem to me making a habit of finding new ways to use Access..!

    Thanks in advance

  2. Best Answer
    Posted by myle

    "as u are adding

    var_active_ID = var_counter - var_num + 2

    im ass umiing that var_active_ID is a number

    so the Crit for the Dlookup would be DLookup("Salary", "TBL_DATA_Combined","ID_Number =" & var_active_ID & "" )

    So if you still getting the error the DOES the var_active_ID value exist in the TBL_DATA_Combined table
    and does the Salary feild of the var_active_ID have a value init

    Q is the var_counter = rec_source_settings![f_assump_counter] have the right answer in it TO"


  3. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    as u are adding

    var_active_ID = var_counter - var_num + 2

    im ass umiing that var_active_ID is a number

    so the Crit for the Dlookup would be DLookup("Salary", "TBL_DATA_Combined","ID_Number =" & var_active_ID & "" )

    So if you still getting the error the DOES the var_active_ID value exist in the TBL_DATA_Combined table
    and does the Salary feild of the var_active_ID have a value init

    Q is the var_counter = rec_source_settings![f_assump_counter] have the right answer in it TO
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  4. #3
    Join Date
    Nov 2016
    Posts
    29
    Provided Answers: 1
    Worked like a charm

    Not sure I understand why the "" are needed at the end seeing as we're not adding another script behind the var_active_ID, but it works, so, thank you!

  5. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    I put them In as a good/bad Habit
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Tags for this Thread

Posting Permissions

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