Application.ExportXML exports dates in the wrong format or the 1899 date if date field is empty

[Jennifer Robertson]

I have a set of tables that I'm exporting into one xml file. That part
works.

What is odd is the dates. In the tables all the dates are in short format. A
date field may also be empty.

The xml shows a date/time format like so:
<EndDate>2010-02-16T00:00:00</EndDate>

and if there is no date it adds a default date like so:

<DateRemoved>1899-12-30T00:00:00</DateRemoved>

Any idea why is it doing this? And how I can resolve it?

 

[Jennifer Robertson]

Hi Jim,

If there is no value in the field, I want it to look like so:

<DateRemoved></DateRemoved>

The fields are date fields in Access. In access if there is no value in the
field, it is left blank.

I want the dates in the XML file to be in a short format which is compatible
with Access.

The data is being exported into an xml file, which is then imported into an
Adobe form. The user will modify the data, send the form back, form data is
exported to xml, the xml is then imported back into Access to update the
tables. This process is all automated using VBA.

Using this process, date fields that were originally blank in the tables now
have the 1899 date in them after the updated data is imported.

Jen

 

[Jennifer Robertson]

I have 9 tables each with 2-3 date fields, but I will try your suggestion.

I'm also trying to figure out how to transform the xml file as it is
exported. Not having much luck with that either! I've read up on xslt and
such, but not much is sinking in.

Regards,

Jen

I would create a query based on the table and use that as the export
source.
Put all the columns you need in the query and for the date field in
question,
rather than having that column name in the query, try this:

DateRemoved2: IIf(IsNull(DateRemoved),"",Format(DateRemoved,"Short Date"))

I don't know how the export will handle the "" value if the date field is
null - it may or may not work. Same goes for once the value is brought
into
Adobe. But the format statement should work to get the field written in
short
date format. If "" doesn't work you could try to specify " ", but again I
don't know what that would do once you're in Adobe.

Do you care what the field name is for the date field? If you need that
field
name to stay the same, the only way this would work would be to use a 2nd
query that simply uses the first query as it's data source and then use
this
for the date field in question in the 2nd query:

DateRemoved: DateRemoved2

Then use the 2nd query as the export source rather than the 1st query.
Access
doesn't allow you to create a new column that has the same name as the
original field it is referencing. Hope this helps.

Hi Jim,

If there is no value in the field, I want it to look like so:

<DateRemoved></DateRemoved>

The fields are date fields in Access. In access if there is no value in
the
field, it is left blank.

I want the dates in the XML file to be in a short format which is
compatible
with Access.

The data is being exported into an xml file, which is then imported into
an
Adobe form. The user will modify the data, send the form back, form data
is
exported to xml, the xml is then imported back into Access to update the
tables. This process is all automated using VBA.

Using this process, date fields that were originally blank in the tables
now
have the 1899 date in them after the updated data is imported.

Jen

I've never exported to XML, so I'm not sure if there's a way to tell it
what

[quoted text clipped - 32 lines]

Any idea why is it doing this? And how I can resolve it?

 

[Jennifer Robertson]

Update:

1. Microsoft Access automatically exports date data as a DateTime.
2. If the field is empty, the field is not included in the XML file.

 

[Jennifer Robertson]

I couldn't figure out how to do it via xslt.

To resolve this I saved the xml file with a txt extension, used .Execute
FindText methods to edit the file to remove all the T00:00:00, then removed
all the 1899-12-30 dates.

This seems to have worked.