Link to home
Start Free TrialLog in
Avatar of rnhturner1
rnhturner1

asked on

Need to convert Time to Numbers in Access 2013 Query

I have lots of old data in my really large Table in Access.  One of those columns is Date/Time.
I am unable to convert it to a number, because I get an error stating:

"Microsoft Access can't change the data type
 There isn't enough disk space or memory."


What I thought I could do was create a query and change it that way to a number.  Any ideas?
Avatar of Simon
Simon
Flag of United Kingdom of Great Britain and Northern Ireland image

First validate the data in your table. Is it actually datetime datatype?

If you can't convert it all at once, set a WHERE condition in the query that is doing the conversion.

What's the actual SQL statement that you're using?

I hope you're leaving the old table untouched and doing your conversion into a new table?
Avatar of rnhturner1
rnhturner1

ASKER

I should have been a little more clear.
I am still new to Access 2013.
Yes my table has it in date/time.

Since new to access, not sure what you mean by set a where condition?  Can you elaborate?

Yes I am working from a copied table as I try these.
> I am unable to convert it to a number

And why would you do so? It is a number already, a Double.
So just format or calculate as you wish.

/gustav
Gustav - Why ask why?  I need it look like a "number" field not a date.  It is crucial for a report I need.
Sorry, but we have no knowledge about your application. If you don't tell, we have to guess, and it is not very common to "have a date look like a number".

Anyway, to do so, just use CDbl in your expression:

=CDbl([YourDateField])

Note that the textbox must be named otherwise than YourDateField.

Or use a query with a similar expression:

DateNumber: CDbl([YourDateField])

/gustav
Also guessing... Do you want to format your date to yymmdd (20141231)  or a section of that e.g. yyyymm (201412) to group by period?
If so, there is the FORMAT function. FORMAT ([YourDateField],"yyyymmdd") = "20141231" - but note that this is actually returning a string value.

Edit: Sorry, I've just re-read the title of your question - do you want to extract just the time portion of a datetime value? You can still do this with the FORMAT function...
e.g. FORMAT([YourDateTimeField\,"hh:nn:ss") = "10:43:32" (string result)
or
FORMAT([YourDateTimeField\,"hhnn") = "1043" (string result)
or to keep it numeric
TIMEVALUE([yourdatetimefield)=10:43:32 (numeric)

You can also format the numeric output however you want to display it as a decimal value, 24 hour clock or with AM/PM.
Gustav - Why ask why?  I need it look like a "number" field not a date.  It is crucial for a report I need.
Why is a very important question, though!
Access stores DateTimes as a special kind of Double
The time is the decimal fraction of a day.
It is always positive (some quibble about the special part, but subtracting 8 hours (~0.333333333) from Day -1 does not yield ~-1.333333333333 as it would mathematically, but ~ -2.666666666666666.

Getting confused yet!?

Zero day for Access is 30-Dec-1899, or more properly in ISO format, 1899-12-30
So formatting Now() as a number --> CDbl(Now()) will give you some number larger than 42000 at the moment.
2015-01-19 6:35 PM is ~42023.768

So, knowing what you'd like to see becomes critically important -- because it's highly doubtful you actually want the number that Access has stored in the DateTime field.  It's much more likely you want a specialized format that contains nothing but digits -- and that's a very, very different thing.

Hence 'why?'
Sounds like you are trying to actually modify the table to populate a newly created column or even to modify the format of an existing column.  In any event, there is no need to do that, nor should you do it.  Use a query to select the set of data needed for the report and include a calculated column that uses the Format() function to format the date however you want to see it in the report.  Keep in mind that once you format the date, it becomes a string (as someone already mentioned) and it will no longer sort like a date unless it is in a ymd order.  If you format it as dmy or mdy, you cannot sort on the field and will need to include the original field in the query so you can sort on it.  And in that case, you would actually need to bind the original date to a control on the report.  You can make the control small and hidden but it needs to be there because Access is smarter than we are and it rewrites the RecordSource query of each report to eliminate columns it thinks are not used.
In your report you can also just set the Format property of the textbox bound to the date field:

   Format: 0

Voila. The dates become numbers.

Or, still guessing, if you just want to force a numeric date format, use:

    Format: yyyy/mm/dd

/gustav
OK...Sorry for the lashing back.  I am just getting frustrated with this one piece.

I need to show a manager the total amount of time elapsed from a report.  I am linking to an Excel Sheet that has this number correctly showing something like 47 hours 22 minutes and 10 seconds(like 48:22:10).  The problem is when I pull this field into Access, it is showing something like 21 hours 22 minutes and 10 seconds(like 21:22:10).  NOTE: Those are not the exact numbers as I was just pulling from memory but the idea is the same as it will not show as the real total.  So in effect, in Access I have a whole column of useless data because it won't show over 24 hours.  I thought that if I could just change this to a numeric value that I could show the value like 1.95 to show that it was almost two days.

I can change the Data Type on smaller data from excel, but I am joining that excel file to my much larger existing database with an update query on access.  This is so large that when I try to change the data type for that column I get the error I mentioned in my initial post.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The Excel custom time format to show more than 24 hours (in hours and minutes) is [h]:mm. I think this works in Access as well, but let me check.

If possible, post a sample of the data (as an accdb/mdb with just that table in and all columns except the relevant one blanked out if necessary).
The date/time data type in Access shows a point in time.  It doesn't show elapsed time.  I've attached a database with date functions you can use.  Look at the third tab - Special DateDiff Function.  It allows you to specify how you want the difference formatted.
UsefulDateFunctions121812a.zip
@gustav has you well on the way.
As a side note, even if you had been able to covert the datatimes to numbers and done the math, you would have still gotten a decimal result in days i.e. 1.333333333333333 -- which is 1 day, 8 hours or 32 hours.
You would still have needed a custom function to wrangle that result into 32:00:00

If you ever did actually need to wrangle the columns, you probably would have best success by creating a temporary table that has a column of the same data type as the primary key and a column of the datatype you want to end with.
You'd then append the primary key and a conversion of the unhappy column to this new table.  You'd delete the unhappy column, and add a new column with the desired data type.  Finally, you'd update the new column with the data in the temporary table and then delete the temporary table.

This is the key thing to understand.  Access stores datetimes as Doubles.  It displays them as whatever format you put to the field, column or control
So in effect, in Access I have a whole column of useless data because it won't show over 24 hours. So in effect, in Access I have a whole column of useless data because it won't show over 24 hours.
99 for 100, that column has a format of hh:mm:ss which tells Access 'don't display the date part'  The problem is that unless you want to show actual Dates, there is no equivalent format to Excel's [h]:nn:ss to display hours greater than 24.  The MS kb link -- although it seems to be down at the moment -- is http://support.microsoft.com/kb/210276