Query for Times Past Midnight 1

[mward04]

I imagine this has been solved in another thread, I just cannot find the answer to my question. If you know the thread, please let me know. I've been spending too much time on this and can't find a solution.

I have 2 times in an XL spreadsheet: Arrival and Discharge. When I look at the Arrival Times in XL, it says the date is 1/0/1900 and then the time.

Frequently, discharge is past Midnight and the time is entered as for example, 27:00 (to represent 3AM the next day) or 1/1/1900. So if I subtract Discharge-Arrival, I would get a positive number in minutes, for example, 104 minutes.

However, when I import it into Access it subtracts a day from the Arrival time and I get 12/30/1899 causing [Discharge]-[Arrival] to be an extra day longer than it should be.

Any suggestions on how to get around this or how to resolve this in Access?

Thank you in advance!
Mike

 

[Pampers]

27:00 is a funny way to say it is 03:00. I dont think 27:00 is even a valid time field.

Pampers
There is only one way to change a diaper - fast

 

[FoxProProgrammer]

Howdy Mike,

Check the format of the cells in Excel. Is the format something like [h]:hh:mm ? If so, this represents an elapsed time format and Access should preserve the date/time when it is imported from Excel. If you enter a time in Excel without a date, it assumes that the baseline date is 1/0/1900. For example, if you enter a time of 23:59, it actually stores the data as 1/0/1900 11:59:00 PM. Assuming that you haven't already formatted the cell, Excel will set it to h:mm. If you enter 27:00, Excel will store the data as 1/1/1900 3:00:00 AM and set the format to [h]:mm if the cell wasn't already formatted. The date/time will be displayed according to however the cell is formatted. This can also be affected by the Regional settings in Control Panel.

How did you import the data? Did you use the Get External Data/Import menu in Access, write a Query, or use DoCmd.TransferSpreadsheet? If you wrote a Query, please post it so we can see if this might be causing the problem. You might also try using the Get External Data menu or DoCmd.TransferSpreadsheet Action in Access (if you haven't already) to see if the data imports properly. If you use DoCmd.TransferSpreadsheet, the data will be stored as Text if the table that you are importing to doesn't already exist. Make sure that the table exists and that the field types are set to Date/Time.

As pampers said, 27:00 isn't a valid time when entered into a field in Access. Excel is much more forgiving when it comes to entering date/time than Access simply because of the nature of the two applications. For example, you can't store 27:00 in a date/time field in an Access table or date field on a form. You would first have to convert it to 1/1/1900 3:00:00 AM. If you use Get External Data or DoCmd.TransferSpreadsheet, Access should convert the data for you and import it correctly.

dz
dzaccess@yahoo.com

 

[mward04]

DZ-thank you for your response. I think part of the problem is the fact that Access doesn't recognize 1/0/1900 as a valid date and converts it to 12/30/1899 upon import.

As far as importing the data, I've been using the Get External Data.

I've been entering 27:00 into Excel, not Access which does interpret the date as the next day or 1/1/1900 3:00AM. Aside from entering the date and time, what would be an easier way to convey that the time occurs the next day?

I finally "solved" this problem by displaying the date in addition to the time in Excel and simply doing an "edit/replace" I just wish there was a simpler way to get this done.

 

[Golom]

Access will convert "dates" that don't seem to be valid using the same logic that is found in the DateSerial function. For example
[tt]
? dateserial(2005,15,45) --> 04/14/2006
[/tt]
so your 1/0/1900 is treated as
[tt]
? dateserial(1900,1,0) --> 12/31/1899
[/tt]
because day zero of a month is the last day of the preceding month.

Similar things happen with TimeSerial
[tt]
? TimeSerial(23,0,0) --> 12/30/1899 11:00:00 PM
? TimeSerial(27,0,0) --> 12/31/1899 3:00:00 AM
[/tt]

 

[mward04]

I believe that Access actually converted 1/0/1900 to 12/30/1899, not 12/31/1899 as I would have predicted.

 

[Golom]

... and also

DateTime fields are really DateTime stamps (i.e. they represent a point in time) and they are limited when you are attempting to represent elapsed time. You can still do it of course by using the DateDiff and DateAdd functions. For example

The elapsed minutes between any two such fields is

DateDiff ( "n", StartDateTime, EndDateTime)

and to add 75 minutes (for example) to an existing timestamp

DateAdd ( "n", 75, StartDateTime )

You really don't need to pay much attention to the absolute values of the DateTime stamps (although they must be relatively correct of course) ... only to the differences between them.

The other option is to simply create a numeric value (not a datetime) and do your own arithmetic on it.

 

[mward04]

I converted all of the dates over to a generic number format and this solved all of the problems. Before, I used the Excel format which allows you to see date/time and now that I used just a number to represent the date, access interprets the time difference correctly!

Thanks!

 

[FoxProProgrammer]

I see that you have a solution, so don't want to create a new problem....but just a question to make sure that you considered this...

Can the Departure date ever be more than one day from the Arrival data? Would it be better to capture the actual Arrival and Departure dates, rather than to allow them to default to Jan 0, 1900 or Dec 31, 1899? If you captured the actual dates, it doesn't seem as if this would be an issue and all the date functions (DateDiff, etc.) would work properly.

dz
dzaccess@yahoo.com

 

[mward04]

that's an excellent point and rarely, yes, it's a situation that we encounter. However, this data is entered in the same way. For example: 1 AM the next day is 25:00 and 2 AM (2 days from now) is 49:00. However, I change the formatting in Excel to a numeric format (1.04 and 2.04 respectively). When I import this into Access, it still works well.

So unless I am missing it, I believe this solution gets around the problem of having patients leave 2+ days in the future.

 

[FoxProProgrammer]

Excel permits you to enter date/time stamps such as 25:00 and 49:00 because it treats them as elapsed times from the default date, which in this case is Jan 0, 1900. That's why it sets the Format for such data to [h]:mm. Note the square brackets. They mean elapsed time, as opposed to h:mm, which is simply a specific time. Access doesn't allow those types of date/time stamps.

Just a suggestion that you might consider...if you are going to maintain this system in Excel, you might choose to use 25:00, 49:00, etc, to represent the date/time stamp. However, if you are going to maintain this system in Access, I'd recommend that you capture the actual Arrival and Departure dates. You wouldn't be able to store the actual Arrival and Departure dates for data that is already in the spreadsheet unless it is saved somewhere else, but you could start this practice for any new data added.

dz
dzaccess@yahoo.com

 

[mward04]

thank you and I appreciate your help DZ!