Link to home
Create AccountLog in
Avatar of yballan
yballanFlag for United States of America

asked on

Formatting date and time in query

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.
Avatar of Norie
Norie

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")
Avatar of yballan

ASKER

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
Change ForExport to the name of the query based on that table.
Avatar of yballan

ASKER

in the macro?
If you just export the table, what does the datetime value look like?
SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of yballan

ASKER

Dear Gustav and Kelvin,

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

yballan
You are welcome!

/gustav