Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 97: Exporting Date Fields to CSV Text File

Posted on 2003-11-21
21
Medium Priority
?
565 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
[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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1600 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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

670 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