convert text to time 1

[tizwaz]

I have inherited a database in which there is a time duration field which is a text field. I have tried converting the data type to date/time format hh:nn but it threatens to delete the contents of the field. I need it to be time so that I can perform calculations on it.

 

[vmauro]

what does the field look like now, is it aleast consistant as a text field. Maybe u can run a make table query using that table in question and add a expression that can convert the field (some kind of string expression).

 

[tizwaz]

It contains entries like 0045, 0120, which represent 45 minutes and 1 hour, 20 mins. Also some of the records have no entry.

 

[cstringer]

You might put some formating code to convert from text to Short Time.

IE: FORMAT([YourtimeField],"Short Time&quot

Which will format the data to a short Time and then you can run your calculations.

 

[lespaul]

JohnEck just answered this in your other thread. please try not to cross post:

Thread701-706163

 

[PaulBricker]

I don't think JohnEck's post will help here because the values are not in a double precision format. Also, his post contained some errors in syntax that would need fixing.

I think if all the values have 4 digits then you can use

Left([field],2) & ":" & Right([field],2)

to get it close to the format you need. Then you should be able to use standard operations to get the results you need.
I don't know of any other way to deal with a textstring conversion to time.

Paul

 

[tizwaz]

thanks I'll try that.
Actually my other thread related to a slightly different problem I have. This was relating to a new database I'm starting in which I need to sum the duration field and wanted to format it correctly so that I can perform calculations. This second thread relates to a different database I inherited in which there is already a text field containing times.

 

[tizwaz]

Paul
Where should I put this - I'm trying to put it as a calcualted field in my query but am getting undefined function 'left' in expression when I try and run the query.

 

[PaulBricker]

You would put it on the Field line in your query something like this

SELECT myTbl.myfld, Left([myfld],2) & ":" & Right([myfld],2) AS MyTime
FROM myTbl;

Paul

 

[tizwaz]

Paul
I'm still getting the same error msg - undefined function Left.
I've got the other problem working thanks to a reply you gave in another thread so I can sum periods of time in my report.
However on this existing database I can't seem to convert the text field to time. I tried this function
Function Totaltime(duration As String) As Date
Dim Ldate As Date
Ldate = CDate(duration)
Totaltime = Ldate
End Function
but when I call this from the query it just gives dates eg 9/1/1900
Tried to add this line
Ldate = Format(Ldate, "hh:nn&quot
but am getting another error - cannot find project or library and the word Format is highlighted.
Really grateful for everybodies help but I'm starting to lose the will to live.

 

[PaulBricker]

Hang in there t. It's not that bad. First, you will have probems using CDate() because it wants a date value and when it doesn't get on, it makes up it's own - in this case 9/1/1900.
I'm not sure why you are having trouble with the Left function. You may need to check your references. Open any module and on the menu bar go to Tools...References and see if anything is labeled as Missing. If it is, uncheck it and close out the module. Then reopen it and recheck the Library that was missing.
If that doesn't work then we will have to see if we can find some other way to handle it but try checking the references first and we'll go from there.

Paul

 

[tizwaz]

Paul
yes I had about 4 references marked as missing - unchecked them closed module and opened again but I can't see the same things listed to re-check them.

 

[PaulBricker]

I assume you made some note of what they were. The libraries are listed in alphabetical order. You may have to scroll down. Try running the Functions again. They may work now. There's always been some issues about how to actually refresh the libraries. Let me know if they work. If you still have problems or didn't make a note of what they are, let me know what version you are running and I will let you know which libraries are a must to have selected.

Paul

 

[rdodge]

What version are you using? Be sure that you have "Visual Basic for Application", "Microsoft Visual Basic For Application Extensibility x.x", and "Microsoft Access x.0 Object Library" all checkmarked.

If you include some other code from different developers, you may also have other types of issues with ambiguity, thus in VBA, it wouls also be a good idea to prequalify your methods/properties/objects/variables. Example of this would be as follows:

VBA.Strings.Left([myfld],2)

Note, "Left" function (or Method) is in "Strings" class which is in "VBA" library, which can also be located via the Object Browser in VBA Editor.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[tizwaz]

Hi Both
I am running Access 2000. the missing references were Microsoft DAO 2.5/3.5 compatability library
DatePicker ActiveX control module
Calendar Active X control module and
cnfqprts OLE control module. I've scrolled down the list and they aren't there. Haven't tried to re-run the function yet but will do.
There will be other developer's code in the database as I think several people have worked on this over a number of years in fact it could have originally been created in access 97 and converted - don't really know the original developer has long left the company.
Visual Basic for Application", "Microsoft Visual Basic For Application Extensibility 5.3", and "Microsoft Access 9.0 Object Library" all already checkmarked.

 

[PaulBricker]

Make sure they didn't stay at the top. I've never seen a library just disappear. Can you create another db, import everything into and select those libraries from the list? I'm not sure which DLL's are associated with the DAO library but I will try and find out and then you can do a Find on it.
In the mean time try the functions again.

Paul

 

[rdodge]

When converting from Access 97 to Access 2000 or later, this particular error has been documented on MS's KB:

http://support.microsoft.com/default.aspx?scid=kb;en-us;199064&Product=acc2000

Be sure to checkmark DAO 3.6 Object Library in Access 2000 list of references.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[tizwaz]

OK guys - the left function now works and I've got returns in my field showing 00:10, 01:30 etc but when I tried to create a report using this query so that I could sum the times they weren't showing as a time field.
Then I added cdate to the query - SQL as follows and got a field returned showing 00:10:00 etc. Great I thought this should work and I'll be able to sum this in my report. Not so I am getting data type mismatch when I try and run the report.
SQL for query
SELECT Calls.[Time taken], Left([time taken],2) & ":" & Right([time taken],2) AS MyTime, Calls.[Date resolved], CDate([MyTime]) AS LatestTime, *
FROM Calls
WHERE (((Calls.[Date resolved]) Between #10/1/2003# And #10/14/2003#));
On the report I am using
=Int(Sum([latestTime]))*24+DatePart("h",Sum([latesttime])) & ":" & DatePart("n",Sum([latesttime]))
in a text box in the report footer but am getting data type mismatch. This calculation (which I gleaned from another thread that Paul answered) works fine on my new database using a field that was created as date/time and formatted as HH:nn but won't work with my newly converted field. Even sum ([LatestTime]) gives the same response.

 

[PaulBricker]

Put the formatted field in your report and then sum it using this expression

=Format(Sum(CDate([MyTime])),"Short Time&quot


Paul

 

[tizwaz]

Paul
Have done but still getting data type mismatch in expression.