Hi, Mr. John Spencer,
I have read your replies and support on many websites. I am very impressed with your knowledge and become your fan.
You are a GENIUS. Wish you a great Success and Better Future.
Best Regards,
Shabbir Hazari
John Spencer MVP wrote:
Re: Convert text to number, CInt() doesn't work
03-Apr-09
I would use the following. IsNumeric will return true if the text value can
be treated as a number (and has no extraneous characters)
IIF(IsNumeric([SomeField]),CInt([SomeField]),Null
That will return integers, if you have decimal portions to the number and want
to keep the decimal (fractional) portion use Val, or CDbl, CSng to do the
conversion
Val will return 0 for any string that it cannot interpret, but will error with
null values. It will return a value of 52 if your string is "52 cards in a
deck" or 1 for "1) Bad data". So in some situations VAL works nicely
Val([SomeField] & ""
will return 0 for fields that are null or fields that start with a letter and
will return the numeric value of the start of the field - all the numbers
until the string contains a non-arithmetic character
Some of the rules for Va
-- Spaces are ignore
-- Minus or plus signs before the number are interpreted correctl
-- First Period (or your decimal separator) is interpreted correctl
-- An embedded comma will chop the number at the comma
-- The letter "e" or "d" can be treated as if you were using scientific notatio
John Spence
Access MVP 2002-2005, 2007-200
The Hilltop Institut
University of Maryland Baltimore Count
Andy wrote:
Previous Posts In This Thread:
Convert text to number, CInt() doesn't work
I have a table where a field is text, when creating a make table query, I
need this field to be outputted as a number field, but CInt() doesn't work
and I get the conversion failure message
Is there anyway I can check to see what is causing the problem, or is CInt()
not what I should be using
Some values are empty and I would imagine that there are "", rather than
NULL, again anyway to check if it gets this to work?
Thanks
Re: Convert text to number, CInt() doesn't work
Hi Andy
As a first suggestion, try using Val(), rather than Cint(). And wrap the
original field in an Nz function, since Val() will fail with a Null entry.
So try: Val(Nz(YourField),0
You can use the Nz function in your Cint expression to cope with nulls in
your field, but that will also give an error with a zero-length string
HTH
Ro
Andy wrote:
Re: Convert text to number, CInt() doesn't work
I would use the following. IsNumeric will return true if the text value can
be treated as a number (and has no extraneous characters)
IIF(IsNumeric([SomeField]),CInt([SomeField]),Null
That will return integers, if you have decimal portions to the number and want
to keep the decimal (fractional) portion use Val, or CDbl, CSng to do the
conversion
Val will return 0 for any string that it cannot interpret, but will error with
null values. It will return a value of 52 if your string is "52 cards in a
deck" or 1 for "1) Bad data". So in some situations VAL works nicely
Val([SomeField] & ""
will return 0 for fields that are null or fields that start with a letter and
will return the numeric value of the start of the field - all the numbers
until the string contains a non-arithmetic character
Some of the rules for Va
-- Spaces are ignore
-- Minus or plus signs before the number are interpreted correctl
-- First Period (or your decimal separator) is interpreted correctl
-- An embedded comma will chop the number at the comma
-- The letter "e" or "d" can be treated as if you were using scientific notatio
John Spence
Access MVP 2002-2005, 2007-200
The Hilltop Institut
University of Maryland Baltimore Count
Andy wrote:
EggHeadCafe - Software Developer Portal of Choice
Migration 2003-2007 Project Server details
http://www.eggheadcafe.com/tutorial...41-dd519122bd06/migration-20032007-proje.aspx