Convert part of text string to Time

[SharonH]

I have data that I import from an Excel sheet into Access. the field I have problems with is called [SaleTime]. It contains the date and time of the article being weighed. The string comes in like this:
20120314080253
I have two fields in my query. One converts the first part to a date and the other converts the second part to Time. It works great in Access.

Weigh_Date: DateSerial(Left([SaleTime],4),Mid([SaleTime],5,2),Mid([SaleTime],7,2))

Weigh_Time: TimeSerial(Right(Left([SaleTime],10),2),Right(Left([SaleTime],12),2),Right([Saletime],2))

My problem is when I create an ODBC link to a new sheet in Excel, the time shows as 12:00:00 AM in all the fields.

What am I doing wrong?

Thanks a lot, Sharon

[TheSmileyCoder]

Are you adding these 2 values together and storing them in a single field in access? Access has one field type called DateTime used for storing both date and time information. Both can (and I would think in most cases should) be stored in only 1 field. It sounds like your export is only exporting the date part.

[NeoPa]

If you're linking to a new sheet then I expect there is no data therein. 12:00:00 AM (or midnight) is actually the time-equivalent of 0.

[SharonH]

Hi there. I am trying to Create a Date Field and a separate Time Field. The client needs to extract data based upon Time (the staff work in shifts). I have now realized, after using Microsoft Query, that the data actually comes in like this:
09/03/2012 12:00:00 AM (Date Field)
and the Time field looks like this: 00/01/1900 12:00:00 AM.
I am totally stumped.
Can you help? Thanks in anticipation :)

[SharonH]

Hi NeoPa. I'm linking the Access query to Excel sheets that I wanted to have the system update automatically so the user just needed to open Excel to have the latest data at his fingertips.

[NeoPa]

I'm sure your thinking makes sense Sharon. Unfortunately, your explanation is still very ambiguous so it's hard to know where/how to help you.

[SharonH]

Hi NeoPa. OK, I shall try to explain it better. The data comes from a scale (it weighs sugar packages to ensure the weight is within limits). The scale data links to Excel. I then import the raw data into Access.
It is important that I create a date field as a separate field and a time field as a separate field in order to analyse the data. The date from the raw data looks like this and imports to Access as text: 20120308110024 which is basically yyyymmddhhmmss.

I need to separate this "text number" into two separate fields. In other words, extract the first 8 numbers to create a new Weigh Date field in the date format dd/mm/yyyy. Then I need to extract the last 6 numbers to create a new Weigh Time field in the time format of hh:mm:ss.

The date field needs to be dd/mm/yyyy and the time field needs to be hh:mm:ss.

The query I have reads as follows:

Weigh_Date: DateSerial(Left([SaleTime],4),Mid([SaleTime],5,2),Mid([SaleTime],7,2))
Weigh_Time: TimeSerial(Right(Left([SaleTime],10),2),Right(Left([SaleTime],12),2),Right([Saletime],2))

When I check the query in Access it looks perfect but after linking the query to Excel, the data actually looks like this:

