Dates from a Table in Query

L

Larry

I have a simple time tracking database that I am creating. I have a table
tblPayPeriod that just has two columns in it - StartDate and EndDate.

I want to create criteria in the query that will only pull data from between
the two dates, so I have tried to add this to the criteria:

Between [Tables]![tblPayPeriod].[StartDate] And
[Tables]![tblPayPeriod].[EndDate]

But it is not working, HELP!
 
D

Duane Hookom

tblPayPeriod would need to be in the "FROM " clause of your query. You
should then be able to get rid of "Table" and just use:

Between [tblPayPeriod].[StartDate] And [tblPayPeriod].[EndDate]

If you can't add the table, you could probably use 2 DLookup()s or 2
subqueries in the criteria.

Between Dlookup("[StartDate]","[tblPayPeriod]") And Dlookup("[EndDate]",
"[tblPayPeriod]")

This assumes there is only one record in tblPayPeriod.
 
J

Jeff Boyce

Larry

"... not working ..." could mean anything. Is the query returning some data
or none? Wrong data or not all of the right data...? Is the PC shutting
down or blowing up?<g>

Also, "how" might depend on which version of Access you are using...

What happens if you remove the paramater prompts and use actual, "hardcoded"
dates in your "Between..." expression?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
F

floronDBA

I have a simple time tracking database that I am creating. I have a table
tblPayPeriod that just has two columns in it - StartDate and EndDate.

I want to create criteria in the query that will only pull data from between
the two dates, so I have tried to add this to the criteria:

Between [Tables]![tblPayPeriod].[StartDate] And
[Tables]![tblPayPeriod].[EndDate]

But it is not working, HELP!

The criteria look Ok, but Access will not know by itself what to apply
them to.

SOMETHING Between [Tables]![tblPayPeriod].[StartDate] And
[Tables]![tblPayPeriod].[EndDate]

Hope this helps
 
J

John W. Vinson

I have a simple time tracking database that I am creating. I have a table
tblPayPeriod that just has two columns in it - StartDate and EndDate.

I want to create criteria in the query that will only pull data from between
the two dates, so I have tried to add this to the criteria:

Between [Tables]![tblPayPeriod].[StartDate] And
[Tables]![tblPayPeriod].[EndDate]

But it is not working, HELP!

If you want to find all records for a particular date, use
= [Enter date:]

as a criterion on StartDate, and

<= [Enter date:]

as a criterion on EndDate.
 

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