Query with two date field for Date range


R

Rag742

How do I create a Query using two tables that have dates field to
calculate a value using both the tables for a Date Range. For Example

I have four TABLES- Time Card(TC), PROJECT, TC hours(HOURS), and TC
expenses(EXPENSE). TC contains name of employees and their billing
rates. PROJECT contains Name of Project. HOURS field are TCId,
PROJECTId, Dateworked, and Hours. EXPENSE contains fields TCId,
PROJECTId, Dateexpense, Expense.
I want to build a query that over a Date Range that sums billable
amount=hours worked* billing rate+ expense.
I set the Dateworked >=[forms]![Report Date Range]![BeginDate] And
<=[forms]![Report Date Range]![EndDate]

What do I do with the Dateexpense to set the Date Range.
 
Ad

Advertisements

G

Guest

Hi.

If I uderstand you want to make a criteria at the same time for two
different tables, is that it?

If so, create a inner join query.

I hope it helps,
Marco
 
R

Rag742

Hi.

If I uderstand you want to make a criteria at the same time for two
different tables, is that it?

If so, create a inner join query.

I hope it helps,
Marco

Rag742 said:
How do I create a Query using two tables that have dates field to
calculate a value using both the tables for a Date Range. For Example
I have four TABLES- Time Card(TC), PROJECT, TC hours(HOURS), and TC
expenses(EXPENSE). TC contains name of employees and their billing
rates. PROJECT contains Name of Project. HOURS field are TCId,
PROJECTId, Dateworked, and Hours. EXPENSE contains fields TCId,
PROJECTId, Dateexpense, Expense.
I want to build a query that over a Date Range that sums billable
amount=hours worked* billing rate+ expense.
I set the Dateworked >=[forms]![Report Date Range]![BeginDate] And
<=[forms]![Report Date Range]![EndDate]
What do I do with the Dateexpense to set the Date Range.

Thanks But this creates another problem. If there is no value for a
date in expense or hour field for the dates in Range the formula of
total bill is blank. How do i get around this
 
Ad

Advertisements

G

Guest

Search between dates, is that it?

bETWEEN [DATA1] AND [DATE2]

Rag742 said:
Hi.

If I uderstand you want to make a criteria at the same time for two
different tables, is that it?

If so, create a inner join query.

I hope it helps,
Marco

Rag742 said:
How do I create a Query using two tables that have dates field to
calculate a value using both the tables for a Date Range. For Example
I have four TABLES- Time Card(TC), PROJECT, TC hours(HOURS), and TC
expenses(EXPENSE). TC contains name of employees and their billing
rates. PROJECT contains Name of Project. HOURS field are TCId,
PROJECTId, Dateworked, and Hours. EXPENSE contains fields TCId,
PROJECTId, Dateexpense, Expense.
I want to build a query that over a Date Range that sums billable
amount=hours worked* billing rate+ expense.
I set the Dateworked >=[forms]![Report Date Range]![BeginDate] And
<=[forms]![Report Date Range]![EndDate]
What do I do with the Dateexpense to set the Date Range.

Thanks But this creates another problem. If there is no value for a
date in expense or hour field for the dates in Range the formula of
total bill is blank. How do i get around this
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top