Outputting to a unique filename

[Aaron Howe]

I have a query which compiles certain data ready for
output via a macro to a CSV file. I recall in Excel it's
possible to autmoate some of the process of creating the
output file to include information from the file itself in
the filename - for example to use information from the
first two fields as the filename.

So what I would want to do in Access is to take the first
two fields of the query (say, client name and date) and
concatenate them into a filename. Example being:
Dummycompany110105.csv and then the next one could be
Dummycorp110105.csv and so on. At the moment I'm having
to rename each output file manually. Is this possible in
Access? If so, how would I code it?

 

[Douglas J. Steele]

strFileName = strClientName & Format(Date(), "ddmmyy") & ".csv"

 

[Aaron Howe]

Excellent, thanks Douglas. Just wondering though - how do
I get this into the macro I created, as I can't seem to
find that macro itself in the VB screen... does this mean
I can't use that functions alongside a macro?

 

[Aaron Howe]

It seems I may have made this more complicated than I
needed to. Searching around I found that this function
can be carried out in a macro also, but I couldn't seem to
get that to work so I turned to VBA.

Here's the code:

Dim strfilename As String
strfilename = "\\server\vol1\mci\sb\" & strCltNumber &
Format(Date(), "ddmmyy") & ".csv"
DoCmd.TransferText acExportDelim, , "qryTimesheet",
strfilename, 1
Application.FollowHyperlink strfilename

This returns the error 2465 "Microsoft Access can't find
the field 'Date' referred to in your expression."

To reiterate a bit more clearly, what I would like to
produce is a unique output file that would look something
like:

\\server\vol1\mci\sb\123456110105.csv

with the formatting I already had in my schema.ini:

[qryTimesheet.csv]
ColNameHeader=True
CharacterSet=OEM
Format=CSVDelimited
DateTimeFormat=dd/mm/yyyy

