text to numeric problem

[laurat]

Hope I can explain this as clearly as possible......

When I designed a database, the users first specified what fields they wanted and whether they'd be numeric, text, etc. There were five fields made text and one memo field. I thought this odd at first because one of the fields they made text was a Quantity field. Well this worked fine for months. Now they have added three additional quantity fields. When a user enters info into these quantity fields on a form they would like code to make sure the sum of the three fields do not exceed the total quantity originally. This would be simple, however as I stated before, the originaly quantity field is a text field and therefore I cannot perform calculations. I cannot just convert it to a numeric fields because some records have more than numbers in the field and would be deleted. I am stuck here and any suggestions would be greatly appreciated. Thank you.

 

[simongallop]

For you code you would need something like:

If Val(OriginalQuant) > newQ+newQ2+newQ3 then
etc
Else
etc
End

The problem that you will find is when you try to compare to one of the records that does contain text because it won't be able to convert to number. Are you sure that the few records that have text in the quantity field are correct and not typos?

HTH

 

[laurat]

The data in the field is not typos, for example one has 24" (representing inches). One has two quantities listed with the work and separating them. (This should not be happening but it already has many times).

I am not quite sure what you mean by your last reply. Could you or someone else explain that further, or any other solutions. Thank you again.

 

[Howlsta]

Hi!

If you've got things like " for inches this shouldn't be a problem as the Val function only stops reading the string at the first character that it cannot recognise as part of a number. It also strips blanks, tabs and line feeds from the argument string. eg the following returns the value of 1234567:

Val (" 123 4567 Fred Bloggs")

So if you had 24" the it will return 24

If Val(TotalQuantity) > Quantity1 + Quantity2 +Quantity3 then
msgbox "OK"
Else
msgbox "Exceeds Original Quantity" etc
End

you could make this a function and call it in the before update event of each quantity text box.

HTH Rich

 

[laurat]

Thank you

Thank you for the suggestions. I added that little bit of code and it seems to work fine but I am going to test it thoroughly today.

Thanks again,
Laura