Trying to Convert Text String to Numeric

[racemadnss]

Im building a database for a company that has a Client # for each Client and a Job # to go with the client

Client# 200 - Job# 002 '2nd job for that client

Ex:
200-002 '2nd job for that client
200-003 '3rd job for that client

When a new job comes in for that client(200) they will get a new Job# 003

Is there a way a Numeric field can handle the 0's in the front? or will I need to use Text format?


Right now I am trying to use text format
But I cant figure out a good way to convert the Text to Numeric, Increase the Number by 1, then convert it back to text with the 0's.

Hope my question makes sense

 

[pbaldy]

I'd store it as a number and display it like:

Format(JobNum,"000")

 

[racemadnss]

OK cool, I was just reading about the Format(JobNum,"000") property.

To get the new job number I query the Jobs table to find the MAX job # for that client, then I will put that value+1 in the [JobID] Textbox and i would like it to look like 003 even though the actual value may only be 3.

Where would I put that code so It would show up right?

 

[pbaldy]

If you want to format the textbox with the number in it, you can just put

000

as the format of the textbox (instead of Standard, General Number, etc). I would use the Format() function I gave you earlier when you needed it somewhere else, like the example you gave earlier (200-002) could be achieved with:

=CustNum & "-" & Format(JobNum,"000")

 

[racemadnss]

Awesome, Thanks alot!

 

[dastr]

Hi Paul,

Do you know by any chance how to convert Binary to Numbers?

I have a column with Binary values and would liek to have it in Numbers?

thanks

 

[DrallocD]

Here is 1 way to do it:

Public Function bin2num(bn As String) As Long
Dim result As Long, nLen As Integer, i As Integer
nLen = Len(bn)
For i = 1 To nLen
    If Mid(bn, i, 1) = "1" Then result = result + 2 ^ (nLen - i)
Next i
bin2num = result
End Function