DLOOKUP - Multiple criteria

[GD]

Happy Holidays!!

I want to auto fill a text box using two other text boxes as criteria, but
my DLOOKUP results in unrelated data.

The data I need is VendorName
The criteria are Invoice# in tblMASTER (to coincide with Text40 on the form)
and Vendor# in tblMASTER (to coincide with Text52 on the form)

=DLookUp("[tblMASTER]![VendorName]","tblMASTER","[tblMASTER]![Invoice#]='" &
Forms!frmGregTest!Text40 & "'" And "[tblMASTER]![Vendor#]='" &
Forms!frmGregTest!Text52 & "'")

Where's my goof??

 

[Ken Snell \(MVP\)]

The And must be part of the string that you build in the third argument:

=DLookUp("[tblMASTER]![VendorName]","tblMASTER","[tblMASTER]![Invoice#]='" &
Forms!frmGregTest!Text40 & "' And [tblMASTER]![Vendor#]='" &
Forms!frmGregTest!Text52 & "'")

"GD" <[email protected]
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

om> wrote in message
news:[email protected]...

 

[Dale Fye]

1. Are the Vendor# and Invoice# fields actually numeric, or text? The way
you have your DLOOKUP setup, they appear to be text.

2. Is the textbox you are trying to fill in on the same form as the other
two controls?

If the answer to #1 is Numeric, and the answer to #2 is "same form", then try:

=DLookup("VendorName", "tblMaster", "[Invoice#] = " & me.text40 _
& " AND [Vendor#] = " & me.text52)

BTW,

I would change the name of your textboxes to have some meaning (how about
txt_InvoiceNum and txt_VendorNum). If you ever come back to perform any
maintenance on this application, you will be hitting yourself over the head
asking what text40 and text52 are. Google on "+vba +naming +conventions" to
get some good ideas on standard naming conventions.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[GD]

Awesome! Thanks, Ken!

--
GD

The And must be part of the string that you build in the third argument:

=DLookUp("[tblMASTER]![VendorName]","tblMASTER","[tblMASTER]![Invoice#]='" &
Forms!frmGregTest!Text40 & "' And [tblMASTER]![Vendor#]='" &
Forms!frmGregTest!Text52 & "'")

"GD" <[email protected]
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

om> wrote in message

Happy Holidays!!

I want to auto fill a text box using two other text boxes as criteria, but
my DLOOKUP results in unrelated data.

The data I need is VendorName
The criteria are Invoice# in tblMASTER (to coincide with Text40 on the
form)
and Vendor# in tblMASTER (to coincide with Text52 on the form)

=DLookUp("[tblMASTER]![VendorName]","tblMASTER","[tblMASTER]![Invoice#]='"
&
Forms!frmGregTest!Text40 & "'" And "[tblMASTER]![Vendor#]='" &
Forms!frmGregTest!Text52 & "'")

Where's my goof??

 

[Whitney]

I'm looking to do something similar, however my id fields are numeric and I'm
using three forms.

I need help using three forms to do the following:

First form will be used by an agent to enter their own id and today's date,
click ok to go to the next form (agent id and name retrieved from table
tbl_SSR). This will only be done once while they have the dbase open, the
form should close or not be visable after this step.

Second form will be used to enter the caller's agent id, click ok (caller's
agent id and name are retreived from table tbl_Agent). This will need to
reappear after the third form.

Third form will be used to enter the details of the call. The first and
second form data should autopopulate at the top of this form.
Ex:
SSR Name
Agent ID - Agent Name
Date

The autopopulated data and the additional data should be stored on
tbl_tracker.

After the third form is completed, I need the second form to reappear blank
for
the next call.

1. Are the Vendor# and Invoice# fields actually numeric, or text? The way
you have your DLOOKUP setup, they appear to be text.

2. Is the textbox you are trying to fill in on the same form as the other
two controls?

If the answer to #1 is Numeric, and the answer to #2 is "same form", then try:

=DLookup("VendorName", "tblMaster", "[Invoice#] = " & me.text40 _
& " AND [Vendor#] = " & me.text52)

BTW,

I would change the name of your textboxes to have some meaning (how about
txt_InvoiceNum and txt_VendorNum). If you ever come back to perform any
maintenance on this application, you will be hitting yourself over the head
asking what text40 and text52 are. Google on "+vba +naming +conventions" to
get some good ideas on standard naming conventions.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Happy Holidays!!

I want to auto fill a text box using two other text boxes as criteria, but
my DLOOKUP results in unrelated data.

The data I need is VendorName
The criteria are Invoice# in tblMASTER (to coincide with Text40 on the form)
and Vendor# in tblMASTER (to coincide with Text52 on the form)

=DLookUp("[tblMASTER]![VendorName]","tblMASTER","[tblMASTER]![Invoice#]='" &
Forms!frmGregTest!Text40 & "'" And "[tblMASTER]![Vendor#]='" &
Forms!frmGregTest!Text52 & "'")

Where's my goof??