DLookup syntax in control on form

[bpauley]

I have the following DLookup syntax in a control on a form. I am attempting
to get a $ rate (RateAmt) from a table entered in the CurrRateAmt text box
on the frmEnterMonthlyVisits form.
I get the #name error. Please help me understand what I have done wrong.
When I put an = sign in from of DLookup, I get an error message pop up with
omitted an operand or operator, entered invalid character or comma, entered
text without surrounding in quotation marks.

If I have not given adequate info, please tell me.

Dlookup("RateAmt";"tblFacilityRates";(((tblFacilityRates.FacilityID)=[forms]![frmEnterMonthlyVisits].[cbofacilityID])
AND
((tblFacilityRates.RateTypeID)=[forms]![frmEnterMonthlyVisits].[cboRateTypeID])
AND
((tblFacilityRates.RateTypeDescription)=[forms]![frmEnterMonthlyVisits].[txtposition]))

Thank you,
Bill

 

[Dirk Goldgar]

I have the following DLookup syntax in a control on a form. I am attempting
to get a $ rate (RateAmt) from a table entered in the CurrRateAmt text box
on the frmEnterMonthlyVisits form.
I get the #name error. Please help me understand what I have done wrong.
When I put an = sign in from of DLookup, I get an error message pop up
with omitted an operand or operator, entered invalid character or comma,
entered text without surrounding in quotation marks.

If I have not given adequate info, please tell me.

Dlookup("RateAmt";"tblFacilityRates";(((tblFacilityRates.FacilityID)=[forms]![frmEnterMonthlyVisits].[cbofacilityID])
AND
((tblFacilityRates.RateTypeID)=[forms]![frmEnterMonthlyVisits].[cboRateTypeID])
AND
((tblFacilityRates.RateTypeDescription)=[forms]![frmEnterMonthlyVisits].[txtposition]))

Thank you,
Bill

You need the equals sign, and also you need the whole criteria argument to
be enclosed in quotes. Try this:

=Dlookup("RateAmt";"tblFacilityRates";"(FacilityID=[forms]![frmEnterMonthlyVisits].[cbofacilityID])
AND (RateTypeID=[forms]![frmEnterMonthlyVisits].[cboRateTypeID]) AND
(RateTypeDescription=[forms]![frmEnterMonthlyVisits].txtposition])")

I've simplified the expression somewhat; see if that works.

 

[bpauley]

Hello Dirk,
Thanks for the quick response.
It still does not work. Still getting an error message pop up with omitted
an operand or operator, entered invalid character or comma, entered text
without surrounding in quotation marks.
Does it make a difference if the bound column in the combo box is not being
displayed? None of the combo boxes display the bound column, ContactID
displays a contact name (text), FacilityID displays a facility full name
(text) and RateTypeID displays a rate type (text).
Thanks,
Bill

I have the following DLookup syntax in a control on a form. I am
attempting to get a $ rate (RateAmt) from a table entered in the
CurrRateAmt text box on the frmEnterMonthlyVisits form.
I get the #name error. Please help me understand what I have done wrong.
When I put an = sign in from of DLookup, I get an error message pop up
with omitted an operand or operator, entered invalid character or comma,
entered text without surrounding in quotation marks.

If I have not given adequate info, please tell me.

Dlookup("RateAmt";"tblFacilityRates";(((tblFacilityRates.FacilityID)=[forms]![frmEnterMonthlyVisits].[cbofacilityID])
AND
((tblFacilityRates.RateTypeID)=[forms]![frmEnterMonthlyVisits].[cboRateTypeID])
AND
((tblFacilityRates.RateTypeDescription)=[forms]![frmEnterMonthlyVisits].[txtposition]))

Thank you,
Bill

You need the equals sign, and also you need the whole criteria argument to
be enclosed in quotes. Try this:

=Dlookup("RateAmt";"tblFacilityRates";"(FacilityID=[forms]![frmEnterMonthlyVisits].[cbofacilityID])
AND (RateTypeID=[forms]![frmEnterMonthlyVisits].[cboRateTypeID]) AND
(RateTypeDescription=[forms]![frmEnterMonthlyVisits].txtposition])")

I've simplified the expression somewhat; see if that works.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

(please reply to the newsgroup)

Hello Dirk,
Thanks for the quick response.
It still does not work. Still getting an error message pop up with
omitted an operand or operator, entered invalid character or comma,
entered text without surrounding in quotation marks.
Does it make a difference if the bound column in the combo box is not
being displayed? None of the combo boxes display the bound column,
ContactID displays a contact name (text), FacilityID displays a facility
full name (text) and RateTypeID displays a rate type (text).
Thanks,
Bill

You need the equals sign, and also you need the whole criteria argument
to be enclosed in quotes. Try this:

=Dlookup("RateAmt";"tblFacilityRates";"(FacilityID=[forms]![frmEnterMonthlyVisits].[cbofacilityID])
AND (RateTypeID=[forms]![frmEnterMonthlyVisits].[cboRateTypeID]) AND
(RateTypeDescription=[forms]![frmEnterMonthlyVisits].txtposition])")

I've simplified the expression somewhat; see if that works.

So long as the bound columns of the combo boxes are correct, it doesn't
matter what they're displaying. The only thing odd I see in your DLookup
expression is your use of the semicolon as an argument separater instead of
the comma that I would use; however, that's a regional setting, and it may
be correct for you. You could try using a comma instead and see if it
works.

Aside from that, I don't see the problem, so long as the form and control
names are not misspelled. You've double-checked that? Try executing the
DLookup from the Immediate window and see if you can debug it better there.

 

[bpauley]

I thought I did, my post appears in line with the others. Am I correct?

(please reply to the newsgroup)

Hello Dirk,
Thanks for the quick response.
It still does not work. Still getting an error message pop up with
omitted an operand or operator, entered invalid character or comma,
entered text without surrounding in quotation marks.
Does it make a difference if the bound column in the combo box is not
being displayed? None of the combo boxes display the bound column,
ContactID displays a contact name (text), FacilityID displays a facility
full name (text) and RateTypeID displays a rate type (text).
Thanks,
Bill

You need the equals sign, and also you need the whole criteria argument
to be enclosed in quotes. Try this:

=Dlookup("RateAmt";"tblFacilityRates";"(FacilityID=[forms]![frmEnterMonthlyVisits].[cbofacilityID])
AND (RateTypeID=[forms]![frmEnterMonthlyVisits].[cboRateTypeID]) AND
(RateTypeDescription=[forms]![frmEnterMonthlyVisits].txtposition])")

I've simplified the expression somewhat; see if that works.

So long as the bound columns of the combo boxes are correct, it doesn't
matter what they're displaying. The only thing odd I see in your DLookup
expression is your use of the semicolon as an argument separater instead
of the comma that I would use; however, that's a regional setting, and it
may be correct for you. You could try using a comma instead and see if it
works.

Aside from that, I don't see the problem, so long as the form and control
names are not misspelled. You've double-checked that? Try executing the
DLookup from the Immediate window and see if you can debug it better
there.

 

[bpauley]

sorry for the last post, I thought the (please reply to the newsgroup) was
your message. Did not look at the bottom of the post.
I will try to review in the immediate window and post my results.
Thank you Dirk
Bill

(please reply to the newsgroup)

Hello Dirk,
Thanks for the quick response.
It still does not work. Still getting an error message pop up with
omitted an operand or operator, entered invalid character or comma,
entered text without surrounding in quotation marks.
Does it make a difference if the bound column in the combo box is not
being displayed? None of the combo boxes display the bound column,
ContactID displays a contact name (text), FacilityID displays a facility
full name (text) and RateTypeID displays a rate type (text).
Thanks,
Bill

You need the equals sign, and also you need the whole criteria argument
to be enclosed in quotes. Try this:

=Dlookup("RateAmt";"tblFacilityRates";"(FacilityID=[forms]![frmEnterMonthlyVisits].[cbofacilityID])
AND (RateTypeID=[forms]![frmEnterMonthlyVisits].[cboRateTypeID]) AND
(RateTypeDescription=[forms]![frmEnterMonthlyVisits].txtposition])")

I've simplified the expression somewhat; see if that works.

So long as the bound columns of the combo boxes are correct, it doesn't
matter what they're displaying. The only thing odd I see in your DLookup
expression is your use of the semicolon as an argument separater instead
of the comma that I would use; however, that's a regional setting, and it
may be correct for you. You could try using a comma instead and see if it
works.

Aside from that, I don't see the problem, so long as the form and control
names are not misspelled. You've double-checked that? Try executing the
DLookup from the Immediate window and see if you can debug it better
there.

 

[bpauley]

Dirk,
I think you had it the first time around. I changed the ; to , and looked at
the spelling of everything in the syntax. I found a missing [ on the code
you returned to me. On the very last line of code on the word txtposition.
It got lost in the shuffle. Anyway, I appreciate your tutoring and it is
working now.
Thanks again,
Bill

(please reply to the newsgroup)

Hello Dirk,
Thanks for the quick response.
It still does not work. Still getting an error message pop up with
omitted an operand or operator, entered invalid character or comma,
entered text without surrounding in quotation marks.
Does it make a difference if the bound column in the combo box is not
being displayed? None of the combo boxes display the bound column,
ContactID displays a contact name (text), FacilityID displays a facility
full name (text) and RateTypeID displays a rate type (text).
Thanks,
Bill

You need the equals sign, and also you need the whole criteria argument
to be enclosed in quotes. Try this:

=Dlookup("RateAmt";"tblFacilityRates";"(FacilityID=[forms]![frmEnterMonthlyVisits].[cbofacilityID])
AND (RateTypeID=[forms]![frmEnterMonthlyVisits].[cboRateTypeID]) AND
(RateTypeDescription=[forms]![frmEnterMonthlyVisits].txtposition])")

I've simplified the expression somewhat; see if that works.

So long as the bound columns of the combo boxes are correct, it doesn't
matter what they're displaying. The only thing odd I see in your DLookup
expression is your use of the semicolon as an argument separater instead
of the comma that I would use; however, that's a regional setting, and it
may be correct for you. You could try using a comma instead and see if it
works.

Aside from that, I don't see the problem, so long as the form and control
names are not misspelled. You've double-checked that? Try executing the
DLookup from the Immediate window and see if you can debug it better
there.

 

[Dirk Goldgar]

Dirk,
I think you had it the first time around. I changed the ; to , and looked
at the spelling of everything in the syntax. I found a missing [ on the
code you returned to me. On the very last line of code on the word
txtposition. It got lost in the shuffle. Anyway, I appreciate your
tutoring and it is working now.

I'm sorry my error led to more trouble for you, and I'm glad you spotted the
mistake and got it working.