How to insert a number with eight or more digits in standard format?

[msalem]

I have a VBA code which picks values from a form, performs calculations on them and then inserts them into a text field in a table.

part of the code is:

AUTO_NO_ads = Me.list_LOOPS_AVAIL_ads.Column(1, varItm_ads)
DWG_ID_ads = Val(1000000 * Me.cbo_SHT_ads) + AUTO_NO_ads + 10000000

The problem occurs as the variable DWG_ID_ads exceeds 7 digits (because of 10000000 added) and then when I use the toggle breakpoint it is evaluated as say 1.800095E+07(exponential format)

How can I store it in the table in standard format i.e. 18000950 and not in the exponential format above. The variable has to be 8 digits long. I've tried CLng but that doesn't help. Any suggestions?

P.S. If I donot add 10000000 then it works fine for 7 digits, but not for 8.

 

[Rabbie]

Have you tried defining the field as Decimal?

 

[msalem]

Just to add to the code to give a better picture:
---------------------------------------------------------------
AUTO_NO_ads = Me.list_LOOPS_AVAIL_ads.Column(1, varItm_ads)
DWG_ID_ads = Val(1000000 * Me.cbo_SHT_ads) + AUTO_NO_ads + 10000000
rst_ADD_SHT_ads.Open "tbl_LOOPGEN_shts", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If Not IsNull(Me.cbo_SHT_ads) Then
With rst_ADD_SHT_ads
If Not .EOF Then
.AddNew
![DWG_ID] = DWG_ID_ads
![LOOP_NUMBER] = LOOP_NUMBER_ads
![SHT] = Me.cbo_SHT_ads
![BGDNO] = "BGD01"
![FLDR] = "CHI"
![MOD] = 0
![Run] = 0
![DELETED] = 0
.Update
End If
End With
End If
rst_ADD_SHT_ads.Close
Set rst_ADD_SHT_ads = Nothing
-----------------------------------------------------------------------

I'm not really concerned if DWG_ID_ads is evaluated in exponential form as long as when the field is added to tbl_LOOPGEN_shts!DWG_ID it's in the eight digit format. Could a format be specified for this:

![DWG_ID] = DWG_ID_ads

?

 

[MStef]

Try next:
In the table ENLARGE (manual, by mouse) the field.

 

[MStef]

Also, do it in the form.

 

[gemma-the-husky]

why wouldnt clng work

this handles a maxint of over 2billion, hence 9 digits for sure

is it just the display width of your column, or field causing the exponential display

 

[msalem]

Guys.....it works now

My table field was set to type Text and I had to change that to Number.

Although the variable DWG_ID_ads was evaluated in exponential but when inserted in the table, it took the standard format.

Cheers!