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?

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



"JimBurke via AccessMonster.com" <u49905@uwe> wrote in message
news:93dd1035e051c@uwe...
> I've never exported to XML, so I'm not sure if there's a way to tell it
> what
> to do if the value of the field is null - obviously it's using some
> default
> behavior. What do you want to appear in the XML file if there is no value
> for
> date? is the problem the default value it's putting in, the <DateRemoved>
> tag,
> or both?
>
> Are the dates stored in the table as Date values or as Text values? You
> mentioned that they're in the table in short format, but I'm guessing that
> they are stored in date format and simply displayed in short format using
> Access Formats. How do you want the date formatted in the XML? Do you want
> something like:
>
> <EndDate>2010-02-16</EndDate>
>
> What exactly are your requirements for what you need in the XML file for
> those dates?
>
>
> Jennifer Robertson wrote:
>>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?
>
> --
> Jim Burke
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200903/1
>

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



"JimBurke via AccessMonster.com" <u49905@uwe> wrote in message
news:93ddf8ad56029@uwe...
>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.
>
> Jennifer Robertson wrote:
>>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?
>
> --
> Jim Burke
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200903/1
>

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.

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.