Solved

Access 97: Exporting Date Fields to CSV Text File

Posted on 2003-11-21
21
554 Views
Last Modified: 2006-11-17
I am exporting a table from Access 97 to a csv text file. I want the date field to be in the format like "January 1, 2003" instead of 01/01,03. How do I get that format to be exported into the text file?
0
Comment
Question by:abnc
  • 11
  • 8
  • 2
21 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9799798
I'm thinking it goes something like this....

  dim rs as recordset
  set rs = currentdb.openrecordset("blName",dbopendynaset)
  for each Itm in me("lstCustomers").ItemsSelected
    rs.addnew
      rs("CustomerNumber") = me("lstCustomers").ItemData(itm)
      rs("CustomerCOmment") = me("txtSampleComment")
    rs.update
  next itm

this needs tuned up to system specs,... but..

regards
Jack
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9799805
sorry, wrong question.... (right answer though)
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9799822
Us and Import/Export Specification to "define" your import options. Cheers, Andrew
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9799824
Hey abnc!
  THIS ONE is for your question!!

  use:
   cstr(format([datevalue], "mmmm dd, yyyy")) to:
   format as specified
   convert to string value
0
 

Author Comment

by:abnc
ID: 9799946
None of the answers so far seem to work. Perhaps I am doing something wrong still. However, here is the SQL View of the Query Data I am trying to export. I want the date information to be formatted in such a way that when I export the query to a text file, the date will be in the format I want, mainly with the month spelled out, followed by the day, and then the 4 digit year.

Here's the SQL View right now:

SELECT [Complete Audio Catalog].Title, Speakers.[Last Name], Speakers.[First Name], [Complete Audio Catalog].Date, [Service Types].Description, [Complete Audio Catalog].[Print Label]
FROM Speakers INNER JOIN ([Service Types] INNER JOIN [Complete Audio Catalog] ON [Service Types].[Service Type] = [Complete Audio Catalog].[Service Type]) ON Speakers.[Speaker ID] = [Complete Audio Catalog].[Speaker ID]
WHERE ((([Complete Audio Catalog].[Print Label])=Yes));

Thanks,
AB
0
 
LVL 32

Accepted Solution

by:
jadedata earned 400 total points
ID: 9799965
SELECT
  [Complete Audio Catalog].Title,
  Speakers.[Last Name], Speakers.[First Name],
  format([Complete Audio Catalog].Date,"mmmm dd, yyyy") as CADDate,
  [Service Types].Description, [Complete Audio Catalog].[Print Label]
FROM Speakers
  INNER JOIN ([Service Types]
    INNER JOIN [Complete Audio Catalog] ON [Service Types].[Service Type] = [Complete Audio Catalog].[Service Type])
  ON Speakers.[Speaker ID] = [Complete Audio Catalog].[Speaker ID]
WHERE ((([Complete Audio Catalog].[Print Label])=Yes));
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9799974
 That table.field named DATE is gonna be giving you problems plenty sooner or later.  

  DATE is a built in function name in Access...
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9799988
and,... do you really need to put the print label field in the output??  (since it appears to be the condition for inclusion in the recordset)...?
0
 

Author Comment

by:abnc
ID: 9800045
I am still getting an error when opening the query. In the field where the date should be shown, I now get "#error" in the field. I don't know if this has any bearing, but I am using Access 97. The query is basically to look at the table and pull only those records marked for labels to be printed. I then want to export the query out to the text file.

It will work fine if I leave the query as:

SELECT [Complete Audio Catalog].Title, Speakers.[Last Name], Speakers.[First Name], [Complete Audio Catalog].Date, [Service Types].Description, [Complete Audio Catalog].[Print Label]
FROM Speakers INNER JOIN ([Service Types] INNER JOIN [Complete Audio Catalog] ON [Service Types].[Service Type] = [Complete Audio Catalog].[Service Type]) ON Speakers.[Speaker ID] = [Complete Audio Catalog].[Speaker ID]
WHERE ((([Complete Audio Catalog].[Print Label])=Yes));


But in this case the day is not in the format I want.

Here is the what the SQL View of the query looks like after I saved it with the suggested values above. This one gives me the "#error" in the field.

SELECT [Complete Audio Catalog].Title, Speakers.[Last Name], Speakers.[First Name], Format([Complete Audio Catalog].Date,"mmmm dd"", ""yyyy") AS CADDate, [Service Types].Description, [Complete Audio Catalog].[Print Label]
FROM Speakers INNER JOIN ([Service Types] INNER JOIN [Complete Audio Catalog] ON [Service Types].[Service Type] = [Complete Audio Catalog].[Service Type]) ON Speakers.[Speaker ID] = [Complete Audio Catalog].[Speaker ID]
WHERE ((([Complete Audio Catalog].[Print Label])=Yes));


Thanks,
AB
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9800171
This:
  format([Complete Audio Catalog].Date,"mmmm dd, yyyy") as CADDate
  is what I gave you...

This:
  Format([Complete Audio Catalog].Date,"mmmm dd"", ""yyyy")
  is what is in your last example...

see any differences in the format parameter "mmmm dd, yyyy"
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:abnc
ID: 9800448
This is what you gave me:

  format([Complete Audio Catalog].Date,"mmmm dd, yyyy") as CADDate


This is what Access does to it after the query was saved. It automatically changed it to this:

  Format([Complete Audio Catalog].Date,"mmmm dd"", ""yyyy")

I typed it in exactly as you stated.
0
 

Author Comment

by:abnc
ID: 9800466
Got to leave the office for now. Will check back on this later.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9800480
It think the word DATE is a function and is compensating

Format([Complete Audio Catalog].[Date],'mmmm dd, yyyy')

try using single quotes and bracketing that Function/Field name...
0
 

Author Comment

by:abnc
ID: 9801463
I tried the last suggestion and even decided to change the name of the field. Still no successful results. Same "#error" information placed in field.

Here is what Access does to it after I save it:

SELECT [Complete Audio Catalog].Title, Speakers.[Last Name], Speakers.[First Name], Format([Complete Audio Catalog].[EventDate],'mmmm dd", "yyyy') AS CADDate, [Service Types].Description, [Complete Audio Catalog].[Print Label]
FROM Speakers INNER JOIN ([Service Types] INNER JOIN [Complete Audio Catalog] ON [Service Types].[Service Type] = [Complete Audio Catalog].[Service Type]) ON Speakers.[Speaker ID] = [Complete Audio Catalog].[Speaker ID]
WHERE ((([Complete Audio Catalog].[Print Label])=Yes));

Perhaps I am overlooking something here. I appreciate your responses anyway.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9803470
Drop the comma from the format parameter and return to double quotes,  worst case you won't see a comma in the date output.
0
 

Author Comment

by:abnc
ID: 9803683
Dropping the the comma and returning to double quotes didn't make a difference. Same "#error" in the field result.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9803718
Do any eventdate fields have null values in them??
0
 

Author Comment

by:abnc
ID: 9803753
Not in any of the records that the query returns. There are some in the table that the query is based on.

However, I did create a new table with just a couple of records; no null fields. Same result with the error when trying to format the date.
0
 

Author Comment

by:abnc
ID: 9812013
Kept tinkering around and finally got it to work. Here's the SQL View of the Access Query:

SELECT [Complete Audio Catalog].Title, Speakers.[Last Name], Speakers.[First Name], Format([Complete Audio Catalog].[EventDate],"mmmm d"", ""yyyy") AS EventDate1, [Service Types].Description, [Complete Audio Catalog].[Print Label]
FROM Speakers INNER JOIN ([Service Types] INNER JOIN [Complete Audio Catalog] ON [Service Types].[Service Type] = [Complete Audio Catalog].[Service Type]) ON Speakers.[Speaker ID] = [Complete Audio Catalog].[Speaker ID]
WHERE ((([Complete Audio Catalog].[Print Label])=Yes));


Don't know exactly what made the difference, but am granting points to jadedata since he provided a lot of help.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9812047
That format function is still misbehaving!
  There should only be double quotes at the beginning and the end of (mmmm dd, yyyy).
it's starting to piss me off, and it isn't even my project...

Try a couple of these replacement strings
  "mm dd yyyy"
  "yyyymmdd"
  "dd mmmm, yyyy"

see if the above cause the same [phase splitting] behaviour that first one is displaying...

0
 
LVL 28

Expert Comment

by:TextReport
ID: 9812101
I still believe the Import/Export Specification is a lot easier. Cheers, Andrew
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

758 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

20 Experts available now in Live!

Get 1:1 Help Now