Weigh_Date: dd/mm/yyyy hh:mm:ss The Date is correct (ie: 09/03/2012 but the time is just zeros (ie: 00:00:00)

The Weigh_Time in excel comes in with the time and the date wrong (ie: 00/01/1900 00:00:00)

It isn't separating the Date info and the time info into two separate fields and it isn't reading the date or the time correctly when linking the data from Access to Excel.

I don't know what to do. I am thinking of bringing the data directly into Excel but that will ultimately create a HUGE file.

I hope this makes more sense? Regards, Sharon

[TheSmileyCoder]

If you open the excel sheet, and select the column where you have EXPORTED the Weigh_Time to, what is its format property set to?

If you change it to: Number with at least 4 decimal places, what values does it show?

[SharonH]

I haven't actually exported the data, I created an ODBC link from the Access DB to Excel but it comes is as a number format. When I change it looks fine but my Excel references to the data don't work.

However, I'm feeling rather clever at the moment. I have now done the following in my Access query. The only problem I am having now is the RealTm field. The time doesn't come into Excel correctly, it shows as "00/01/1900". I haven't been able to find a command like "CDate" to use for time. Any ideas? Here is my query:

Convert text number - Weigh_Date: DateSerial(Left([SaleTime],4),Mid([SaleTime],5,2),Mid([SaleTime],7,2))+TimeSerial(Right(Left([SaleTime],10),2),Right(Left([SaleTime],12),2),Right([Saletime],2))

Format as Date - WghDt: CDate([Weigh_Date])

Extract Date Part of field - RealDt: CDate(Left([WghDt],10))

Extract Time Part of field - RealTm: CDate(Right([WghDt],11))

[NeoPa]

SharonH:
I hope this makes more sense? Regards, Sharon

It doesn't help much with the ambiguities I'm afraid Sharon. You make very clear what was already clear, which is your thinking and what you need to see, but give very little in the way of technical information that we can work with. Let me see if I can clarify what it is you need to explain better.

How do you access the data that is stored in an Excel spreadsheet from your Access database? You mention ODBC, importing and linking. I have no idea why ODBC might come into any part of this, and generally only one of linking and importing would be required (although I have a project where I use linked tables to import - but I'd never explain this situation without making those separate elements clear).

I'll assume that the layout of the sheet you're importing from is not too relevant at this stage, but how the data is stored in Excel is important. The format of the column is irrelevant for the process, but it may confuse you if it's not taken into consideration when you report how it's held in Excel. I suggest you focus on an individual cell from the sheet, make sure the format is set simply to General and the Horizontal Alignment also set to General then post what the value looks like in here. Ensure the column width is wider than the data then you can also indicate which side the data is aligned to. If it's on the right then we're dealing with numeric data and if on the left then string data. There is no need to save changes after getting this information.

When you have linked/imported the worksheet then please report here details of the field that has been created. We need the type and length characteristics.

[SharonH]

OK. Let me try. I'm using Office 2010. The data in Excel was brought in by clicking the Data Tab, then choosing "Get External Data" "From other sources". I then used Microsoft Query to bring the data from my Access query into my Excel spreadsheet.

All the columns have been formatted as General and they look like this:

   WghDt     RealDt  RealTm
40977.34219   40977   0
40977.34236   40977   0

All the figures are on the right side of the columns.

When I change the formatting of the columns to Date and Time, they look like this (also on the right).

   WghDt      RealDt      RealTm
09/03/2012  09/03/2012  12:00:00 AM
09/03/2012  09/03/2012  12:00:00 AM

Does that help?

Regards, Sharon

Sorry, it doesn't look good on this forum. I don't know how to paste it as a spreadsheet. I just use Control + V to paste.....

[NeoPa]

One important point to remember, and I suspect it is at the heart of this issue somewhere or other, is that 32-bit long values only go up to 2 GB if signed and still only 4 GB if unsigned. That's ten decimal places at most. Eight places of date plus six of time come to fourteen DP. Unless we can force this into being seen as string data we will have serious problems reading these values.

Does that make the issue we're dealing with clearer Sharon? We need some precise technical information before we can look at a way of forcing Excel to behave itself.

** At this point I caught your post #9 which I'd missed earlier. **

An ODBC linked table? My system doesn't allow linking to Excel via ODBC. ODBC is very flexible so I cannot say it's impossible on your system, but I would ask why use ODBC for an Excel link when Access has inbuilt Excel connectivity (Instead of selecting ODBC you can select Microsoft Excel (*.xlsx; *.xls)) from Files of type:.

Regardless of that issue you are still falling over the problem of Excel trying to present the data numerically.

[NeoPa]

Now there is a post #11 to respond to. I will prepare that but I think I need to put down a post marker in case you beat me to the punch again :-D

[SharonH]

Oops. Sorry. Getting a bit mixed up here :(

[NeoPa]

SharonH:
Sorry, it doesn't look good on this forum. I don't know how to paste it as a spreadsheet. I just use Control + V to paste.....

Check out [code] Tags Must be Used. It will explain how to format code and/or tabular data.

From your post #11 it seems clear that the Date/Time data ([WghDt]) in Excel has come through fully, but it is neither text nor Long data, but Double (or Date/Time). This is good news. Try the following (assuming [WghDt] is in A2) :
[RealDt] - =DATEVALUE(TEXT(A2,"d mmmm yyyy"))
[RealTm] - =TIMEVALUE(TEXT(A2,"HH:mm:ss"))

[SharonH]

Hi NeoPa. Do I try your suggestion in Excel or in Access? Thanks, Sharon

[NeoPa]

In Excel Sharon. The reference to A2 would only make sense in Excel.

[SharonH]

Hi NeoPa. Sorry I haven't got back to you earlier, but I was in the bush (wilderness) for the past 5 days.
Thanks, will try that.
Regards
Sharon

[NeoPa]

No worries Sharon. I won't make any comments about the bush, but it sounds exciting.