Tables/Queries

[Scott Nash]

Hello,

I've seen some posts regarding a similar problem to what I'm having but cant
find the resolutution!

I have a database with tableA which contains basic inventory info. I have a
field in tableA named location. There is a second table (tableB) which has
a list of all the locations our company delivers to.

I've been warned to stay away from lookup fields, so I'm using combo boxes
on the form to look up the location and then populate the location field in
tableA when the value is selected.

Here is the issue (and again, I've found some posts similar): Running a
query seems to be an issue. If I enter NEW YORK, I get no results. And the
other strange thing is that the location field in table A does not contain
the state name but a number.

I need to be able to keep the locaiton field a drop down so my users can
select a state, but I must be able to report off of that info by entering
the name of a state.

How can I achieve this??

Thank you everyone.

 

[Duane Hookom]

You generally use a control on a form to filter a query. Use a combo box as
this control. You can display the name and bind to the number.

 

[John W. Vinson]

I need to be able to keep the locaiton field a drop down so my users can
select a state, but I must be able to report off of that info by entering
the name of a state.

The Report itself should be joined on a query joining your main table to the
location table, using the location name from the latter. As a search
criterion, you can use an unbound Form (let's call it frmCriteria) with a
Combo Box (cboLocation let's say) displaying the human-meaningful name but
using the ID as its bound column; your query criterion would be

=Forms![frmCriteria]![cboLocation]

and the form would have a command button to launch the report.

John W. Vinson [MVP]

 

[CANTANTE DOCK UNA VERA SCOPERTA!]

I need to be able to keep the locaiton field a drop down so my users can
select a state, but I must be able to report off of that info by entering
the name of a state.

The Report itself should be joined on a query joining your main table to the
location table, using the location name from the latter. As a search
criterion, you can use an unbound Form (let's call it frmCriteria) with a
Combo Box (cboLocation let's say) displaying the human-meaningful name but
using the ID as its bound column; your query criterion would be

=Forms![frmCriteria]![cboLocation]

and the form would have a command button to launch the report.

John W. Vinson [MVP]

www.carlogiove.altervista.org

 

[Scott Nash]

Thanks all!

I need to be able to keep the locaiton field a drop down so my users can
select a state, but I must be able to report off of that info by entering
the name of a state.

The Report itself should be joined on a query joining your main table to the
location table, using the location name from the latter. As a search
criterion, you can use an unbound Form (let's call it frmCriteria) with a
Combo Box (cboLocation let's say) displaying the human-meaningful name but
using the ID as its bound column; your query criterion would be

=Forms![frmCriteria]![cboLocation]

and the form would have a command button to launch the report.

John W. Vinson [MVP]

www.carlogiove.altervista.org