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!

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.


--
Duane Hookom
Microsoft Access MVP


"Larry" wrote:

> 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!

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


"Larry" <(E-Mail Removed)> wrote in message
news:7552B796-6C0C-43F9-B8B4-(E-Mail Removed)...
>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!

On 10 nov, 16:52, Larry <La...@discussions.microsoft.com> wrote:
> 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

On Mon, 10 Nov 2008 07:52:05 -0800, Larry <(E-Mail Removed)>
wrote:

>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.

--

John W. Vinson [MVP]