Date/Time Format Issue

[AthenaBS]

Hi

I'm very new to Access, so please be gentle

I have imported a appointment database that I need to analyse, however the Date & Time data within the table has been written as;
Date: 20051220 - which I would like to read dd/mm/yyyy (20/12/2005)
Time: 800 - which I would like to read as hh:mm (08:00)

I would like to produce a report of all the queries, i.e. longest appointment, shortest etc... but display them in the correct Date/Time format.

So how should I go about this?
I cannot alter the imported table in anyway.

1. Can I produce a mirror like table of the existing table, that automatically use the correct Date/Time format?
2. Can I alter the values within a query?
3. Can I alter the final output in the Report?

What is the best way to tackle this?

 

[llkhoutx]

Your date is being stored as a string, not a date. A "date" is actually similar to a large floating point number with digits to the left of the decimal representing the elapsed hours from a root date (12/31/1899) and the digits to the right of the decimal represending fractions of hours.

Dates are dislayed in various manner via their format, not by the way they are stored. A date is a date is a date.

 

[AthenaBS]

So you're saying I don't need to bother with converting the Date/Time format.
In which case how would I go about finding the % of people Not seen within 20 minutes of arrival between a specific date period.

My Table has a column called "Slotdate", "arrived" and "in".
Arrived & In are both represented as Time in 800 format (as explained previously)

Date: 20051220 - which I would like to read dd/mm/yyyy (20/12/2005)
Time: 800 - which I would like to read as hh:mm (08:00)

I have a Form with 2 date fields Start Date[Text1] & End Date[Text2]

My Query SQL view currently consists of;

SELECT Appointments.slotdate
FROM Appointments
WHERE (((Appointments.slotdate) Between [Forms]![DateRange]![Text1] And [Forms]![DateRange]![Text2]));

This is for the entered date range.
But I can't work out duration in this format, it all goes wrong if someone arrived at 857 and went in at 905. The duration is only 8 minutes, but I can't get the correct duration result, let alone find the %

Any ideas would be most appreciated

 

[llkhoutx]

You're not working with "time," you're working with numeric values. Time is Access is a Date format field.

There is no such thing as "800 format". You just have a number.

Your so called "date" is a string, not an Access Data.

If you want to treat the contents of your fields like dates and time you have to unpack them as such.

With what you have you can convert the "800 format" time to minutes, eg

dim lngMinutes as long
'peel off seconds, then minutes which may be more than 1 digit
lngMinutes= right(Timefiled,2) + left(TimeField,len(TimeField)-2) * 60

You have confused Access datatypes. A date is a dat is a date; a number can be a byte, integer, long, or a string; text can be any kind of keystroke.
For Access data types, look at "DataType" column in a tables design view and see what types are allowable.

A textbox if unbound can be anything; if bound, it's datatype is determined by the the table field to which it is bound. If bound to a query field, look at the table field underlying the query field.

Hopefully the foregoing will get you started.

 

[raskew]

Hi -

You might take a look at this article to get a clearer understanding of how dates are stored in Access http://support.microsoft.com/kb/q130514/

Bob

 

[ozphantom]

Hi AthenaBS,

Are you are still having trouble with this?

As you mentioned that you cannot change the source data table, you will need to create a query which converts the text into date format.

To do this, create a new query in design view and in the "Field" space, write an expression that converts the text to a date.

For example, take the left 4 characters and turn them into the year. Then do similar for month and day, put them together and you will have a field expression that looks something like the following:

Date: "#" & Day(Right(Left([fieldname],8),2)) & "/" & Month(right(Left([fieldname],6),2)) & "/" & Year(Left([fieldname],4)) & "#"​

Once you have done this, you can do something similar for the time.

This will not alter the original table data but will provide you with a query where the data is in date format instead of just text. Once you have the query set up, you can then do calculations based on differences in time, etc.

Hope this helps!

Best wishes,

OzPhantom

 

[ozphantom]

woops - you won't need to convert the day() month() and year() in your case as you are simply grabbing the values instead of needing to extract them.