date range problem

[laurat]

I have a report to find the date range. This report is based off of a query. The criteria is set in the date field of the query and has:

Between[Enter a starting date:] And [Enter an ending date: ].

The problem is the date range displayed is not accurate. If you type in 8/10/02 for the start date and 8/12/02 for the end date. It only gives you the records with dates between 8/10 and 8/11. If you want to see records entered today(8/12/02) you have to type 8/13/02 as the ending date. This does not make sense to me and I am not quite sure how to fix the problem. I hope someone out there can and soon. Thank you so much.

 

[pdx_man]

How are you entering the dates that are stored in these fields? My guess is that you are using the Now() function. If you are linking to a SQL Server table, the datatype needs to be changed to a smalldatetime. What is happening is the dates are being stored with the time parameters, too. So when you are querying through 08/12, it is not including dates that are 08/12/02 11:43:02.

HTH

 

[laurat]

You are right, I am using the Now() function. How do I change it to that smalldatetime that you suggested??

The weird thing is that I went back and checked dates in the beginning(when the db was first used) and it works for like the first month of dates. For example, I typed 11/29/01 for the starting and ending date and it brings up all records with 11/29 as the date. However when I tried 11/30/01 it did not bring up any records and did not work for any random dates I tried after that.

Thanks for the help.

 

[pdx_man]

Instead of using Now(), use Date(). Do you have permissions to do design changes on your SQL Server? I'm guessing not, so I would feel bad if you changed someone else's structure inacurately. So, just use the Date() function.

 

[JCross]

question?

hi! I solved this same problem by insertin -1 and +1 into the query critera. It worked - though it seems a little simplistic........maybe it wasn't the right way to do it.


Jennifer

 

[Rich]

Have you defined the parameters as Date/Time

 

[antomack]

Within your query set the date field to the expression below

format([YourDateField],"mm/dd/yyyy")

and then put your criteria below this of

Between[Enter a starting date:] And [Enter an ending date: ]

 

[laurat]

Thank you all for your help.

The Date() solves the problem for records that are entered in the future but it does nothing for the ones that have already been entered. If nothing else at least it will work for the future...Thank you.

As for the: Format([Orig Date],"mm/dd/yyyy") I am a little lost as to where I put that in the query. I think I need a little more info because I do not understand. Thank you.

 

[pdx_man]

You will need to do an Update query to fix the dates in the past. Truncate the time info. You will want to do this to have consistant data stored in the field. (Others may be accessing the same data source and will also have issues) The Format([Orig Date],"mm/dd/yyyy") is a band-aid that will work.

Design view of query:
In an open column in the grid on the Field row, put TheDate: Format([Orig Date],"mm/dd/yyyy") . Then, put your criteria on this column.

 

[NOL]

Hi ,
I was trying to truncate the time part using
Format (MyDatetime, "mm/dd/yyyy") or Format(MydateTime, "Short Date")

But once i use format , the field loses it's date datatype and behaves as a string, so that if i sort asc 1/1/2003 appears before 12/31/2002.

I want to be able to do a truncate like ORACLE does.
Any pointers woule help greatly !

Thanks,
Gina.

 

[pdx_man]

Hmm,
Access should know this is a date ... You can always force it using the CDate function:

CDate(Format (MyDatetime, "mm/dd/yyyy")),

But I would look at your data type for MyDatetime. Is it defined in the table as having a Short Date format? Is this a linked Oracle table? What is the context of your field?

Also, try to post new questions in a new thread. That way, others can learn, too . Thanks.

 

[NOL]

Thanks so much ! That worked perfectly

The table is a linked ORACLE Table.
and the field Datatype as seen in Access Table Design View is "Date/Time".

I will make sure I post a new topic next time.
I was thinking that any1 doing a search would find all related info under one post.
But your suggestion makes sense .
Thank You !

Gina.