?
Solved

Format Long Integer to Time

Posted on 2014-01-13
2
Medium Priority
?
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

752 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