Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 568
  • Last Modified:

Access 97: Exporting Date Fields to CSV Text File

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
abnc
Asked:
abnc
  • 11
  • 8
  • 2
1 Solution
 
jadedataMS Access Systems CreatorCommented:
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
 
jadedataMS Access Systems CreatorCommented:
sorry, wrong question.... (right answer though)
0
 
TextReportCommented:
Us and Import/Export Specification to "define" your import options. Cheers, Andrew
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
jadedataMS Access Systems CreatorCommented:
Hey abnc!
  THIS ONE is for your question!!

  use:
   cstr(format([datevalue], "mmmm dd, yyyy")) to:
   format as specified
   convert to string value
0
 
abncAuthor Commented:
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
 
jadedataMS Access Systems CreatorCommented:
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
 
jadedataMS Access Systems CreatorCommented:
 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
 
jadedataMS Access Systems CreatorCommented:
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
 
abncAuthor Commented:
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
 
jadedataMS Access Systems CreatorCommented:
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
 
abncAuthor Commented:
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
 
abncAuthor Commented:
Got to leave the office for now. Will check back on this later.
0
 
jadedataMS Access Systems CreatorCommented:
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
 
abncAuthor Commented:
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
 
jadedataMS Access Systems CreatorCommented:
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
 
abncAuthor Commented:
Dropping the the comma and returning to double quotes didn't make a difference. Same "#error" in the field result.
0
 
jadedataMS Access Systems CreatorCommented:
Do any eventdate fields have null values in them??
0
 
abncAuthor Commented:
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
 
abncAuthor Commented:
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
 
jadedataMS Access Systems CreatorCommented:
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
 
TextReportCommented:
I still believe the Import/Export Specification is a lot easier. Cheers, Andrew
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 11
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now