-
Solved: Help Needed with Dlookup (and greatly appreciated)
Hi,
This is my first time posting here and i am running into some problems using a Dlookup.
the code i am using is:
DLookup("[SumOfRSLT_1]", "qry_sumlaborhours", "[ACCTG_ACT] = ""charge1""")= x
charge1 is a variable defined as string
[ACCTG_ACT] is a text field
my problem is this: if i obtain a value for charge1 in the immediate window, and replace charge1 with that value in the dlookup, the dlookup will run perfectly. However, if i leave the variable in, it returns a null value.
I need to use a variable as i'm running through this dlookup many times to place values directly into excel.
Sorry if i didnt explain this well, i'm pretty new to VBA and my knowledge is limited
-
Greg, welcome to the Forum.
I do not use Dlookup functions very often, but I would think that your problem stems from the fact that you are putting the Variable in Quotes. It is Ok to that with a real value, but when you do it with the Variable you are asking Access to evaluate "[ACCTG_ACT] = the word charge1, not the variable.
I am also not sure about the "= x" on the end of the function either, I would have thought you would have wnated that at the front to set x = to the looked up value?
Try
DLookup("[SumOfRSLT_1]", "qry_sumlaborhours", "[ACCTG_ACT] = " & charge1 & ")"
-
DLookup is a finnicky function- you have to treat it like a SQL string- which means that you need to make sure you wrap any variables used with data qualifiers. If charge1 is a string variable, then you need to wrap it with single quotes, which is the string data qualifier. Like this:
[VBA]DLookup("[SumOfRSLT_1]", "qry_sumlaborhours", "[ACCTG_ACT] = '" & charge1 & "')"[/VBA]
If charge1 is a number value, you do not need qualifiers, and (for future reference) if it's a date, you wrap it with pound signs (#) instead of single quotes.
HTH
-
Randy, I'd watch those single quotes if there is the remotest possibility that your string could contain one (e.g. O'Hara, 'Optimum' etc.).
You can use
[vba]x = DLookup("[SumOfRSLT_1]", "qry_sumlaborhours", "[ACCTG_ACT] = """ & charge1 & """)[/vba]
or
[vba]x = DLookup("[SumOfRSLT_1]", "qry_sumlaborhours", "[ACCTG_ACT] = " & chr(34) & charge1 & chr(34))[/vba]
-
I just wanted to thank you all for your help!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules