Append first diget to last diget

[Guest]

I have a table Named Transact_Detail with a field named TR_NO. It carries a 4
diget code like 1001, 1002 then it can go to the 2000 series up to the 9000
series. I want to have a field in my query where it would take the first
diget and append it to the end of the number and name that field HLP_TEXT.
How would I go about doing this. I know it would be an expression but dont
know where to begin.

 

[Michel Walsh]

Hi,



If the number is a number, then use the integer division by 1000:


9056 \ 1000


returns 9


If the nunber us a string, use Left:


Left("9056", 1)

returns the string with one character, the character 9



To concatenate a number (or a string) to a string, use & (be sure to use a
space before and after it):


"something" & (9006\1000)


should return

something9


while
"something " & (9006\1000)

should return

something 9




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I forgot to ask ..
Does this take the first diget from a Text datatype Named ..TR_NO...which
has 4 numbers in the field like 2001,3004 etc and append it to the last diget
of a Text datatype field named ..Transaction Type... which would look
something like 0002 or 1003 (from above numbers)? I think I should have been
more clear on on this. Their will always bee 4 digets in TR_NO filed and I
just want to take the first diget from that filed and append it to the last
diget in the..Transaction Type.. field which is also a text datatype. Thanks
again

 

[John Spencer]

Can you explain a little further

How does 2001 become 0002, while 3004 becomes 1003?
In other words, I can see where the last character comes from but not the
source for the first three characters.

Perhaps what you want is

TransactionType = TransactionType & Left(TR_No,1)

 

[John Vinson]

I forgot to ask ..
Does this take the first diget from a Text datatype Named ..TR_NO...which
has 4 numbers in the field like 2001,3004 etc and append it to the last diget
of a Text datatype field named ..Transaction Type... which would look
something like 0002 or 1003 (from above numbers)? I think I should have been
more clear on on this. Their will always bee 4 digets in TR_NO filed and I
just want to take the first diget from that filed and append it to the last
diget in the..Transaction Type.. field which is also a text datatype. Thanks
again

I think you're using the word "diget" differently than I use the word
"digit". The first digit of TR_NO is not 2006 - it is 2. The last
digit of 1003 is not 1003 - it is 3. Is "23" what you want?

John W. Vinson[MVP]