Export to Excel problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

General info:
MS Access 2003. My database has Access 2000 format.
In my Access database I use ODBC sources of data (SQL server).

My problem description:
When I use SendObject command to email data in MS Excel format, text fields
with Null values become empty in MSExcel (they do not contain any information
but they are not Null - that I checked with IsNull() function).

My colleagues then try to import the emailed MS Excel spreadsheet to their
Access database and they cannot, because the text fields do not allow empty
values.

I tried TransferSpreadsheet command to save data to XLS format and it works
great: null values are saved as null values (IsNull() function in Excel
confirms that).
But TransferSpreadsheet command is not appropriate for me, because I need to
email files without saving them to harddrive or whatever.

The question:
Is there any way out?

Thank you in advance!!!
 
Create a query that has a calculated field in place of the text field so
that you replace Null values with empty string values:
NoNullField: Nz([TextField], "")

Then use SendObject with this query.
 
Thank you, Ken!
I think, my problem explanation was not very good...

I don't need zero values. I do need Null values.

I tried:
NullField: IIf([TextField]="";Null;[TextField])

But in Excel the saved values are still not null.






Ken Snell (MVP) said:
Create a query that has a calculated field in place of the text field so
that you replace Null values with empty string values:
NoNullField: Nz([TextField], "")

Then use SendObject with this query.
--

Ken Snell
<MS ACCESS MVP>

Mike said:
General info:
MS Access 2003. My database has Access 2000 format.
In my Access database I use ODBC sources of data (SQL server).

My problem description:
When I use SendObject command to email data in MS Excel format, text
fields
with Null values become empty in MSExcel (they do not contain any
information
but they are not Null - that I checked with IsNull() function).

My colleagues then try to import the emailed MS Excel spreadsheet to their
Access database and they cannot, because the text fields do not allow
empty
values.

I tried TransferSpreadsheet command to save data to XLS format and it
works
great: null values are saved as null values (IsNull() function in Excel
confirms that).
But TransferSpreadsheet command is not appropriate for me, because I need
to
email files without saving them to harddrive or whatever.

The question:
Is there any way out?

Thank you in advance!!!
Click to expand...
Click to expand...
 
Hi Mike and Ken,

I guess that the problem is associated with different Excel formats and/or
export routines in Access.

When I tried it just now SendObject ... acFormatXLS created an Excel 5.0/95
workbook (allee samee OutputTo). TransferSpreadsheet uses different export
code and defaults to the current format (Excel 8.0 to 11).


Mike said:
Thank you, Ken!
I think, my problem explanation was not very good...

I don't need zero values. I do need Null values.

I tried:
NullField: IIf([TextField]="";Null;[TextField])

But in Excel the saved values are still not null.






Ken Snell (MVP) said:
Create a query that has a calculated field in place of the text field so
that you replace Null values with empty string values:
NoNullField: Nz([TextField], "")

Then use SendObject with this query.
--

Ken Snell
<MS ACCESS MVP>

Mike said:
General info:
MS Access 2003. My database has Access 2000 format.
In my Access database I use ODBC sources of data (SQL server).

My problem description:
When I use SendObject command to email data in MS Excel format, text
fields
with Null values become empty in MSExcel (they do not contain any
information
but they are not Null - that I checked with IsNull() function).

My colleagues then try to import the emailed MS Excel spreadsheet to
their
Access database and they cannot, because the text fields do not allow
empty
values.

I tried TransferSpreadsheet command to save data to XLS format and it
works
great: null values are saved as null values (IsNull() function in Excel
confirms that).
But TransferSpreadsheet command is not appropriate for me, because I
need
to
email files without saving them to harddrive or whatever.

The question:
Is there any way out?

Thank you in advance!!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top