However when I get this running in Access my schema.ini is
ignored (I realise it's not specified in the code, but I
couldn't get it to accept it).

Is what I'm asking at all possible or have I just made a
simple matter worse?

 

[Douglas J. Steele]

Your references are probably messed up.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

It seems I may have made this more complicated than I
needed to. Searching around I found that this function
can be carried out in a macro also, but I couldn't seem to
get that to work so I turned to VBA.

Here's the code:

Dim strfilename As String
strfilename = "\\server\vol1\mci\sb\" & strCltNumber &
Format(Date(), "ddmmyy") & ".csv"
DoCmd.TransferText acExportDelim, , "qryTimesheet",
strfilename, 1
Application.FollowHyperlink strfilename

This returns the error 2465 "Microsoft Access can't find
the field 'Date' referred to in your expression."

To reiterate a bit more clearly, what I would like to
produce is a unique output file that would look something
like:

\\server\vol1\mci\sb\123456110105.csv

with the formatting I already had in my schema.ini:

[qryTimesheet.csv]
ColNameHeader=True
CharacterSet=OEM
Format=CSVDelimited
DateTimeFormat=dd/mm/yyyy

However when I get this running in Access my schema.ini is
ignored (I realise it's not specified in the code, but I
couldn't get it to accept it).

Is what I'm asking at all possible or have I just made a
simple matter worse?

-----Original Message-----
Excellent, thanks Douglas. Just wondering though - how do
I get this into the macro I created, as I can't seem to
find that macro itself in the VB screen... does this mean
I can't use that functions alongside a macro?
itself
.

 

[Aaron Howe]

Hi Doug, thanks for the reply

I tried the references but none of that worked, none were
missing and removing/adding them again didn't seem to make
a difference. I was however able to change it from Date()
to Now() and it had the same effect essentially. However
this leaves me with two further problems:

1. My schema.ini is being ignored and so the output date
format is useless to me (it's using dd/mm/yyyy hh:mm:ss
where I want dd/mm/yyyy alone)
2. It's not adding the string strCltNumber to the
filename, so each output file is the same. It doesn't
query that field, it just doesn't use it. Should I be
making a declaration for this elsewhere?

Would it make a difference that I'm attaching this to a
button?

-----Original Message-----
Your references are probably messed up.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://members.rogers.com/douglas.j.steele/AccessReference Errors.html


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

It seems I may have made this more complicated than I
needed to. Searching around I found that this function
can be carried out in a macro also, but I couldn't seem to
get that to work so I turned to VBA.

Here's the code:

Dim strfilename As String
strfilename = "\\server\vol1\mci\sb\" & strCltNumber &
Format(Date(), "ddmmyy") & ".csv"
DoCmd.TransferText acExportDelim, , "qryTimesheet",
strfilename, 1
Application.FollowHyperlink strfilename

This returns the error 2465 "Microsoft Access can't find
the field 'Date' referred to in your expression."

To reiterate a bit more clearly, what I would like to
produce is a unique output file that would look something
like:

\\server\vol1\mci\sb\123456110105.csv

with the formatting I already had in my schema.ini:

[qryTimesheet.csv]
ColNameHeader=True
CharacterSet=OEM
Format=CSVDelimited
DateTimeFormat=dd/mm/yyyy

However when I get this running in Access my schema.ini is
ignored (I realise it's not specified in the code, but I
couldn't get it to accept it).

Is what I'm asking at all possible or have I just made a
simple matter worse?

-----Original Message-----
Excellent, thanks Douglas. Just wondering though - how do
I get this into the macro I created, as I can't seem to
find that macro itself in the VB screen... does this mean
I can't use that functions alongside a macro?

-----Original Message-----
strFileName = strClientName & Format(Date(), "ddmmyy")
& ".csv"

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


in message
I have a query which compiles certain data ready for
output via a macro to a CSV file. I recall in Excel
it's
possible to autmoate some of the process of creating the
output file to include information from the file itself
in
the filename - for example to use information from the
first two fields as the filename.

So what I would want to do in Access is to take the
first
two fields of the query (say, client name and date) and
concatenate them into a filename. Example being:
Dummycompany110105.csv and then the next one could be
Dummycorp110105.csv and so on. At the moment I'm having
to rename each output file manually. Is this possible
in
Access? If so, how would I code it?


.

.

.

 

[Douglas J. Steele]

Answers in-line

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi Doug, thanks for the reply

I tried the references but none of that worked, none were
missing and removing/adding them again didn't seem to make
a difference. I was however able to change it from Date()
to Now() and it had the same effect essentially. However
this leaves me with two further problems:

If you can't find a problem with your references, then you may be using Date
as a variable or field name somewhere in your application. Date is a
reserved word, and you should never use it for your own purposes.

1. My schema.ini is being ignored and so the output date
format is useless to me (it's using dd/mm/yyyy hh:mm:ss
where I want dd/mm/yyyy alone)

Create a query and use the Format function (not the Format property) to
return the date in the format you want. Export the query, not the table.

2. It's not adding the string strCltNumber to the
filename, so each output file is the same. It doesn't
query that field, it just doesn't use it. Should I be
making a declaration for this elsewhere?

I don't understand what you mean by "it doesn't query that field". From
where do you expect the value for strCtlNumber to come?

Would it make a difference that I'm attaching this to a
button?

-----Original Message-----
Your references are probably messed up.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://members.rogers.com/douglas.j.steele/AccessReference Errors.html


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

It seems I may have made this more complicated than I
needed to. Searching around I found that this function
can be carried out in a macro also, but I couldn't seem to
get that to work so I turned to VBA.

Here's the code:

Dim strfilename As String
strfilename = "\\server\vol1\mci\sb\" & strCltNumber &
Format(Date(), "ddmmyy") & ".csv"
DoCmd.TransferText acExportDelim, , "qryTimesheet",
strfilename, 1
Application.FollowHyperlink strfilename

This returns the error 2465 "Microsoft Access can't find
the field 'Date' referred to in your expression."

To reiterate a bit more clearly, what I would like to
produce is a unique output file that would look something
like:

\\server\vol1\mci\sb\123456110105.csv

with the formatting I already had in my schema.ini:

[qryTimesheet.csv]
ColNameHeader=True
CharacterSet=OEM
Format=CSVDelimited
DateTimeFormat=dd/mm/yyyy

However when I get this running in Access my schema.ini is
ignored (I realise it's not specified in the code, but I
couldn't get it to accept it).

Is what I'm asking at all possible or have I just made a
simple matter worse?

-----Original Message-----
Excellent, thanks Douglas. Just wondering though - how
do
I get this into the macro I created, as I can't seem to
find that macro itself in the VB screen... does this mean
I can't use that functions alongside a macro?

-----Original Message-----
strFileName = strClientName & Format(Date(), "ddmmyy")
& ".csv"

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


in message
I have a query which compiles certain data ready for
output via a macro to a CSV file. I recall in Excel
it's
possible to autmoate some of the process of creating
the
output file to include information from the file
itself
in
the filename - for example to use information from the
first two fields as the filename.

So what I would want to do in Access is to take the
first
two fields of the query (say, client name and date) and
concatenate them into a filename. Example being:
Dummycompany110105.csv and then the next one could be
Dummycorp110105.csv and so on. At the moment I'm
having
to rename each output file manually. Is this possible
in
Access? If so, how would I code it?


.

.

.

 

[Aaron Howe]

I tried the format function and it worked perfectly, it's
something I previously had no awareness of I'm ashamed to
say!

WRT to the strCltNumber, that's a field in my query which
I'm looking to use in the filename itself (currently the
filename only outputs as a date - i.e. 120105.csv

And you're right, I was using a date expression... it has
gone now

-----Original Message-----
Answers in-line

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi Doug, thanks for the reply

I tried the references but none of that worked, none were
missing and removing/adding them again didn't seem to make
a difference. I was however able to change it from Date ()
to Now() and it had the same effect essentially. However
this leaves me with two further problems:

If you can't find a problem with your references, then you may be using Date
as a variable or field name somewhere in your application. Date is a
reserved word, and you should never use it for your own purposes.

1. My schema.ini is being ignored and so the output date
format is useless to me (it's using dd/mm/yyyy hh:mm:ss
where I want dd/mm/yyyy alone)

Create a query and use the Format function (not the Format property) to
return the date in the format you want. Export the query, not the table.

2. It's not adding the string strCltNumber to the
filename, so each output file is the same. It doesn't
query that field, it just doesn't use it. Should I be
making a declaration for this elsewhere?

I don't understand what you mean by "it doesn't query that field". From
where do you expect the value for strCtlNumber to come?

Would it make a difference that I'm attaching this to a
button?

-----Original Message-----
Your references are probably messed up.

This can be caused by differences in either the

location
or file version of

certain files between the machine where the application was developed, and
where it's being run (or the file missing completely

from
the target

machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't

selected
the "keep debug

window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need

the
reference(s) you

just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the

reference
you just added. If

that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you

go
back in)

For far more than you could ever want to know about

this
problem, check out
http://members.rogers.com/douglas.j.steele/AccessReference

Errors.html


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


"Aaron Howe" <[email protected]>

wrote
in message

It seems I may have made this more complicated than I
needed to. Searching around I found that this function
can be carried out in a macro also, but I couldn't

seem
to

get that to work so I turned to VBA.

Here's the code:

Dim strfilename As String
strfilename = "\\server\vol1\mci\sb\" & strCltNumber &
Format(Date(), "ddmmyy") & ".csv"
DoCmd.TransferText acExportDelim, , "qryTimesheet",
strfilename, 1
Application.FollowHyperlink strfilename

This returns the error 2465 "Microsoft Access can't find
the field 'Date' referred to in your expression."

To reiterate a bit more clearly, what I would like to
produce is a unique output file that would look something
like:

\\server\vol1\mci\sb\123456110105.csv

with the formatting I already had in my schema.ini:

[qryTimesheet.csv]
ColNameHeader=True
CharacterSet=OEM
Format=CSVDelimited
DateTimeFormat=dd/mm/yyyy

However when I get this running in Access my

schema.ini
is

ignored (I realise it's not specified in the code, but I
couldn't get it to accept it).

Is what I'm asking at all possible or have I just made a
simple matter worse?

-----Original Message-----
Excellent, thanks Douglas. Just wondering though - how
do
I get this into the macro I created, as I can't seem to
find that macro itself in the VB screen... does this mean
I can't use that functions alongside a macro?

-----Original Message-----
strFileName = strClientName & Format(Date (), "ddmmyy")
& ".csv"

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


in message
I have a query which compiles certain data ready for
output via a macro to a CSV file. I recall in Excel
it's
possible to autmoate some of the process of creating
the
output file to include information from the file
itself
in
the filename - for example to use information

from
the

first two fields as the filename.

So what I would want to do in Access is to take the
first
two fields of the query (say, client name and

date)
and

concatenate them into a filename. Example being:
Dummycompany110105.csv and then the next one

could
be

Dummycorp110105.csv and so on. At the moment I'm
having
to rename each output file manually. Is this possible
in
Access? If so, how would I code it?


.

.



.

.

 

[Douglas J. Steele]

I think we need to start over again.

So, you've got a field in your query to use in the filename. However, you're
trying to export the query, aren't you, as opposed to, say, reading the
results of the query row by row and exporting a file for each row?

Give a bit more detail, please, and either I or someone else should be able
to help.