DLookUp syntax help please

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here's what isn't working (get error#):
=DLookUp("BC_WCC","qryBC_Tax_Select","[EmployeeID] = """ & [EmployeeID] &
"And [MonthID] = '" & [Month2] & "'")

EmployeeID is a number.
MonthID in the query is a text field.
Month2 is a control on the form that is text.

I've used single and double quotes, which I know can be problematic, but it
helps me understand the pieces...but I still can't get it right.

Thanks in advance.

Jerry
 
JWCrosby said:
Here's what isn't working (get error#):
=DLookUp("BC_WCC","qryBC_Tax_Select","[EmployeeID] = """ & [EmployeeID] &
"And [MonthID] = '" & [Month2] & "'")

EmployeeID is a number.
MonthID in the query is a text field.
Month2 is a control on the form that is text.

I've used single and double quotes, which I know can be problematic, but
it
helps me understand the pieces...but I still can't get it right.

Thanks in advance.

Jerry
Click to expand...

It looks like you're missing the closing quotes forthe EmployeeID:

=DLookUp("BC_WCC","qryBC_Tax_Select","[EmployeeID] = """ & [EmployeeID] &
"""And [MonthID] = '" & [Month2] & "'")

It might help to be more consistent on the use of single and double quotes:

=DLookUp("BC_WCC","qryBC_Tax_Select","[EmployeeID] = '" & [EmployeeID] &
"'And [MonthID] = '" & [Month2] & "'")

Carl Rapson
 
I was hopeful there for a moment, but it didn't work. Still getting #Error,
but I most certainly appreciate your effort to help me.

Would it be helpful to add that the "Month2" field is a control in the form
page header with this syntax: =DLookUp("[Month]","tblMonths","[SortOrder] = "
& "2") It is working and results in "Feb"

Jerry

Carl Rapson said:
JWCrosby said:
Here's what isn't working (get error#):
=DLookUp("BC_WCC","qryBC_Tax_Select","[EmployeeID] = """ & [EmployeeID] &
"And [MonthID] = '" & [Month2] & "'")

EmployeeID is a number.
MonthID in the query is a text field.
Month2 is a control on the form that is text.

I've used single and double quotes, which I know can be problematic, but
it
helps me understand the pieces...but I still can't get it right.

Thanks in advance.

Jerry
Click to expand...

It looks like you're missing the closing quotes forthe EmployeeID:

=DLookUp("BC_WCC","qryBC_Tax_Select","[EmployeeID] = """ & [EmployeeID] &
"""And [MonthID] = '" & [Month2] & "'")

It might help to be more consistent on the use of single and double quotes:

=DLookUp("BC_WCC","qryBC_Tax_Select","[EmployeeID] = '" & [EmployeeID] &
"'And [MonthID] = '" & [Month2] & "'")

Carl Rapson
Click to expand...
 
Here's what isn't working (get error#):
=DLookUp("BC_WCC","qryBC_Tax_Select","[EmployeeID] = """ & [EmployeeID] &
"And [MonthID] = '" & [Month2] & "'")

EmployeeID is a number.
MonthID in the query is a text field.
Month2 is a control on the form that is text.

I've used single and double quotes, which I know can be problematic, but it
helps me understand the pieces...but I still can't get it right.

Thanks in advance.

Jerry
Click to expand...

If, as you state, EmployeeID is a Number datatype, your syntax is as
if it were Text datatype.

Try:
=DLookUp("[BC_WCC]","qryBC_Tax_Select","[EmployeeID] = " &
[EmployeeID] & " And [MonthID] = '" & [Month2] & "'")

make sure the name of the control is not the same as any field used in
the above expression.
 
That does make a difference. I don't think you can refer to a calculated
field value like that. If possible, you should link tblMonths to your query,
so that the month value is available without having to use a second DLookUp.
If you can't put the link in qryBC_Tax_Select directly, you could create
another query linking qryBC_Tax_Select to tblMonths and use that query in
your DLookUp for BC_WCC.

Carl Rapson

JWCrosby said:
I was hopeful there for a moment, but it didn't work. Still getting #Error,
but I most certainly appreciate your effort to help me.

Would it be helpful to add that the "Month2" field is a control in the
form
page header with this syntax: =DLookUp("[Month]","tblMonths","[SortOrder]
= "
& "2") It is working and results in "Feb"

Jerry

Carl Rapson said:
JWCrosby said:
Here's what isn't working (get error#):
=DLookUp("BC_WCC","qryBC_Tax_Select","[EmployeeID] = """ & [EmployeeID]
&
"And [MonthID] = '" & [Month2] & "'")

EmployeeID is a number.
MonthID in the query is a text field.
Month2 is a control on the form that is text.

I've used single and double quotes, which I know can be problematic,
but
it
helps me understand the pieces...but I still can't get it right.

Thanks in advance.

Jerry
Click to expand...

It looks like you're missing the closing quotes forthe EmployeeID:

=DLookUp("BC_WCC","qryBC_Tax_Select","[EmployeeID] = """ & [EmployeeID] &
"""And [MonthID] = '" & [Month2] & "'")

It might help to be more consistent on the use of single and double
quotes:

=DLookUp("BC_WCC","qryBC_Tax_Select","[EmployeeID] = '" & [EmployeeID] &
"'And [MonthID] = '" & [Month2] & "'")

Carl Rapson
Click to expand...
Click to expand...
 
thanks, Fred, that got it to work!

Now, if you happen to come back and read this perhaps you could answer
another question. That DLookUp is pulling the correct number from the query
[BC_WCC] and in the report I want to show it as currency. While I have the
field formatted as currency, it isn't showing it as such.

[BC_WCC] in the query is drawn from a public function, if that helps any.

Any ideas on how to get it to show as currency? (If I don't hear back I'll
post as a separate question.)

Jerry

fredg said:
Here's what isn't working (get error#):
=DLookUp("BC_WCC","qryBC_Tax_Select","[EmployeeID] = """ & [EmployeeID] &
"And [MonthID] = '" & [Month2] & "'")

EmployeeID is a number.
MonthID in the query is a text field.
Month2 is a control on the form that is text.

I've used single and double quotes, which I know can be problematic, but it
helps me understand the pieces...but I still can't get it right.

Thanks in advance.

Jerry
Click to expand...

If, as you state, EmployeeID is a Number datatype, your syntax is as
if it were Text datatype.

Try:
=DLookUp("[BC_WCC]","qryBC_Tax_Select","[EmployeeID] = " &
[EmployeeID] & " And [MonthID] = '" & [Month2] & "'")

make sure the name of the control is not the same as any field used in
the above expression.
Click to expand...
 
thanks, Fred, that got it to work!

Now, if you happen to come back and read this perhaps you could answer
another question. That DLookUp is pulling the correct number from the query
[BC_WCC] and in the report I want to show it as currency. While I have the
field formatted as currency, it isn't showing it as such.

[BC_WCC] in the query is drawn from a public function, if that helps any.

Any ideas on how to get it to show as currency? (If I don't hear back I'll
post as a separate question.)

Jerry

fredg said:
Here's what isn't working (get error#):
=DLookUp("BC_WCC","qryBC_Tax_Select","[EmployeeID] = """ & [EmployeeID] &
"And [MonthID] = '" & [Month2] & "'")

EmployeeID is a number.
MonthID in the query is a text field.
Month2 is a control on the form that is text.

I've used single and double quotes, which I know can be problematic, but it
helps me understand the pieces...but I still can't get it right.

Thanks in advance.

Jerry
Click to expand...

If, as you state, EmployeeID is a Number datatype, your syntax is as
if it were Text datatype.

Try:
=DLookUp("[BC_WCC]","qryBC_Tax_Select","[EmployeeID] = " &
[EmployeeID] & " And [MonthID] = '" & [Month2] & "'")

make sure the name of the control is not the same as any field used in
the above expression.
Click to expand...
Click to expand...

=Format(DLookUp("[BC_WCC]","qryBC_Tax_Select","[EmployeeID] = " &
[EmployeeID] & " And [MonthID] = '" & [Month2] & "'"),"Currency")
 
Back
Top