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

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!!!
 
K

Ken Snell MVP

Use an expression for the Filename argument:

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

Include the quotes and = sign.
 
K

KKennedy

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?
 
K

KKennedy

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?
 
K

KKennedy

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...
 
K

KKennedy

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...
 
K

Ken Snell MVP

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...
 
K

Ken Snell MVP

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...
 
K

KKennedy

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...
 
K

KKennedy

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Export table to txt file with a variable filename 0
Transfer text with count 3
Date in file name 4
export a simple text file 1
macro with new file name 1
naming export file name 1
Export a Table and rename the file 1
Transfer text delimited with date in file name using a macro 3

Top