Solved

Format Long Integer to Time

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

Accepted Solution

by:
als315 earned 500 total points
Comment Utility
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
Comment Utility
Thank you!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

743 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

14 Experts available now in Live!

Get 1:1 Help Now