Using Date Form Field data in Outputto Filename (a date to string chanbge needed)?

[Rob Oldfield]

Two ways round it...

Use the format command to ensure that the date is in 01/01/2005 format
before you start using the mid and left functions.

Use the Year, Month and Day functions to return just an integer value from a
date, and build your string from that.

 

[rgrantz]

I'd like to put the text in a couple date fields in a form that I use as the
report launcher to be put in the file name as well.

Ex:

DoCmd.OutputTo acReport, "rptDailyReport", acFormatRTF,
"c:\Whatever\SubWhatever\AccessReport\Snapshots\rptDailyReport" &
Forms!frmInspectionReportDates![StartDate] & "TO" &
Forms!frmInspectionReportDates![EndDate] & ".rtf"

The problem appears to be the "/" used in the date field. I have tried
using Mid to make filenames, which works, expect when the date is using
single digits for month and day (ie. Mid([StartDate],1,2 will return 10 when
it's 10/1/04, but 1/ when it's 1/1/04)

How can I either turn the date field into a string that's acceptable to
concatenate to a filename, or parse it somehow to eradicate the "/" or
replace them with underscores or something? They need to be date formatted
on the form, as they are used as the criteria for the report's date range.

Thanks for any help

 

[John Spencer (MVP)]

Add one more, use the format function to force the date into a useable format.

Format(someDate,"mm-dd-yyyy")
Format(SomeDate,"yyyymmdd")
Format(SomeDate,"mmm dd, yyyy")
....

Two ways round it...

Use the format command to ensure that the date is in 01/01/2005 format
before you start using the mid and left functions.

Use the Year, Month and Day functions to return just an integer value from a
date, and build your string from that.

I'd like to put the text in a couple date fields in a form that I use as the
report launcher to be put in the file name as well.

Ex:

DoCmd.OutputTo acReport, "rptDailyReport", acFormatRTF,
"c:\Whatever\SubWhatever\AccessReport\Snapshots\rptDailyReport" &
Forms!frmInspectionReportDates![StartDate] & "TO" &
Forms!frmInspectionReportDates![EndDate] & ".rtf"

The problem appears to be the "/" used in the date field. I have tried
using Mid to make filenames, which works, expect when the date is using
single digits for month and day (ie. Mid([StartDate],1,2 will return 10 when
it's 10/1/04, but 1/ when it's 1/1/04)

How can I either turn the date field into a string that's acceptable to
concatenate to a filename, or parse it somehow to eradicate the "/" or
replace them with underscores or something? They need to be date formatted
on the form, as they are used as the criteria for the report's date range.

Thanks for any help

 

[Rob Oldfield]

Quite right. A great deal easier than my original idea.

Add one more, use the format function to force the date into a useable format.

Format(someDate,"mm-dd-yyyy")
Format(SomeDate,"yyyymmdd")
Format(SomeDate,"mmm dd, yyyy")
...

Two ways round it...

Use the format command to ensure that the date is in 01/01/2005 format
before you start using the mid and left functions.

Use the Year, Month and Day functions to return just an integer value from a
date, and build your string from that.

I'd like to put the text in a couple date fields in a form that I use

as
the

report launcher to be put in the file name as well.

Ex:

DoCmd.OutputTo acReport, "rptDailyReport", acFormatRTF,
"c:\Whatever\SubWhatever\AccessReport\Snapshots\rptDailyReport" &
Forms!frmInspectionReportDates![StartDate] & "TO" &
Forms!frmInspectionReportDates![EndDate] & ".rtf"

The problem appears to be the "/" used in the date field. I have tried
using Mid to make filenames, which works, expect when the date is using
single digits for month and day (ie. Mid([StartDate],1,2 will return

10
when

it's 10/1/04, but 1/ when it's 1/1/04)

How can I either turn the date field into a string that's acceptable to
concatenate to a filename, or parse it somehow to eradicate the "/" or
replace them with underscores or something? They need to be date formatted
on the form, as they are used as the criteria for the report's date range.

Thanks for any help