Export a table to text file with file name depending on certain da

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

Hi,

Any assistance greatly appreciated.

When the macro runs, I have a form where the user enters a date...that date
I want to be part of the text file name thats created by the transfer text
action. I have a macro with a Transfer Text action that exports an Access 07
table to a text file)

So instead of having the filename stay the same, I want the filename to
consist of 2 parts; a static string of characters plus the date that's
entered in the form.

For example, the text filename might be : 999904102009.txt assuming the
user enters into the form field the date "04102009" while another time it
could be 999904242009.txt assuming the user enters into the form field the
date "04242009"

How do I get a dynamic file name??

Any suggestions...Thank you!!!
 
Use an expression for the Filename argument:

="C:\MyFolder\MyFile" & Forms!NameOfForm!NameOfControlWithDate & ".txt"

Include the quotes and = sign.
 
I used this suggestion in an Access 2000 macro, but the
Forms!FormName!ControlName! ends up with brackets when I save it. The form
accesses a table that is built with a query that returns the date the
filename needs. How do I get rid of the brackets, or is there another
solution?
 
I used this suggestion in an Access 2000 macro, but the
Forms!FormName!ControlName! ends up with brackets when I save it. The form
accesses a table that is built with a query that returns the date the
filename needs. How do I get rid of the brackets, or is there another
solution?
 
I'm ok with the brackets, I have the form open when I run the macro, the form
is unbound and so is the form field, but I get a type mismatch error that I
can trace to the presence of the form parameters. I modified the form field
to convert the date in MYYYY format to a character string, but that did not
work. =CStr(DatePart('M',DateSerial(Year(Date()),Month(Date()),0)) &
DatePart('YYYY',DateSerial(Year(Date()),Month(Date()),0)))
--
KKennedy


Ken Snell MVP said:
The brackets are normal syntax for ACCESS, so long as it looks like this
(note: no trailling ! is to be there):

[Forms]![FormName]![ControlName]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Click to expand...
 
I'm ok with the brackets, I have the form open when I run the macro, the form
is unbound and so is the form field, but I get a type mismatch error that I
can trace to the presence of the form parameters. I modified the form field
to convert the date in MYYYY format to a character string, but that did not
work. =CStr(DatePart('M',DateSerial(Year(Date()),Month(Date()),0)) &
DatePart('YYYY',DateSerial(Year(Date()),Month(Date()),0)))
--
KKennedy


Ken Snell MVP said:
The brackets are normal syntax for ACCESS, so long as it looks like this
(note: no trailling ! is to be there):

[Forms]![FormName]![ControlName]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Click to expand...
 
If the value you want to use is a date value, you can use the Format
function to change it to a desired "string" format:

="C:\MyFolder\MyFile" & Format(Forms!NameOfForm!NameOfControlWithDate,
"MYYYY") & ".txt"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




KKennedy said:
I'm ok with the brackets, I have the form open when I run the macro, the
form
is unbound and so is the form field, but I get a type mismatch error that
I
can trace to the presence of the form parameters. I modified the form
field
to convert the date in MYYYY format to a character string, but that did
not
work. =CStr(DatePart('M',DateSerial(Year(Date()),Month(Date()),0)) &
DatePart('YYYY',DateSerial(Year(Date()),Month(Date()),0)))
--
KKennedy


Ken Snell MVP said:
The brackets are normal syntax for ACCESS, so long as it looks like this
(note: no trailling ! is to be there):

[Forms]![FormName]![ControlName]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


KKennedy said:
I used this suggestion in an Access 2000 macro, but the
Forms!FormName!ControlName! ends up with brackets when I save it. The
form
accesses a table that is built with a query that returns the date the
filename needs. How do I get rid of the brackets, or is there another
solution?
--
KKennedy


:

Use an expression for the Filename argument:

="C:\MyFolder\MyFile" & Forms!NameOfForm!NameOfControlWithDate &
".txt"

Include the quotes and = sign.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hi,

Any assistance greatly appreciated.

When the macro runs, I have a form where the user enters a
date...that
date
I want to be part of the text file name thats created by the
transfer
text
action. I have a macro with a Transfer Text action that exports an
Access
07
table to a text file)

So instead of having the filename stay the same, I want the filename
to
consist of 2 parts; a static string of characters plus the date
that's
entered in the form.

For example, the text filename might be : 999904102009.txt assuming
the
user enters into the form field the date "04102009" while another
time
it
could be 999904242009.txt assuming the user enters into the form
field
the
date "04242009"

How do I get a dynamic file name??

Any suggestions...Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
If the value you want to use is a date value, you can use the Format
function to change it to a desired "string" format:

="C:\MyFolder\MyFile" & Format(Forms!NameOfForm!NameOfControlWithDate,
"MYYYY") & ".txt"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




KKennedy said:
I'm ok with the brackets, I have the form open when I run the macro, the
form
is unbound and so is the form field, but I get a type mismatch error that
I
can trace to the presence of the form parameters. I modified the form
field
to convert the date in MYYYY format to a character string, but that did
not
work. =CStr(DatePart('M',DateSerial(Year(Date()),Month(Date()),0)) &
DatePart('YYYY',DateSerial(Year(Date()),Month(Date()),0)))
--
KKennedy


