look up 2 columns in a table using one criteria ina query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am wanting to create a query that looks for a certain item number. I have
2 columns in the look up table that can contain the same information, but not
always in the same entry. How can I get the query to look at both column A
& B and show the findings?
 
Hi,



something like:


=DLookup("fieldName", "tableName", "ColumnA=222 OR ColumnB=222" )


Note that DLookup returns only one row that matches the criteria.


Hoping it may help,
Vanderghast, Access MVP
 
Sort of?

We enter 2 lots of die information Eg: Column 1's name "Die1"and "Die2",
there is also which level these are on and which position Eg: Level 1 - East,
Level 10 - West.

I want to be able to create a report from the query that allows the user to
enter the Die they wish to search for and be able to see each entry that
contains that die number.

Will that work?
 
Hi,



Depends on how your tables are designed. As example, if "die1" and "die2"
are fields name, under which is a number (1 to 10), the criteria, the third
argument, can be:

FORMS!FormName!WantedFieldName & "=" & FORMS!FormName!WantedValue


where WantedFieldName and WantedValue are controls with the value "die1" or
"die2" for the first, and a number, for the second. As example, if you use
combo box and WantedFieldName has selected die2 in it, and same, WantedValue
as 5 in it, then the criteria, after substitution (by Access, you have
nothing extra to do, it is just for illustration), Access will get
"die2=5" as criteria, so, limiting the records where that is true. If it
is possible that you get many records, then maybe it is preferable to built
the whole SQL string, at run time, and use it as rowsource of a listbox, or
as record source of a form, as example.


But again, the exact detail depends on how your table is designed.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top