updating text field with numeric value?

[msalem]

I have a text field Dwg_Id in a table which I'm trying to update to a number using:

Val(1000000*[tbl_LOOPGEN_shts]![SHT]) + [ tbl_LOOPGEN_shts]![Auto_No])

but it's not letting me do that. Please note SHT is also a text field and Auto_No is AutoNumber.

If I only update the field to:

Val(1000000*[tbl_LOOPGEN_shts]![SHT])

it works, but I'm not able to add the other field to it?? Any other ways of doing this?

Thanks

 

[DCrake]

ExprVal(tbl_LOOPGEN_shts]![SHT]) + [ tbl_LOOPGEN_shts]![Auto_No]) * 100000

You also have an extra ) at the end of the string

 

[Brianwarnock]

Val(1000000*[tbl_LOOPGEN_shts]![SHT]) + [ tbl_LOOPGEN_shts]![Auto_No])

is syntactically incorrect as it has unbalanced()
Not sure if this will work but try

Val(1000000*[tbl_LOOPGEN_shts]![SHT]) + Val([tbl_LOOPGEN_shts]![Auto_No])

Brian

 

[Brianwarnock]

Hmm David and I think that you want different arithmetic applied but you should get the picture.

Brian

 

[Brianwarnock]

ExprVal(tbl_LOOPGEN_shts]![SHT]) + [ tbl_LOOPGEN_shts]![Auto_No]) * 100000

You also have an extra ) at the end of the string

Err! Yours don't balance either David, because of the Smilie, you need to turn Smilies off if typing: ( in adjacent positions.

Brian

 

[msalem]

Guys Unfortunately neither work............

David could you retype it pls......I was actually trying this:

Val(1000000*[tbl_LOOPGEN_shts]![SHT]) + [ tbl_LOOPGEN_shts]![Auto_No]

the first part of the Val() expression is evaluated but it doesn't add up with the numeric field.....says a type-conversion error

any suggestions?

 

[Brianwarnock]

That should work.

Brian

 

[DCrake]

Ok Lets take it to the exteme and build a function

Public Function Add2Values(Value1 As Sting,Value2 As Long) As Long

Dim A As Long
Dim B As Long

If IsNumeric(Value1) Then
   A = CLng(Value1)
   A = A * 100000
Else
   A = 0
End If

B = CLng(Value2)

Add2Values = A+B

End Function

Then in your query use

Add2Values(SHT,Auto_No)

 

[msalem]

works a treat now........cheers!