Report query for Two Date 2

[veboit]

Hi
I want get a report startdate and end date. I am using Access query. I tried using 'between' but the problem when I type same date for two parameter it does not showing any result.if I want one day report I want type startdate and the enddate will be next day.
How can query using two parameter for one date (from and to)
two parameters?. Also I need to display the report heading from and to any help greatly appricated.
thanks

 

[dhookom]

I expect your date field might also be storing a time. If this is the case then:
[blue]Between #3/15/2006# And #3/15/2006# [/blue]
would return only records from midnight.

You should always get your criteria from controls on forms. Making users enter more than just a click on a pop-up isn't good user interface.

If you want to include "all day" on the end date criteria, add .999 to the value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[veboit]

Thanks for you reply. Yes my date field also storing the time. I want to include "all day". can you please explain how to add .999 to the value?
thanks

 

[dhookom]

What is the SQL view of your query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[veboit]

Here is the query view:

SELECT MyTable1.Complete_Date
FROM MyTable1 WHERE ((MyTable1.Complete_Date) Between [From-Date] And [To-Date]);

 

[sdk]

Hi

on Duanes behalf:

WHERE ((MyTable1.Complete_Date) Between [From-Date] And [To-Date]+0.999);

Personally, I would add just one - it won't make any difference to your results, is more correct etc.

Also, Duane's advice is correct - you really should look at having a form front-end to control criteria for a report. In this instance the form would include the two fields, and the report can either filter for the criteria, or the query can refer to the forms values, eg:
WHERE ((MyTable1.Complete_Date) Between forms!FormName![TxtFrom] And forms!FormName![TxtTo] );

This might seem to complicate your requirements, but gives you control over how the user interacts with the system, can make criteria easier to use etc.

Cheers

S

 

[dhookom]

Or use

WHERE (DateValue(MyTable1.Complete_Date) Between forms!FormName![TxtFrom] And forms!FormName![TxtTo] );


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Pauly77]

You could use the calendar control to populate 2 text boxes, one with a start date and one with an end date.