Solved: Help Needed with Dlookup (and greatly appreciated)

[GregRA1]

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

[OBP]

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 & ")"

[CreganTur]

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

[geekgirlau]

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]

[GregRA1]

I just wanted to thank you all for your help!