Query in form with two criteria

[Joe_Hunt via AccessMonster.com]

I started learning about Access two weeks ago, so to say I'm a newbie is an
understatement. I'm creating a form that is going to (hopefully) take the
place of what we're now doing in Excel. I've loaded several tables of data
and when I run a query on that data I can pull up all the data I need. What I
need now is to be able to get just one, the right one hopefully, to show up
in a text box. I have 130+ vehicles in the tables, and since the data for
these vehicles comes from different sources they are under different names so
I've created a reference table that kind of pulls it all together. What I'm
looking for is for a query in one text box to look for two fields on a form
in a query I've already set up and pull back the correct value. The query I
have (that doesn't work) is =DLookUp(Form![NAME],Form![Text40],ALG_08_Query!
MSRP,Form![NAME]). I do know that the number of arguments is incorrect.

The data: There is a textbox with the name of "NAME" and another textbox with
the name of "Text40." The query I made that pulled together the info I need
is "ALG_08_Query." What I need it to do is look for the name and date in the
"NAME" textbox and "Text40" textbox and bring back the value in the MSRP
column.

I hope this makes sense. I've been able to get almost everything else on the
form to work through formulas and a little coding, and if someone can lessen
my big learning curve here I can modify it for everything else and hopefully
learn the glorious world of Microsoft Access. This is the 2003 version if
that matters, and I do appreciate it.

 

[Tom van Stiphout]

On Wed, 21 Jan 2009 23:09:01 GMT, "Joe_Hunt via AccessMonster.com"

Dlookup takes 3 parameters. The third one is a where clause. Your
where clause should probably be:
"myFirstField=" & Me.myFirstControl & " and mySecondField=" &
Me.mySecondControl
(of course you replace myObjectNames with yours)

In this example I am assuming that both fields are not text. If they
are, you need to wrap single-quotes around the values.

Don't use "NAME" as the name of a control. It is a reserved word
requiring you to put square brackets around the name all the time.
Also don't use Text40 as the name of a control, but use a more
descriptive name.

-Tom.
Microsoft Access MVP