I need to export a query (to excel format) for use in uploading data to a
system. That system requires specific column headers on the upload file.

One of the column headers needs to be: File # (no quotes)

The data in the query field is File #: Right([datafield],6). The displayed
header is fine, but when exporting, the # symbol is replaced with a period.

I tried converting it in sql using cstr(), however that resulted in a column
header of "File ." (including the quotes).

Does anyone have any suggestions on how to remedy this?

I have tried putting & and [] around the character as suggested by some
posts, however that was not successful in the export.


cm

This sounds familiar ... have you already asked this question?

If you don't get an immediate response in these newsgroups, remember that
folks here are volunteers. It may be that:
* nobody's home
* nobody has time
* nobody knows

If you must have an immediate response, consider hiring someone (or paying a
support site fee).

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"cm" <(E-Mail Removed)> wrote in message
news:AF9A173E-321E-4F02-9782-(E-Mail Removed)...
>I need to export a query (to excel format) for use in uploading data to a
> system. That system requires specific column headers on the upload file.
>
> One of the column headers needs to be: File # (no quotes)
>
> The data in the query field is File #: Right([datafield],6). The displayed
> header is fine, but when exporting, the # symbol is replaced with a
> period.
>
> I tried converting it in sql using cstr(), however that resulted in a
> column
> header of "File ." (including the quotes).
>
> Does anyone have any suggestions on how to remedy this?
>
> I have tried putting & and [] around the character as suggested by some
> posts, however that was not successful in the export.
>
>
> cm

On Feb 4, 12:58*pm, cm <c...@discussions.microsoft.com> wrote:
> I need to export a query (to excel format) for use in uploading data to a
> system. That system requires specific column headers on the upload file.
>
> One of the column headers needs to be: File # (no quotes)
>
> The data in the query field is File #: Right([datafield],6). The displayed
> header is fine, but when exporting, the # symbol is replaced with a period.

Can't tell you why, but this seems to work:
Expr1: "File " & Chr$(35) & [DHSCaseNo]

Chr$(35) returns the ASCII code for a colon.

That works for data within the field, however I only need it in the column
heading. I can't get it to work for that purpose. Any other thoughts?


--

cm


"(E-Mail Removed)" wrote:

> On Feb 4, 12:58 pm, cm <c...@discussions.microsoft.com> wrote:
> > I need to export a query (to excel format) for use in uploading data to a
> > system. That system requires specific column headers on the upload file.
> >
> > One of the column headers needs to be: File # (no quotes)
> >
> > The data in the query field is File #: Right([datafield],6). The displayed
> > header is fine, but when exporting, the # symbol is replaced with a period.
>
> Can't tell you why, but this seems to work:
> Expr1: "File " & Chr$(35) & [DHSCaseNo]
>
> Chr$(35) returns the ASCII code for a colon.
> .
>

[patcash]

I ran into the same problem today. Try checking the Save Formatted box.

Excel can open a CSV file automatically. If you have to, you can create the headers using the old BASIC OPEN FOR OUTPUT and PRINT commands and then append a CSV created with Docmd.TransferText