Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access export date and time fields and keep certain format

Posted on 2009-07-06
4
Medium Priority
?
202 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

773 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