Solved

Need to convert Time to Numbers in Access 2013 Query

Posted on 2015-01-19
14
290 Views
Last Modified: 2016-01-25
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?
0
Comment
Question by:rnhturner1
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40558172
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?
0
 

Author Comment

by:rnhturner1
ID: 40558242
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40558252
> 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
0
 

Author Comment

by:rnhturner1
ID: 40558594
Gustav - Why ask why?  I need it look like a "number" field not a date.  It is crucial for a report I need.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40558636
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
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40558713
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40558975
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?'
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:PatHartman
ID: 40559051
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40559143
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
0
 

Author Comment

by:rnhturner1
ID: 40560020
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.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40560031
Then you need a function like this:
Public Function FormatHourMinute( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String
  
' Returns count of days, hours and minutes of datTime
' converted to hours and minutes as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03# + #20:01#
'   returns: 30:04
'
' 2005-02-05. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinute     As String
  Dim strHourMinute As String
  
  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute count when needed.
  strMinute = Right("0" & CStr(Minute(datTime)), 2)
  strHourMinute = strHour & strSeparator & strMinute
  
  FormatHourMinute = strHourMinute
  
End Function

Open in new window

/gustav
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40560037
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).
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40560081
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40560140
@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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now