Using a Table Lookup Field in a Parameter Query

[BrookieOU]

I have a tblBillableHours and tblProjectBilling. In the tblBillable Hours I
store all of the pay periods along with the number of billable days and hours
per pay period. In the tblProjectBilling I store the invoices we bill and
use a lookup field to lookup the pay period from tblBillableHours.

I am trying to do a query where it will list the name, invoice #, PO#, date
and invoice amount from tblProjectBilling. I would like to enter a parameter
so that it will prompt users to enter the pay period of the invoices they
would like to see. The problem I'm having is that because of the lookup it
wants me to enter the autonumber assigned to the pay period and not the date.
Is there a way I can set it so that I can enter the date and not the
autonumber?

And thanks to everyone who has helped me and everyone else on here! It's
great!

Brooke

 

[Ken Sheridan]

In the query join the two tables and, instead of the foreign key column from
tblProjectBilling include the real date/time data type column from
tblBillableHours. You can then include a parameter on this column. Be sure
to declare the parameter as a date parameter value entered in short date
format could otherwise be misinterpreted as an arithmetical expression and
give the wrong result.

Another approach would be to reference a combo box on an unbound dialogue
form as the parameter. the bound column of the combo box would be the
autonumber column and the visible column the date, so a user can select a
date but the parameter value would be the autonumber column's value. You'd
then not need to include the tblBillableHours table in the query. The combo
box would be set up along these lines:

RowSource: SELECT YourID, YourDate FROM tblBillableHours ORDER BY
YourDate;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England

 

[BrookieOU]

Thank you sooo much! The first one worked great and I'm going to look into
the second one. I have been reading about how lookups are BAD!