Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Formatting date and time in query

Posted on 2013-05-12
9
Medium Priority
?
573 Views
Last Modified: 2013-05-13
Dear Experts,

I am trying to export a table into a .cvs file, with date and time formatted as:
5/13/2013
16:00

And what I am doing is not working.  I have tried using DateVal(date) and TimeVal(time), tried to format the field as short date and short time, but whatever I have done so far results in:
5/13/2013 0:00
12/30/1899 16:00:00

Please advise.
0
Comment
Question by:yballan
[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
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 39160034
Instead of exporting the table expost a query based on the table.

It would have all the fields from the table and you can use the Format function with your date/time field(s).

FormattedDateTime:Format([DateTimeField], "mm/dd/yyyy hh:mm")
0
 

Author Comment

by:yballan
ID: 39160072
Dear imnorie,

Thank you for your prompt response, I don't think I know how to export a query.
Here is what I am doing.
I call a query to get a table for export:
SELECT NewProducts.CategoryL, NewProducts.CategoryS, NewProducts.Keiban, NewProducts.Shouhinmei, NewProducts.ShouhinGazouURL, NewProducts.MobileShopGazouSakusei, NewProducts.OtherGazou1URL, NewProducts.OtherGazou2URL, NewProducts.OtherGazou3URL, NewProducts.HanbaiKakaku, NewProducts.KaiinKakaku, NewProducts.Teika, NewProducts.Genka, NewProducts.Zaikosuu, NewProducts.ZaikoKanri, NewProducts.MinKounyuSuryou, NewProducts.MaxKounyuSuryou, DateValue(NewProducts.StartSaleDate) AS Expr1, TimeValue(NewProducts.StartSaleTime), DateValue(NewProducts.EndSaleDate), TimeValue(NewProducts.EndSaleTime), NewProducts.Unit, NewProducts.Weight, NewProducts.OutofStockHyouji, NewProducts.TekiseiStock, NewProducts.HyoujiJun, NewProducts.KantanDesc, NewProducts.Description, NewProducts.MobileShopDesc, NewProducts.SmartPhoneDesc, NewProducts.TrackBackSettei, NewProducts.NewMarkSettei, NewProducts.NewMarkGazou, NewProducts.CalamelCategoryID, NewProducts.CaramelWord1, NewProducts.CaramelWord2, NewProducts.CaramelWord3, NewProducts.CaramelDesc, NewProducts.Title, NewProducts.Keyword, NewProducts.PageDesc, NewProducts.Shipping, NewProducts.Show INTO ForExport
FROM NewProducts;

Then I use TranserText to export it (please see attached)

Please give me a little more guidance?

Thank you,
EE1.jpg
0
 
LVL 35

Expert Comment

by:Norie
ID: 39160081
Change ForExport to the name of the query based on that table.
0
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.

 

Author Comment

by:yballan
ID: 39160115
in the macro?
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39160457
If you just export the table, what does the datetime value look like?
0
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1000 total points
ID: 39160473
There is no need to create a table for this.
Modify your query to a normal select query which you export as you previosly did with the table:

SELECT
  NewProducts.CategoryL,
  NewProducts.CategoryS,
  NewProducts.Keiban,
  NewProducts.Shouhinmei,
  NewProducts.ShouhinGazouURL,
  NewProducts.MobileShopGazouSakusei,
  NewProducts.OtherGazou1URL,
  NewProducts.OtherGazou2URL,
  NewProducts.OtherGazou3URL,
  NewProducts.HanbaiKakaku,
  NewProducts.KaiinKakaku,
  NewProducts.Teika,
  NewProducts.Genka,
  NewProducts.Zaikosuu,
  NewProducts.ZaikoKanri,
  NewProducts.MinKounyuSuryou,
  NewProducts.MaxKounyuSuryou,
  Format(NewProducts.StartSaleDate, "m\/d\/yyyy") AS SSDate,
  Format(NewProducts.StartSaleTime, "hh\:nn") As SSTime,
  Format(NewProducts.EndSaleDate, "m\/d\/yyyy") As ESDate,
  Format(NewProducts.EndSaleTime, "hh\:nn") As ESTime,
  NewProducts.Unit,
  NewProducts.Weight,
  NewProducts.OutofStockHyouji,
  NewProducts.TekiseiStock,
  NewProducts.HyoujiJun,
  NewProducts.KantanDesc,
  NewProducts.Description,
  NewProducts.MobileShopDesc,
  NewProducts.SmartPhoneDesc,
  NewProducts.TrackBackSettei,
  NewProducts.NewMarkSettei,
  NewProducts.NewMarkGazou,
  NewProducts.CalamelCategoryID,
  NewProducts.CaramelWord1,
  NewProducts.CaramelWord2,
  NewProducts.CaramelWord3,
  NewProducts.CaramelDesc,
  NewProducts.Title,
  NewProducts.Keyword,
  NewProducts.PageDesc,
  NewProducts.Shipping,
  NewProducts.Show
FROM
  NewProducts;

/gustav
0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 1000 total points
ID: 39161002
The might be one reason for needing the data to go into a table. An export from a query, can truncate memo fields if the data exceeds 256 characters. If any of you fields have this limit, you may have to write the data into a table and export the table. Fields such as NewProduct.Description need to be checked. If ANY fields that supply data to the table are Memo, look at the expected length of the actual data.


Kelvin
0
 

Author Closing Comment

by:yballan
ID: 39161053
Dear Gustav and Kelvin,

Thank you for detailed explanation, now I understand, and it worked perfectly!!!

yballan
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39161067
You are welcome!

/gustav
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

618 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