DLookUp syntax help please

[JWCrosby]

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

 

[Carl Rapson]

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

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

 

[JWCrosby]

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

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

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

 

[fredg]

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

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.

 

[Carl Rapson]

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

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

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

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

 

[JWCrosby]

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

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

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.

 

[fredg]

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

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

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.

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