Date Filter

[arcootje]

I've got a question:

I've a table in this table there are only date value's

the notation can not be changed, but it is for example 15-09-2003 10:33

i want a filter on that, in a query.

When i open this query, he must ask me what date i want to see.
and when i enter that date, he shows me that date.
there can be more times on that date

 

[bat17]

In the design view of the query, goto Query>Parameters...
and add a parameter called "Pick Date" and select the Data Type as Date/Time
In the criteria for your date field add
>=[Pick Date] And <[Pick Date]+1

HTH

Peter

 

[Andrew Fergus]

Hi Peter

I was trialling exactly what you posted when you posted it and found it doesn't work (I was surprised it didn't work but there is a "too complex" error).

I recommend arcootje tries this if you can't get the previous suggestion to work:

>= [Enter Date] And < DateAdd ("d",1,[Enter Date])

NB : spaces have been added into the above formula to preserve it.

My only proviso is that you only enter a date and not a time (otherwise it won't work).

HTH, Andrew.

 

[bat17]

I trialed it before I posted it and it did work as long as the Query Parameter was set. Tested in A97

PARAMETERS [Pick Date] DateTime;
SELECT DATA_3MT.ID, DATA_3MT.[Date - Time]
FROM DATA_3MT
WHERE (((DATA_3MT.[Date - Time])>=[Pick Date] And (DATA_3MT.[Date - Time])<[Pick Date]+1));


peter

 

[Andrew Fergus]

My apologies Peter - your method does work.

When I was trying to specifiy the criteria at about the same time you did (and failing with the "too complex" error), I was trying to do it without going through Query -> Parameters menu and hadn't fully appreciated the impact of this within your post. The method I posted does not require any entries under the Query -> Parameters menu.

Cheers, Andrew.

 

[bat17]

The method I posted does not require any entries under the Query -> Parameters menu.

and therefore a simpler solution which probably makes it a better one!



Peter