Add numeriac numbers to end of text

bohon79

New Member
Joined
Jul 2, 2015
Messages
27
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

Well-known Member
Joined
May 25, 2013
Messages
696
You just concatenate the values ?

Code:
NewValue = OldValue & NewNumber
If you need leading zeroes then use the Format() functions
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,758
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

New Member
Joined
Jul 2, 2015
Messages
27
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,758
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)
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,550
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,758
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:


tblBohon.jpg


tblBohon2.jpg
 

Forum statistics

Threads
1,081,488
Messages
5,359,013
Members
400,515
Latest member
smigaud

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top