Date Calculation Problem

[jereece]

I have a form that has a number of date fields. The data is imported from an SQL database locally. The data imported includes the date and time. However I am only interested in the date, not the time. I also have fields on my form that display the calculate the differnce between the dates, so I have to keep the format of the field as date/time. My problem is, my query that calculates the difference may not be accurate because it takes into account the time piece. My question is how is the best way to elminiate this. Is there some property I can change on the format of the date field? Attached is an image of my properties. Or is there somethign I can change in my query shown below to only take into account the Date and not the time?

DAYS_OPEN: IIf([Status]="In Progress",(Date()-[START_DATE])-[DAYS_STOPPED],([APPROVAL_DATE]-[START_DATE])-[DAYS_STOPPED])

Thanks,
Jim

 

[namliam]

Depending on how you want to "trim" the time off....
From the current Now... 11/18/2008 2:19:10 PM
Do you want that to be 11/18/2008 or 11/19/2008

You can simply change your date/time to date only by doing...
Int(YourDateTimeField)
Which will return ONLY the date, i.e. 11/18/2008

To make that into 11/18/2008
Int(YourDateTimeField)+1

Good luck !

 

[jereece]

Is this put into the format of the data field or the query?

 

[namliam]

Into your formula...
i.e.
(Date()-int([START_DATE]))

 

[jereece]

Formula? Do you mean the Criteria field in the Query? Remember I am importing the Date / Time from data in an SQL database. I am not using a formula to create the date.

Thanks for the clarification.
Jim

 

[namliam]

Yes, in the query, but not in the criteria field... in the query field, like an expression.

Exactly where you have your current IIF...
Except everywhere you have a date/time field you want to lose the Time... use Int(datetimefield) to lose the time.

 

[jereece]

Sorry it took a while to get back (holidays and illness).

When I try what you suggest it say it's an invalid field. See attachment for where I am currently.

Thanks as always,
Jim

 

[namliam]

I dont see what I suggested...

What are you field types?
What columns are you updating with what?