dlookup with multiple criteria

[Guest]

On a form Tankentryform I am trying to grab the rate field back on the table
tbltanksrate2 where the following fields on the form are equal to the given
values back on the table. The rate field is just an input not a calculated
field but it should automitically be put in on the form based on the
clientid, pickupyd,etc. see below.

getting a syntax error not sure where???????


=dlookup(â€[rate]â€, “tbltanksrate2â€,â€[clientid] = ‘â€&
forms![tankentryform]![clientid] & “’†and “[pickupyd]= “ &
forms![tankentryform]![pickupyd] and â€[pickupydname] = ‘â€&
forms![tankentryform]![pickupydname] & “’†and “[delivyd]= “ &
forms![tankentryform]![delivyd] and â€[pickupydname] = ‘â€&
forms![tankentryform]![pickupydname] & “’†and “[type] = ‘â€&
forms![tankentryform]![tankmat] & “’â€)

thanks,
Barb

 

[Allan Murphy]

Try

=dlookup("[rate]", "tbltanksrate2","[clientid] = '"&
forms![tankentryform]![clientid] & "'" & _
" and "[pickupyd]= " & forms![tankentryform]![pickupyd] & _
" and "[pickupydname] = '"&
forms![tankentryform]![pickupydname] & "'" & _
" and "[delivyd]= " & forms![tankentryform]![delivyd] & _
" and "[pickupydname] = '"&
forms![tankentryform]![pickupydname] & "'" & _
" and "[type] = '"& forms![tankentryform]![tankmat] & "'")

You may have to change the " or ' depending upon the data types.

In coding like this I would start with one field then gradually build the
dlookup to the final criteria and correct any syntax errors along the way.

 

[Duane Hookom]

I have basically removed some quotes and made a huge assumption regarding
your field data types.

=dlookup("[rate]", "tbltanksrate2","[clientid] = '" &
forms![tankentryform]![clientid] & "' and [pickupyd]= " &
forms![tankentryform]![pickupyd] & " and [pickupydname] = '"&
forms![tankentryform]![pickupydname] & "' and [delivyd]= " &
forms![tankentryform]![delivyd] & " and [pickupydname] = '" &
forms![tankentryform]![pickupydname] & "' and [type] = '"&
forms![tankentryform]![tankmat] & "'")

 

[Guest]

Thanks for your help but still getting a syntax error.
clientid-autonumb,pickupyd&delivyd are number fields
pickupydname,delivydname, and type-tankmat are text fields.

not sure where the error is????

=DLookUp("[rate]","tbltanksrate2","[clientid] = '" &
[Forms]![tankentryform]![clientid] & "' and [pickupyd]= " &
[Forms]![tankentryform]![pickupyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [delivyd]= " &
[Forms]![tankentryform]![delivyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [type] = '" &
[Forms]![tankentryform]![tankmat] & "'")


thanks,
Barb

I have basically removed some quotes and made a huge assumption regarding
your field data types.

=dlookup("[rate]", "tbltanksrate2","[clientid] = '" &
forms![tankentryform]![clientid] & "' and [pickupyd]= " &
forms![tankentryform]![pickupyd] & " and [pickupydname] = '"&
forms![tankentryform]![pickupydname] & "' and [delivyd]= " &
forms![tankentryform]![delivyd] & " and [pickupydname] = '" &
forms![tankentryform]![pickupydname] & "' and [type] = '"&
forms![tankentryform]![tankmat] & "'")


--
Duane Hookom
MS Access MVP

On a form Tankentryform I am trying to grab the rate field back on the
table
tbltanksrate2 where the following fields on the form are equal to the
given
values back on the table. The rate field is just an input not a
calculated
field but it should automitically be put in on the form based on the
clientid, pickupyd,etc. see below.

getting a syntax error not sure where???????


=dlookup("[rate]", "tbltanksrate2","[clientid] = '"&
forms![tankentryform]![clientid] & "'" and "[pickupyd]= " &
forms![tankentryform]![pickupyd] and "[pickupydname] = '"&
forms![tankentryform]![pickupydname] & "'" and "[delivyd]= " &
forms![tankentryform]![delivyd] and "[pickupydname] = '"&
forms![tankentryform]![pickupydname] & "'" and "[type] = '"&
forms![tankentryform]![tankmat] & "'")

thanks,
Barb

 

[Duane Hookom]

You must use the proper delimiters for dates and strings and remove them for
numeric. I am concerned also about your "type-tankmat" fields that is "type"
in the DLookup().

Try:

=DLookUp("[rate]","tbltanksrate2","[clientid] = " &
[Forms]![tankentryform]![clientid] & " and [pickupyd]= " &
[Forms]![tankentryform]![pickupyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [delivyd]= " &
[Forms]![tankentryform]![delivyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [type] = '" &
[Forms]![tankentryform]![tankmat] & "'")

--
Duane Hookom
MS Access MVP

Thanks for your help but still getting a syntax error.
clientid-autonumb,pickupyd&delivyd are number fields
pickupydname,delivydname, and type-tankmat are text fields.

not sure where the error is????

=DLookUp("[rate]","tbltanksrate2","[clientid] = '" &
[Forms]![tankentryform]![clientid] & "' and [pickupyd]= " &
[Forms]![tankentryform]![pickupyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [delivyd]= " &
[Forms]![tankentryform]![delivyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [type] = '" &
[Forms]![tankentryform]![tankmat] & "'")


thanks,
Barb

I have basically removed some quotes and made a huge assumption regarding
your field data types.

=dlookup("[rate]", "tbltanksrate2","[clientid] = '" &
forms![tankentryform]![clientid] & "' and [pickupyd]= " &
forms![tankentryform]![pickupyd] & " and [pickupydname] = '"&
forms![tankentryform]![pickupydname] & "' and [delivyd]= " &
forms![tankentryform]![delivyd] & " and [pickupydname] = '" &
forms![tankentryform]![pickupydname] & "' and [type] = '"&
forms![tankentryform]![tankmat] & "'")


--
Duane Hookom
MS Access MVP

On a form Tankentryform I am trying to grab the rate field back on the
table
tbltanksrate2 where the following fields on the form are equal to the
given
values back on the table. The rate field is just an input not a
calculated
field but it should automitically be put in on the form based on the
clientid, pickupyd,etc. see below.

getting a syntax error not sure where???????


=dlookup("[rate]", "tbltanksrate2","[clientid] = '"&
forms![tankentryform]![clientid] & "'" and "[pickupyd]= " &
forms![tankentryform]![pickupyd] and "[pickupydname] = '"&
forms![tankentryform]![pickupydname] & "'" and "[delivyd]= " &
forms![tankentryform]![delivyd] and "[pickupydname] = '"&
forms![tankentryform]![pickupydname] & "'" and "[type] = '"&
forms![tankentryform]![tankmat] & "'")

thanks,
Barb

 

[Guest]

Actually have a design problem going on. Hope you can help.


I have 3 tables

CLIENTTABLE
clientid-PK
clientnumb
clientname
address
state
etc

TBLTANKRATES2
rateid
clientid-pk
pickupyd-pk
pickupydname
delivyd-pk
delivydname
typeofmat-pk
rate

TANKTICKETENTRY
clientid-pk
ticket-pk
servicedate
typeofmat
pickupdyd
delivyd
truck#
gross weight
comment

I need to grab the rate value from the tanksrate2 table where the
clientid,pickupyd, delivyd, and typeofmat are the same.

Joins for query clienttable (clientid-clientid)tankticketentry
tankrate2 (clientid-clientid)tankticketntry
tankrate2 (pickupyd-pickupyd)tankticketntry
tankrate2 (delivyd-delivyd)tankticketntry
tankrate2 (typeofmat-typeofmat)tankticketntry

The query worked fine except there may be a situation where there may be a
pickup yd with the same # but different pickupydname and same for deliv. so
I made the pickupydname and delivydname primary keys also in the tankrate2
table and now the query is not updatable. not sure how to lay this out. The
rate is not a calculated field just based on the clientid,
pickupyd,pickupydname,delivyd,delivydname, and typeofmat. want on the form
based on this query for the rate to automatically fill in based on all of
these items.

Thanks for your help,
Barb

You must use the proper delimiters for dates and strings and remove them for
numeric. I am concerned also about your "type-tankmat" fields that is "type"
in the DLookup().

Try:

=DLookUp("[rate]","tbltanksrate2","[clientid] = " &
[Forms]![tankentryform]![clientid] & " and [pickupyd]= " &
[Forms]![tankentryform]![pickupyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [delivyd]= " &
[Forms]![tankentryform]![delivyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [type] = '" &
[Forms]![tankentryform]![tankmat] & "'")

--
Duane Hookom
MS Access MVP

Thanks for your help but still getting a syntax error.
clientid-autonumb,pickupyd&delivyd are number fields
pickupydname,delivydname, and type-tankmat are text fields.

not sure where the error is????

=DLookUp("[rate]","tbltanksrate2","[clientid] = '" &
[Forms]![tankentryform]![clientid] & "' and [pickupyd]= " &
[Forms]![tankentryform]![pickupyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [delivyd]= " &
[Forms]![tankentryform]![delivyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [type] = '" &
[Forms]![tankentryform]![tankmat] & "'")


thanks,
Barb

I have basically removed some quotes and made a huge assumption regarding
your field data types.

=dlookup("[rate]", "tbltanksrate2","[clientid] = '" &
forms![tankentryform]![clientid] & "' and [pickupyd]= " &
forms![tankentryform]![pickupyd] & " and [pickupydname] = '"&
forms![tankentryform]![pickupydname] & "' and [delivyd]= " &
forms![tankentryform]![delivyd] & " and [pickupydname] = '" &
forms![tankentryform]![pickupydname] & "' and [type] = '"&
forms![tankentryform]![tankmat] & "'")


--
Duane Hookom
MS Access MVP


On a form Tankentryform I am trying to grab the rate field back on the
table
tbltanksrate2 where the following fields on the form are equal to the
given
values back on the table. The rate field is just an input not a
calculated
field but it should automitically be put in on the form based on the
clientid, pickupyd,etc. see below.

getting a syntax error not sure where???????


=dlookup("[rate]", "tbltanksrate2","[clientid] = '"&
forms![tankentryform]![clientid] & "'" and "[pickupyd]= " &
forms![tankentryform]![pickupyd] and "[pickupydname] = '"&
forms![tankentryform]![pickupydname] & "'" and "[delivyd]= " &
forms![tankentryform]![delivyd] and "[pickupydname] = '"&
forms![tankentryform]![pickupydname] & "'" and "[type] = '"&
forms![tankentryform]![tankmat] & "'")

thanks,
Barb

 

[Guest]

I figured out the design of the tables. Dlookup showing up as Name? on form.
not sure if I should be using textbox name form the form in the dlookup or
the control source.

Thanks,
Barb

You must use the proper delimiters for dates and strings and remove them for
numeric. I am concerned also about your "type-tankmat" fields that is "type"
in the DLookup().

Try:

=DLookUp("[rate]","tbltanksrate2","[clientid] = " &
[Forms]![tankentryform]![clientid] & " and [pickupyd]= " &
[Forms]![tankentryform]![pickupyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [delivyd]= " &
[Forms]![tankentryform]![delivyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [type] = '" &
[Forms]![tankentryform]![tankmat] & "'")

--
Duane Hookom
MS Access MVP

Thanks for your help but still getting a syntax error.
clientid-autonumb,pickupyd&delivyd are number fields
pickupydname,delivydname, and type-tankmat are text fields.

not sure where the error is????

=DLookUp("[rate]","tbltanksrate2","[clientid] = '" &
[Forms]![tankentryform]![clientid] & "' and [pickupyd]= " &
[Forms]![tankentryform]![pickupyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [delivyd]= " &
[Forms]![tankentryform]![delivyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [type] = '" &
[Forms]![tankentryform]![tankmat] & "'")


thanks,
Barb

I have basically removed some quotes and made a huge assumption regarding
your field data types.

=dlookup("[rate]", "tbltanksrate2","[clientid] = '" &
forms![tankentryform]![clientid] & "' and [pickupyd]= " &
forms![tankentryform]![pickupyd] & " and [pickupydname] = '"&
forms![tankentryform]![pickupydname] & "' and [delivyd]= " &
forms![tankentryform]![delivyd] & " and [pickupydname] = '" &
forms![tankentryform]![pickupydname] & "' and [type] = '"&
forms![tankentryform]![tankmat] & "'")


--
Duane Hookom
MS Access MVP


On a form Tankentryform I am trying to grab the rate field back on the
table
tbltanksrate2 where the following fields on the form are equal to the
given
values back on the table. The rate field is just an input not a
calculated
field but it should automitically be put in on the form based on the
clientid, pickupyd,etc. see below.

getting a syntax error not sure where???????


=dlookup("[rate]", "tbltanksrate2","[clientid] = '"&
forms![tankentryform]![clientid] & "'" and "[pickupyd]= " &
forms![tankentryform]![pickupyd] and "[pickupydname] = '"&
forms![tankentryform]![pickupydname] & "'" and "[delivyd]= " &
forms![tankentryform]![delivyd] and "[pickupydname] = '"&
forms![tankentryform]![pickupydname] & "'" and "[type] = '"&
forms![tankentryform]![tankmat] & "'")

thanks,
Barb

 

[Duane Hookom]

Usually the display of "Name?" suggests the name of the text box is the name
of a field. If so, change the name of the text box to something like
txtRate.

--
Duane Hookom
MS Access MVP

I figured out the design of the tables. Dlookup showing up as Name? on
form.
not sure if I should be using textbox name form the form in the dlookup or
the control source.

Thanks,
Barb

You must use the proper delimiters for dates and strings and remove them
for
numeric. I am concerned also about your "type-tankmat" fields that is
"type"
in the DLookup().

Try:

=DLookUp("[rate]","tbltanksrate2","[clientid] = " &
[Forms]![tankentryform]![clientid] & " and [pickupyd]= " &
[Forms]![tankentryform]![pickupyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [delivyd]= " &
[Forms]![tankentryform]![delivyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [type] = '" &
[Forms]![tankentryform]![tankmat] & "'")

--
Duane Hookom
MS Access MVP

Thanks for your help but still getting a syntax error.
clientid-autonumb,pickupyd&delivyd are number fields
pickupydname,delivydname, and type-tankmat are text fields.

not sure where the error is????

=DLookUp("[rate]","tbltanksrate2","[clientid] = '" &
[Forms]![tankentryform]![clientid] & "' and [pickupyd]= " &
[Forms]![tankentryform]![pickupyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [delivyd]= " &
[Forms]![tankentryform]![delivyd] & " and [pickupydname] = '" &
[Forms]![tankentryform]![pickupydname] & "' and [type] = '" &
[Forms]![tankentryform]![tankmat] & "'")


thanks,
Barb
:

I have basically removed some quotes and made a huge assumption
regarding
your field data types.

=dlookup("[rate]", "tbltanksrate2","[clientid] = '" &
forms![tankentryform]![clientid] & "' and [pickupyd]= " &
forms![tankentryform]![pickupyd] & " and [pickupydname] = '"&
forms![tankentryform]![pickupydname] & "' and [delivyd]= " &
forms![tankentryform]![delivyd] & " and [pickupydname] = '" &
forms![tankentryform]![pickupydname] & "' and [type] = '"&
forms![tankentryform]![tankmat] & "'")


--
Duane Hookom
MS Access MVP


On a form Tankentryform I am trying to grab the rate field back on
the
table
tbltanksrate2 where the following fields on the form are equal to
the
given
values back on the table. The rate field is just an input not a
calculated
field but it should automitically be put in on the form based on the
clientid, pickupyd,etc. see below.

getting a syntax error not sure where???????


=dlookup("[rate]", "tbltanksrate2","[clientid] = '"&
forms![tankentryform]![clientid] & "'" and "[pickupyd]= " &
forms![tankentryform]![pickupyd] and "[pickupydname] = '"&
forms![tankentryform]![pickupydname] & "'" and "[delivyd]= " &
forms![tankentryform]![delivyd] and "[pickupydname] = '"&
forms![tankentryform]![pickupydname] & "'" and "[type] = '"&
forms![tankentryform]![tankmat] & "'")

thanks,
Barb