[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

Format Number Long Integer Field

Table1 is Source Table and Field5 is Number Long Integer In Table1.
The First Query(Append)(Qry1_Update_6Chars) Formats Field5 find, if Field5 = 6 Characters:  
 ADD-TIME: CStr(Format(CDate(Left([Field5],2) & ":" & Mid([Field5],3,2)),"hh:nn AM/PM"))
---------------------------------------------------------------------------------------------------------------
My problem is Qry2_Update_5Chars which I tried append query:
ADD-TIME: IIf(Len([Field5])=5,CStr(Format(CDate(Left([Field5],1) & ":" & Mid([Field5],2,2)),"hh:nn AM/PM")))
If you just view it, it seems find but if try to Run to Append it, it doesn’t work.
Any suggestions would be appreciated!
---------------------------------------------------------------------------------------------------------------
Table2 now show QRy1_Update_6Chars  completed, If I can just get is Qry2_Update_5Chars to work.
Foramt.mdb
0
ca1358
Asked:
ca1358
  • 2
  • 2
2 Solutions
 
FlysterCommented:
If you are trying to get the time value in table 2 to match field 5 in table 1, you need to create a relationship between the 2. Also, you should use an Update query, not an Append. Here's the sql:
UPDATE Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.[COMMITMENT-NUM] SET Table2.[ADD-Time] = IIf(Len([Field5])=5,CStr(Format(CDate(Left([Field5],1) & ":" & Mid([Field5],2,2)),"hh:nn AM/PM")));

Open in new window

See attached
Flyster
Format.mdb
0
 
ca1358Author Commented:
Qry2_Update_5chars does update the 5 Characters but wipes out 6 Characters in the Add_Time Field.

Dont understand why.
0
 
Gustav BrockCIOCommented:
First, you should never store date or time as anything else than data type Date. No exceptions.

Second, you only need one query:

INSERT INTO
    Table2
    ( [COMMITMENT-NUM], [ADD-TIME] )
SELECT
    Table1.Field1,
    TimeValue(Format("0" & [Field5],"!@@\:@@\:@@")) AS TrueTime
FROM
    Table1;

If you insist on a text date, apply Format:

INSERT INTO
    Table2
    ( [COMMITMENT-NUM], [ADD-TIME] )
SELECT
    Table1.Field1,
    Format(TimeValue(Format("0" & [Field5],"!@@\:@@\:@@")), "hh:nn AM/PM") AS TextTime
FROM
    Table1;

/gustav
0
 
ca1358Author Commented:
Thank you both for your help.  Sorry but I am not in control of the Extracts and I agree with you they should of format as Time data type.  I thank you again for helping learning more about Formatting.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now