Solved

Format Long Integer to Time

Posted on 2014-01-13
2
355 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

685 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