yballan
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.
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.
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.ShouhinGazouUR L, NewProducts.MobileShopGazo uSakusei, NewProducts.OtherGazou1URL , NewProducts.OtherGazou2URL , NewProducts.OtherGazou3URL , NewProducts.HanbaiKakaku, NewProducts.KaiinKakaku, NewProducts.Teika, NewProducts.Genka, NewProducts.Zaikosuu, NewProducts.ZaikoKanri, NewProducts.MinKounyuSuryo u, NewProducts.MaxKounyuSuryo u, DateValue(NewProducts.Star tSaleDate) AS Expr1, TimeValue(NewProducts.Star tSaleTime) , DateValue(NewProducts.EndS aleDate), TimeValue(NewProducts.EndS aleTime), NewProducts.Unit, NewProducts.Weight, NewProducts.OutofStockHyou ji, NewProducts.TekiseiStock, NewProducts.HyoujiJun, NewProducts.KantanDesc, NewProducts.Description, NewProducts.MobileShopDesc , NewProducts.SmartPhoneDesc , NewProducts.TrackBackSette i, NewProducts.NewMarkSettei, NewProducts.NewMarkGazou, NewProducts.CalamelCategor yID, 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
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.ShouhinGazouUR
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.
ASKER
in the macro?
If you just export the table, what does the datetime value look like?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dear Gustav and Kelvin,
Thank you for detailed explanation, now I understand, and it worked perfectly!!!
yballan
Thank you for detailed explanation, now I understand, and it worked perfectly!!!
yballan
You are welcome!
/gustav
/gustav
It would have all the fields from the table and you can use the Format function with your date/time field(s).
FormattedDateTime:Format([