Solved

Access export date and time fields and keep certain format

Posted on 2009-07-06
4
197 Views
Last Modified: 2013-11-27
I have a query that I am exporting and need to keep the format a certain way.
the date needs to look like this

20090707 for July 7, 2009

I have the format correct in the query but when I export it it turns to this.

7/7/2009 1:47:58 PM

Same with the time - I have it set to HHMMSS
hour minute second
and when exporting I get

1:47:58 PM

0
Comment
Question by:Pdeters
  • 2
4 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 24788110
FORMAT([Table].[Field], "yyyyMMdd")
Wouldn't this work.
P.
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 24788135
Also, if you are exporting the result into another table then make sure the target column default format is set to yyyyMMdd. You can set this in the table design mode.
P.
0
 

Author Closing Comment

by:Pdeters
ID: 31600302
I put in the query as it is not pulling a date but using today's date

Format(Date(),"yyyyMMdd")

Thanks!1
0
 
LVL 5

Expert Comment

by:dvz-
ID: 24788312
You can also try

date_format([table].[fld], '%Y%m%d')
#should print "yyyymmdd" 20090631

and

date_format([table].[fld], '%h%i%s')
#should print "hhmmss"

query usage:
$query = "SELECT date_format(datefld , '%Y%m%d') FROM ourTable";

Hope this helps.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

792 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