Excel Export

  • Thread starter Thread starter Sandy H
  • Start date Start date
S

Sandy H

Hi
This is a repost because I still haven't worked out the problem.

I have a report that I export to excel with code from a form. One of the
fields in the
query is a text field that is formatted as 00/0000;;_ with an input mask.
When this query is
exported the value appears as it should. But when I export the report, the
value changes
to a five digit number commencing with 38.

The underlying field is a text field, not a date field. On the report I use
an Input mask to
format it (00/0000;;_). I have also tried using format @@\/@@@@ and that
doesn't
work either.

Any help would be appreciated.

Sandy
 
Perhaps you could just set your input mask's second area to 0, so that the /
is stored with the text?
 
Thanks for the idea. This is very frustrating but still no luck.
The table stores the value as Text with an input string of: 00/0000;;_
A query outputs the value as dd/mmmm (no formatting is used here)
A report displays the value as ddmmmm even though the underlying query
displays the value correctly. If I format the report field or put an input
mask on it to display it correctly, I get the wrong value when I export it
to Excel. Without formatting the report field, the value exports properly
but with no separator.

Any other ideas?

Sandy
 
If you're doing anything different here from your original post, it's not
evident to me.
Input Masks only affect data as it is being entered.
The InputMask for your field should be 00/0000;0;_
This will store NEW values with the / embedded in the text.
However, it will have no effect on existing values. You can add the / to
the stored values using an update query. Once it is stored with the / in
place, you should be able to display or export it without further ado.
BTW, I wouldn't expect changes to the InputMask in the report to have any
effect, as no data is input that way.

HTH
 
This may be nothing to do with how Access is dealing with the data but how
Excel is dealing with it. If the value being passed to Excel is, for the
sake of argument, 31/2005 the likelihood is that Excel will take this to be
an arithmetic calculation, 31 divided by 2005. You may need to force Excel
into believing that what is being passed is text and not a number. Excel
prefers numbers, anything that can be taken to be a number may well be used
as a number.

I could be wrong, but it might be worth investigating.

Steve
 
Back
Top