Solved

Formatting date and time in query

Posted on 2013-05-12
9
520 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
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 33

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 33

Expert Comment

by:Norie
ID: 39160081
Change ForExport to the name of the query based on that table.
0
 

Author Comment

by:yballan
ID: 39160115
in the macro?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 22

Expert Comment

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

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 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 250 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 49

Expert Comment

by:Gustav Brock
ID: 39161067
You are welcome!

/gustav
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now