Ken Snell MVP said:
The brackets are normal syntax for ACCESS, so long as it looks like this
(note: no trailling ! is to be there):

[Forms]![FormName]![ControlName]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


KKennedy said:
I used this suggestion in an Access 2000 macro, but the
Forms!FormName!ControlName! ends up with brackets when I save it. The
form
accesses a table that is built with a query that returns the date the
filename needs. How do I get rid of the brackets, or is there another
solution?
--
KKennedy


:

Use an expression for the Filename argument:

="C:\MyFolder\MyFile" & Forms!NameOfForm!NameOfControlWithDate &
".txt"

Include the quotes and = sign.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hi,

Any assistance greatly appreciated.

When the macro runs, I have a form where the user enters a
date...that
date
I want to be part of the text file name thats created by the
transfer
text
action. I have a macro with a Transfer Text action that exports an
Access
07
table to a text file)

So instead of having the filename stay the same, I want the filename
to
consist of 2 parts; a static string of characters plus the date
that's
entered in the form.

For example, the text filename might be : 999904102009.txt assuming
the
user enters into the form field the date "04102009" while another
time
it
could be 999904242009.txt assuming the user enters into the form
field
the
date "04242009"

How do I get a dynamic file name??

Any suggestions...Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Yes, that worked: removing the CStr and replacing it with the format command.
Thanks.
--
KKennedy


Ken Snell MVP said:
If the value you want to use is a date value, you can use the Format
function to change it to a desired "string" format:

="C:\MyFolder\MyFile" & Format(Forms!NameOfForm!NameOfControlWithDate,
"MYYYY") & ".txt"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




KKennedy said:
I'm ok with the brackets, I have the form open when I run the macro, the
form
is unbound and so is the form field, but I get a type mismatch error that
I
can trace to the presence of the form parameters. I modified the form
field
to convert the date in MYYYY format to a character string, but that did
not
work. =CStr(DatePart('M',DateSerial(Year(Date()),Month(Date()),0)) &
DatePart('YYYY',DateSerial(Year(Date()),Month(Date()),0)))
--
KKennedy


Ken Snell MVP said:
The brackets are normal syntax for ACCESS, so long as it looks like this
(note: no trailling ! is to be there):

[Forms]![FormName]![ControlName]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I used this suggestion in an Access 2000 macro, but the
Forms!FormName!ControlName! ends up with brackets when I save it. The
form
accesses a table that is built with a query that returns the date the
filename needs. How do I get rid of the brackets, or is there another
solution?
--
KKennedy


:

Use an expression for the Filename argument:

="C:\MyFolder\MyFile" & Forms!NameOfForm!NameOfControlWithDate &
".txt"

Include the quotes and = sign.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hi,

Any assistance greatly appreciated.

When the macro runs, I have a form where the user enters a
date...that
date
I want to be part of the text file name thats created by the
transfer
text
action. I have a macro with a Transfer Text action that exports an
Access
07
table to a text file)

So instead of having the filename stay the same, I want the filename
to
consist of 2 parts; a static string of characters plus the date
that's
entered in the form.

For example, the text filename might be : 999904102009.txt assuming
the
user enters into the form field the date "04102009" while another
time
it
could be 999904242009.txt assuming the user enters into the form
field
the
date "04242009"

How do I get a dynamic file name??

Any suggestions...Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Yes, that worked: removing the CStr and replacing it with the format command.
Thanks.
--
KKennedy


Ken Snell MVP said:
If the value you want to use is a date value, you can use the Format
function to change it to a desired "string" format:

="C:\MyFolder\MyFile" & Format(Forms!NameOfForm!NameOfControlWithDate,
"MYYYY") & ".txt"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




KKennedy said:
I'm ok with the brackets, I have the form open when I run the macro, the
form
is unbound and so is the form field, but I get a type mismatch error that
I
can trace to the presence of the form parameters. I modified the form
field
to convert the date in MYYYY format to a character string, but that did
not
work. =CStr(DatePart('M',DateSerial(Year(Date()),Month(Date()),0)) &
DatePart('YYYY',DateSerial(Year(Date()),Month(Date()),0)))
--
KKennedy


Ken Snell MVP said:
The brackets are normal syntax for ACCESS, so long as it looks like this
(note: no trailling ! is to be there):

[Forms]![FormName]![ControlName]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I used this suggestion in an Access 2000 macro, but the
Forms!FormName!ControlName! ends up with brackets when I save it. The
form
accesses a table that is built with a query that returns the date the
filename needs. How do I get rid of the brackets, or is there another
solution?
--
KKennedy


:

Use an expression for the Filename argument:

="C:\MyFolder\MyFile" & Forms!NameOfForm!NameOfControlWithDate &
".txt"

Include the quotes and = sign.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hi,

Any assistance greatly appreciated.

When the macro runs, I have a form where the user enters a
date...that
date
I want to be part of the text file name thats created by the
transfer
text
action. I have a macro with a Transfer Text action that exports an
Access
07
table to a text file)

So instead of having the filename stay the same, I want the filename
to
consist of 2 parts; a static string of characters plus the date
that's
entered in the form.

For example, the text filename might be : 999904102009.txt assuming
the
user enters into the form field the date "04102009" while another
time
it
could be 999904242009.txt assuming the user enters into the form
field
the
date "04242009"

How do I get a dynamic file name??

Any suggestions...Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top