Custom Query field

[yeleek]

Hi,



In my DB i have a joint Date/time field. I've created a field in the query which is just performing format shortdate and asking for two parameters [startdate] and [enddate]. This is to extract just the date from the original field, and to set the criteria.

When however I'm running the query or trying to create reports using this field, and if the results cross the end of one month/the beginning of another I see very strange results such as

01/06/2012
02/06/2012
03/06/2012
28/05/2012
29/05/2012
30/05/2012
31/05/2012

I think this is because the field I've created is not being seen as a date/time format and is therefore being handled as text. How can i set the format in the query so this field i've created is treated as a date and the results go 28/05 -> 03/06?

Thanks

[pbaldy]

You can either put the criteria on a calculated field that uses DateValue() instead of Format(), or you can use the date/time field directly and add a day to the selected end date.

[yeleek]

You can either put the criteria on a calculated field that uses DateValue() instead of Format(), or you can use the date/time field directly and add a day to the selected end date.

Thank you so much. DaveValue() is working fine, easy when you know how heh?

[pbaldy]

Happy to help!