Export always witha different name

[Marco]

Hi.

I need to export a query to a txt but always with a different name.

Imagine.

I now I'll export with the following name: FA0000000001.txt the next time
all need to export with the name of FA0000000002.txt.

How can I build this type of sequentional name number?

Regards,
Marco

 

[Arvin Meyer [MVP]]

Here's an old newsgroup post that shows one way of doing it:

If the last used ID was C00097, Access would automatically set the ID for
the new
customer as C00098.

Use the Right$() function to get the portion of the field you want, and the
Val() function to turn it into a number you can manipulate. Then use Str()
to turn it back into a string, and concatenate it to your "C". It might
look something like this:

="C" & Format(Val(Right(Max([CustID]),5))+1,"00000")

If your Customer ID field is called CustID. Paste that string into the
Default Value property of your Customer ID field.

It works like this:

Starting from the inside out, Max([CustID]) returns the maximum value of
the [CustID] field on the current form (C00097;

This works on text just as
well as numbers). Right(Max([CustID]),5) returns the rightmost 5
characters of the selected string (00097).

Val(Right(Max([CustID]),5))
turns that string into a numeric value (97), and

Val(Right(Max([CustID]),5))+1 adds one to it (98);

Format(Val(Right(Max([CustID]),5))+1,"00000") turns the value (98) into a
formatted string of four characters (0098); any which are not "filled" will
be zeros.

Finally, "C" & Format(Val(Right(Max([CustID]),5))+1,"00000") concatenates
the "C"
onto the front to meet your formatting requirements. Don't forget the "="
sign.

 

[Marco]

Hi. Thanks.

here do I put that information/code?

In a form?

regards,
Marco

Here's an old newsgroup post that shows one way of doing it:

If the last used ID was C00097, Access would automatically set the ID for
the new
customer as C00098.

Use the Right$() function to get the portion of the field you want, and the
Val() function to turn it into a number you can manipulate. Then use Str()
to turn it back into a string, and concatenate it to your "C". It might
look something like this:

="C" & Format(Val(Right(Max([CustID]),5))+1,"00000")

If your Customer ID field is called CustID. Paste that string into the
Default Value property of your Customer ID field.

It works like this:

Starting from the inside out, Max([CustID]) returns the maximum value of
the [CustID] field on the current form (C00097;

This works on text just as
well as numbers). Right(Max([CustID]),5) returns the rightmost 5
characters of the selected string (00097).

Val(Right(Max([CustID]),5))
turns that string into a numeric value (97), and

Val(Right(Max([CustID]),5))+1 adds one to it (98);

Format(Val(Right(Max([CustID]),5))+1,"00000") turns the value (98) into a
formatted string of four characters (0098); any which are not "filled" will
be zeros.

Finally, "C" & Format(Val(Right(Max([CustID]),5))+1,"00000") concatenates
the "C"
onto the front to meet your formatting requirements. Don't forget the "="
sign.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi.

I need to export a query to a txt but always with a different name.

Imagine.

I now I'll export with the following name: FA0000000001.txt the next time
all need to export with the name of FA0000000002.txt.

How can I build this type of sequentional name number?

Regards,
Marco

 

[Marco]

Hi.

I have this and is returning error on Max:

Dim stDocName As String

stDocName = "C" &
Format(Val(Right(Max([19_tbl_Numerador_Ficheiro]![Numerador_Ficheiro]), 10))
+ 1, "0000000000")

DoCmd.TransferText , acExportDelim, Export_ES, "93_qry_Export_to_TXT_ES",
stDocName

saying that "sub or function not defined"

Regards.

Here's an old newsgroup post that shows one way of doing it:

If the last used ID was C00097, Access would automatically set the ID for
the new
customer as C00098.

Use the Right$() function to get the portion of the field you want, and the
Val() function to turn it into a number you can manipulate. Then use Str()
to turn it back into a string, and concatenate it to your "C". It might
look something like this:

="C" & Format(Val(Right(Max([CustID]),5))+1,"00000")

If your Customer ID field is called CustID. Paste that string into the
Default Value property of your Customer ID field.

It works like this:

Starting from the inside out, Max([CustID]) returns the maximum value of
the [CustID] field on the current form (C00097;

This works on text just as
well as numbers). Right(Max([CustID]),5) returns the rightmost 5
characters of the selected string (00097).

Val(Right(Max([CustID]),5))
turns that string into a numeric value (97), and

Val(Right(Max([CustID]),5))+1 adds one to it (98);

Format(Val(Right(Max([CustID]),5))+1,"00000") turns the value (98) into a
formatted string of four characters (0098); any which are not "filled" will
be zeros.

Finally, "C" & Format(Val(Right(Max([CustID]),5))+1,"00000") concatenates
the "C"
onto the front to meet your formatting requirements. Don't forget the "="
sign.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi.

I need to export a query to a txt but always with a different name.

Imagine.

I now I'll export with the following name: FA0000000001.txt the next time
all need to export with the name of FA0000000002.txt.

How can I build this type of sequentional name number?

Regards,
Marco

 

[Arvin Meyer [MVP]]

You might possibly need to set a reference to DAO and delete a reference to
ADO from any code window:

Tools >>> References

uncheck ActiveX Data Objects and find Microsoft Data Access Objects, and
check it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi.

I have this and is returning error on Max:

Dim stDocName As String

stDocName = "C" &
Format(Val(Right(Max([19_tbl_Numerador_Ficheiro]![Numerador_Ficheiro]),
10))
+ 1, "0000000000")

DoCmd.TransferText , acExportDelim, Export_ES, "93_qry_Export_to_TXT_ES",
stDocName

saying that "sub or function not defined"

Regards.

Here's an old newsgroup post that shows one way of doing it:

If the last used ID was C00097, Access would automatically set the ID for
the new
customer as C00098.

Use the Right$() function to get the portion of the field you want, and
the
Val() function to turn it into a number you can manipulate. Then use
Str()
to turn it back into a string, and concatenate it to your "C". It might
look something like this:

="C" & Format(Val(Right(Max([CustID]),5))+1,"00000")

If your Customer ID field is called CustID. Paste that string into the
Default Value property of your Customer ID field.

It works like this:

Starting from the inside out, Max([CustID]) returns the maximum value of
the [CustID] field on the current form (C00097;

This works on text just as
well as numbers). Right(Max([CustID]),5) returns the rightmost 5
characters of the selected string (00097).

Val(Right(Max([CustID]),5))
turns that string into a numeric value (97), and

Val(Right(Max([CustID]),5))+1 adds one to it (98);

Format(Val(Right(Max([CustID]),5))+1,"00000") turns the value (98) into a
formatted string of four characters (0098); any which are not "filled"
will
be zeros.

Finally, "C" & Format(Val(Right(Max([CustID]),5))+1,"00000") concatenates
the "C"
onto the front to meet your formatting requirements. Don't forget the
"="
sign.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi.

I need to export a query to a txt but always with a different name.

Imagine.

I now I'll export with the following name: FA0000000001.txt the next
time
all need to export with the name of FA0000000002.txt.

How can I build this type of sequentional name number?

Regards,
Marco