Importing a date/time field

susanmorr

New member
Local time
Tomorrow, 05:28
Joined
Aug 12, 2002
Messages
5
I've had aread through the posts re date/time issues- there are so many!
My problem seems just a little different.
I import data from an information system into Access.
The field that's giving me grief is in date/time format (although I only want the date).
I have set the property of this field in Access to Short Date, and also tried the input mask __/__/__ , but still the time is entered into the table.
I have seen the suggestion to check the regional settings in control panel (will do this when back at work tomorrow)
Is there any other way to prevent the time from being entered into the table?

Thanks in advance for your help.
 
The reason you are getting the time imported along with the date is that Access (apparently correctly) recognizes a date/time variable, so it imports it using those rules. Setting formats to things like "Short Date" won't help because that applies only to display, not to binary data operations. I.e. the Format function isn't for Access convenience - it is for YOUR convenience.

You could just let the import happen and then perhaps go back to change the stored date. It would be a lot easier to treat this procedure according to Julius Caesar's primary rule: Divide and conquer. Break up the problem into two parts - import and adjust.

Before I go too far, let me point out that MOST of the time (admittedly not ALL of the time), it does not matter that you have time stored with a date. The FORMAT function can display the date without a time if that is what you wanted. The DateAdd, DatePart, and other date functions would not care. The only time you would run into trouble is with DateDiff in units of days, if and only if the hidden times add another 12 hours to the difference. Because in that case, you might experience some rounding problems. Any other time, time is benign.

But if you really, really, REALLY wanted to rid yourself of the time, you might take the imported data and run an update query on that column.

The function you might wish to use would look something like this:

CDat( Format( [DateField],"{put your favorite date-but-no-time format specification here}" ) )

In other words, update the field by doing the moral equivalent of truncating its implied fraction.
 
I tried your suggestion, but recieved this message "Undefined function 'Cdat' in expression"

The reason I don't want the time in the field is that I have made a query which sorts the records by date, and does a count of records that meet certain criteria for that date. (It's medical, the end result is the number of patients who are classified as "ICU" or "HDU" each day)

It works if only date is entered, but if time is present obviously then sorts by date and time.

If you know of a better way to achieve this I'd be very grateful.
Thank you for your time.
 
Whoops - my bad. CDate not CDat. My fat little fingers got in the way. You could try that.

Or, you could keep the times and just modify the query. In your query that does the "sort ascending" by date and time, don't sort by the raw date field. Sort by the Format([DateField],"Short Date") in the query grid. That forces the sort to use the text rather than the raw date.

Or, follow the help links on formatting a custom date string that puts dates in yyyy/mm/dd order. (Hint - then go back and read this paragraph a second time.) ;)
 

Users who are viewing this thread

Back
Top Bottom