Add numeriac numbers to end of text

[bohon79]

Hello,

Have been trying to find the answer to this but keep coming up in internet search on how to convert. not what i am looking for.

I am trying to add a number to a specific text in sequential text/number value in a query. for example:

if a field has AFC then the field will read AFCIV1, then AFCIV2 and so on. same goes with CSS and NVCIV.

any help.

erick

 

[welshgasman]

You just concatenate the values ?

NewValue = OldValue & NewNumber

If you need leading zeroes then use the Format() functions

 

[Micron]

It would be easier if you had the text in one field and the number in another. Much easier to do something like increase by one
DCount("NumberField","tbl1", "txtField = 'AFCIV'") + 1 than it is to figure out what the last number is in the AFCIV field if it includes a number, and then add one to that.
You would then concatenate the 2 fields in your form control(s).

 

[bohon79]

dcount will not work as it counts all the records and adds a 1 to it.

cancatenate i know how to do and not what i am looking for.

if there are 10 AFC in one field then i am looking for it to do in another field is
afc1
afc2
afc3
and so on

 

[Micron]

dcount will not work as it counts all the records and adds a 1 to it.

Not when you put criteria on the count, as did I.

cancatenate i know how to do and not what i am looking for.

You would be looking for it if you split the data into 2 fields so that you can get the next number more easily - because you'd want to put it back together again (in some form field I presume)

 

[bohon79]

maybe this will show that dcount does not work.

not what i am looking for.

 

[xenou]

Have been trying to find the answer to this but keep coming up in internet search on how to convert. not what i am looking for.

I am trying to add a number to a specific text in sequential text/number value in a query. for example:

if a field has AFC then the field will read AFCIV1, then AFCIV2 and so on. same goes with CSS and NVCIV.

when you say "in a query" do you mean you literally want to write a SELECT query that does this, or are you trying to write an UPDATE query (or something else) that makes a permanent change in your table data?

Or in other words, what is your ultimate goal here?

On a separate note, what Micron said about DCount() could work but as he said you would have to introduce criteria into the function, not simply count all records.

 

[Micron]

maybe this will show that dcount does not work.
not what i am looking for.

You are so sure. Yet here is a table before and after running a query using DCount, where CSS on record 13 needs to get a value of 4: