how to strip time from date field on export?

M

magmike

When I export a query that has a date field (which automatically puts the
date of record entry by using =date() ), it also includes the time. I have
all the fields formatted with "short date". When I veiw the table, it
doesn't show the time either.

How can I strip this data out before or during export?

Mike
 
J

Jeff Boyce

Mike

What you see displayed is governed by formatting. What is stored in the
table is a value that Access translates to date AND time. If you only want
to see the date portion, try formatting the query field.
 
M

magmike

I did. However, the problem arose when exporting the data for merge use in
MS Word. No matter what I did, although you only saw the date inside Access,
when the query was exported as a delimitted file, it included the decimal
part of the data (the time).

I did discover however, that when linking the merge document to an access
table (it will not link to a query) that only the date shows. So I exported
the query, and then imported it as a new table, and used that new table as
the source for the Word document, and now all is well.

Mike
 
J

Jeff Boyce

So, another approach would be to use the integer portion of the date only?

Glad you found a way.

Jeff
 
V

Van T. Dinh

You shouldn't have to do that many steps. Try one of the
following in the Query:

1. Create a Calculated Field in your Query like:

DateOnly: DateValue([DateTimeField])

and use this rather than your DateTimeField.

2. Or another Calculated Field:

DateText: Format([YourDateField], "dd/mm/yyyy")

or whatever the formatting string you normally use.

You can then use this "date" in your Word MailMerge doc.

Note that Word MailMerge can use Access Select Queries
*except* Parametrised Queries as the DataSource for the
MailMerge. It sounds like you were trying to use a
Parametrised Query.

HTH
Van T. Dinh
MVP (Access)
 
M

magmike

Thanks for the tips, but it didn't work! Someone else gave me a tip on how
to actually strip the time out of the raw data, but that didn't seem to work
either. I'm not sure what's going on. If you want to play with it yourself,
you can download it from http://www.netterweb.com/ffa.zip. I'll leave it on
the server for a couple of days.

Thanks for your help,
Mike

Van T. Dinh said:
You shouldn't have to do that many steps. Try one of the
following in the Query:

1. Create a Calculated Field in your Query like:

DateOnly: DateValue([DateTimeField])

and use this rather than your DateTimeField.

2. Or another Calculated Field:

DateText: Format([YourDateField], "dd/mm/yyyy")

or whatever the formatting string you normally use.

You can then use this "date" in your Word MailMerge doc.

Note that Word MailMerge can use Access Select Queries
*except* Parametrised Queries as the DataSource for the
MailMerge. It sounds like you were trying to use a
Parametrised Query.

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
I did. However, the problem arose when exporting the data for merge use in
MS Word. No matter what I did, although you only saw the date inside Access,
when the query was exported as a delimitted file, it included the decimal
part of the data (the time).

I did discover however, that when linking the merge document to an access
table (it will not link to a query) that only the date shows. So I exported
the query, and then imported it as a new table, and used that new table as
the source for the Word document, and now all is well.

Mike
Click to expand...
Click to expand...
 
V

Van T. Dinh

1. Your Field [Date] has zero time component, i.e. no time value. Thus you
don't need to strip it. If you show zero time on your MailMerge doc, it is
because of the format on the MergeField in your MailMerge doc. Right-click
on the MergeField and choose a suitable format.

2. If you are talking about the Field [Time] in your database, then don't
include it in your MailMerge doc.

3. Both "Date" & "Time" are bad choices for Field names as there are
inbuilt functions Date() and Time() and also Date statement and Time
statement. It can get very cofusing when you write VBA code. Suggest you
change the Field names if possible.

4. MailMerge doc can use normal SELECT Queries. I have never tried
Cross-Tab Query but I don't think MailMerge can use Cross-Tab Queries since
the number of Columns are not fixed in Cross-Tab Queries. Hence Cross-Tab
Queries do not appear in the DataSource selection list for MailMerge.
 
M

magmike

The use of the cross-tab is the only way I know how to show only one
instance of a duplicate record, since duplicates are allowed. I only need to
send one email to each email address, but the email address may be listed
multiple times.

Do you know a better query format for this purpose?

BTW - thanks for all your help. I am learning so much from you!

Mike

Van T. Dinh said:
1. Your Field [Date] has zero time component, i.e. no time value. Thus you
don't need to strip it. If you show zero time on your MailMerge doc, it is
because of the format on the MergeField in your MailMerge doc. Right-click
on the MergeField and choose a suitable format.

2. If you are talking about the Field [Time] in your database, then don't
include it in your MailMerge doc.

3. Both "Date" & "Time" are bad choices for Field names as there are
inbuilt functions Date() and Time() and also Date statement and Time
statement. It can get very cofusing when you write VBA code. Suggest you
change the Field names if possible.

4. MailMerge doc can use normal SELECT Queries. I have never tried
Cross-Tab Query but I don't think MailMerge can use Cross-Tab Queries since
the number of Columns are not fixed in Cross-Tab Queries. Hence Cross-Tab
Queries do not appear in the DataSource selection list for MailMerge.

--
HTH
Van T. Dinh
MVP (Access)





magmike said:
Thanks for the tips, but it didn't work! Someone else gave me a tip on how
to actually strip the time out of the raw data, but that didn't seem to work
either. I'm not sure what's going on. If you want to play with it yourself,
you can download it from http://www.netterweb.com/ffa.zip. I'll leave it on
the server for a couple of days.

Thanks for your help,
Mike
Click to expand...
Click to expand...
 
V

Van T. Dinh

Check Access / JET SQL Help on the DISTINCT keyword.

--
HTH
Van T. Dinh
MVP (Access)




magmike said:
The use of the cross-tab is the only way I know how to show only one
instance of a duplicate record, since duplicates are allowed. I only need to
send one email to each email address, but the email address may be listed
multiple times.

Do you know a better query format for this purpose?

BTW - thanks for all your help. I am learning so much from you!

Mike
Click to expand...
ut of the raw data, but that didn't seem to
 
Top