Stick additional digits in front of a number?

[Clueless Newbie]

Yep, stupid me again...

I'm trying to update a table though a form ("Form_Kuendigen").
The table ("Vertragsnummern") contains two numeric fields ("VertragsNr" and "VertragNRZFYM") as well as two checkboxes named "Kuend_VN" and "Kuend_ZFYM".

What I'm trying to achieve is the following:

When Kuend_VN and/or Kuend_ZFYM are checked I want to update VertragsNr and/or VertragNRZFYM with their respective current entry plus an additional "100" right in front of the current value. I.e., a value of 999999 should be transformed into 100999999 etc. (Our SAP will then correctly identify these records as cancelled contracts.)

Unfortunately, I can't just add 100000000 to every entry in question, since some of them are shorter and some are longer, depending on the age of the recordset.

Does any of you happen to have an idea, please? Thank you!

Ute

 

[ColinEssex]

Ute, just thinking quickly, you may be able to use an IIf statement.

IIf([Kuend_VN] = "Yes","100"& [VertragsNr],[VertragsNr])

I haven't tried it, but I'm sure someone will know for sure

Col

 

[raskew]

Hi Ute-

This function will return the number you described,
regardless of size:

Function RaiseIt(num As Long) As Long
'Sample Usage:
'? RaiseIt(999)
'100999
'? RaiseIt(999999)
'100999999

Dim n As Integer
Dim numHold As Long

numHold = num

n = 1

Do While numHold > 10 ^ n
   n = n + 1
Loop

RaiseIt = 10 ^ (n + 2) + numHold

End Function

HTH - Bob

 

[neileg]

Problem with Colin's answer is that it will return a text field. If you need a number you could go for:
IIf([Kuend_VN] = "Yes",Val("100"& [VertragsNr]),[VertragsNr])

Or you may prefer Bob's approach.

 

[ColinEssex]

Problem with Colin's answer is that it will return a text field. If you need a number you could go for:
IIf([Kuend_VN] = "Yes",Val("100"& [VertragsNr]),[VertragsNr])

Thanks Neil, I'd overlooked that point.

Col

 

[Clueless Newbie]

Works like a charm! Thank you!

Ute

 

[Clueless Newbie]

PS: This would have worked also, as I've found out, but it's more code, so I prefer your solution

        satz.Edit
            If Kuend_VN = True Then
              satz!VertragsNr = "100" & [VertragsNr]
            Else
              satz!VertragsNr = Me!VertragsNr
            End If
            If Kuend_ZFYM = True Then
              satz!VertragNRZFYM = "100" & [VertragsNr]
            Else
              satz!VertragNRZFYM = Me!VertragNRZFYM
            End If
        satz.Update