Date Format Issue

[M_S_Jones]

Hello all,

I have encountered a problem with using date values in a calculated field in a query. The root of this problem can be found on a form in the system front end, which offers the users a default date using the now() function (which also adds a time on to the end of the date). The majority of records that have been saved thus have a time on the end as well. A query that I am running now needs to calculate the difference in days between two dates, the aforementioned date with a time on the end, and a date in short date format from another system's back end. This generated an error at first, but even when I've managed to get it working, doesn't return integers (which is desirable).

I've tried all kinds of changes to format the data in the query, for example adding a new field to the query such as:

(where the now() date field is named "Insp_Date")

test: Format([Insp_Date],"dd/mm/yyyy")
test2: Format([test],"Short Date")

In the above scenario, the field test2 does return the date in short date format sans the time, but when this is passed into the calculated field (and subtracted from another time), "#Error" is returned. Is there any way to achieve my desired output in the query? If not, is it possible to format all of the values in the table to be short dates? I can change the form so that it offers default dates as date() rather than now(), but all of the existing data in the table will still be in the wrong format.

Thanks for any advice you can offer,

Matt

 

[DCrake]

Simple Software Solutions

Instead of

test: Format([Insp_Date],"dd/mm/yyyy")
test2: Format([test],"Short Date")

Use

Elapsed:DateDiff("d",Format([Insp_Date],"dd/mm/yyyy"),Format([OtherDate],"dd/mm/yyyy"))

CodeMaster:

 

[namliam]

Cannot you just use the function datediff ??

DateDiff("D", date1, date2)

That should allways return an integer and be what you are looking for (I hope)

@DCrake,
Your format should work, but is it not dangerous to make implicit conversions? Format returns a string while the Datediff function requires dates as inputs. Datediff should allways return integer for days anyway, if not you can use something like Int(date2) to trunc it.

 

[M_S_Jones]

Thank you very much DCrake, that works perfectly. Thank you too namliam, is the general consensus that I should datediff without the conversions?

 

[namliam]

I wouldnt add anything that seems extra (meaning the format stuff)