Solved

Format Long Integer to Time

Posted on 2014-01-13
2
354 Views
Last Modified: 2014-01-13
I am trying to Append ADD-Time (Field5) Field from Table VFE1M07D to LPS Extract Table.  The Format of the Table VFE1M07D is the following:  5 places or 6 places.  

VFE1M07D
Field5
84622
121716
151030
104714
114707
94107
84310

The 6 places work but the 5 places does not.
LPS Extract_Commitments
ADD-TIME

12:17 PM
3:10 PM
10:47 AM
11:47 AM



I have included a database with examples. "UpdateAddTime"

This one WORKS:
Query:  "1_AppendExtract"  works updating the 6 places but not the 5 places.

SQL Statment:
INSERT INTO [LPS Extract_Commitments] ( [COMMITMENT-NUM], [ADD-Time] )
SELECT VFE1M07D.Field1 AS Expr1, CStr(Format(CDate(Left([Field5],2) & ":" & Mid([Field5],3,2)),"hh:nn AM/PM")) AS [ADD-TIME]
FROM VFE1M07D;
===============================================
So I tired Query 2 but it doesn’t work.

Query: "2_AppendExtract_Both 6Len&5Len"

SQL Statment:
INSERT INTO [LPS Extract_Commitments] ( [COMMITMENT-NUM], [ADD-Time] )
SELECT VFE1M07D.Field1 AS Expr1, ((IIf(Len([VFE1M07D]![Field5])=6,CStr(Format(CDate(Left([Field5],2) & ":" & Mid([Field5],3,2)),"hh:nn AM/PM"))))) Or ((IIf(Len([VFE1M07D]![Field5])=5,CStr(Format(CDate(Left([VFE1M07D]![Field5],1) & ":" & Mid([VFE1M07D]![Field5],2,2)),"hh:nn AM/PM"))))) AS [ADD-TIME]
FROM VFE1M07D;

Any help would be appreciated.
UpdateAddTime.mdb
0
Comment
Question by:ca1358
2 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 39777229
You can use this formula for conversion:
 TimeSerial(Int([Field5]/10000),Int(([Field5] Mod 10000)/100),([Field5] Mod 100))

Open in new window


SQL:
UPDATE [LPS Extract_Commitments] RIGHT JOIN VFE1M07D ON [LPS Extract_Commitments].[COMMITMENT-NUM] = VFE1M07D.Field1 SET [LPS Extract_Commitments].[COMMITMENT-NUM] = [Field1], [LPS Extract_Commitments].[ADD-TIME] = TimeSerial(Int([Field5]/10000),Int(([Field5] Mod 10000)/100),([Field5] Mod 100));

Open in new window

0
 

Author Closing Comment

by:ca1358
ID: 39777270
Thank you!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

840 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