IsNumeric() Bug?
in [Help]
|
From: Vincent on 8 Jun 2010 11:04 On Jun 8, 10:06 am, XPS35 <xps...(a)gmail.com> wrote: > Vincent wrote: > > The following was ran from the immediate window of Access 2007: > > > ?DLookup("PurgePeriod", "Info") = "" > > True > > > ?IsNumeric("") > > False > > > ?IsNumeric(DLookup("PurgePeriod", "CompanyInfoMaster")) > > True > > > Huh? Is someone able to explain this? > > > Vincent > > The value of a boolean (yes/no) field is either -1 (representing true) > or 0 (false). > Try using IsNumeric in a query and you see only -1 or 0 in the column. > > -- > Groeten, > > Peterhttp://access.xps350.com > > --- news://freenews.netfront.net/ - complaints: n...(a)netfront.net --- The "PurgePeriod" field is an integer field. If I try using IsNumeric in a query, I see a value of 0 in the column, which would indicate false. But, why is the above scenario showing a value of true?
From: Vincent on 8 Jun 2010 11:16 On Jun 8, 10:59 am, "Bob Barrows" <reb01...(a)yahoo.com> wrote: > Vincent wrote: > > On Jun 8, 10:42 am, "Bob Barrows" <reb01...(a)yahoo.com> wrote: > >> Vincent wrote: > >>> The following was ran from the immediate window of Access 2007: > > >>> ?DLookup("PurgePeriod", "Info") = "" > >>> True > > >>> ?IsNumeric("") > >>> False > > >>> ?IsNumeric(DLookup("PurgePeriod", "CompanyInfoMaster")) > >>> True > > >>> Huh? Is someone able to explain this? > > >> You do realize that, without criteria, the DLookup returns the value > >> from a random row in the domain (table) you are searching ... > > >> Given that, perhaps the PurgePeriod field in the Info table contains > >> an empty string in the random row examined by DLookup, and the > >> PurgePeriod in CompanyInfoMaster contains a number in the random row > >> selected by Dlookup? > > > Nope. There is only one row in this table. > > My point was: your example code is referring to two different tables. You > told us what DLookup returns from Info. What does it return from > CompanyInfoMaster? > > -- > Bob Barrows I'm sorry. I was trying to abbreviate and only abbreviated one spot. They are both looking at the same table. Vincent
From: Salad on 8 Jun 2010 11:23 Vincent wrote: > On Jun 8, 10:06 am, XPS35 <xps...(a)gmail.com> wrote: > >>Vincent wrote: >> >>>The following was ran from the immediate window of Access 2007: >> >>>?DLookup("PurgePeriod", "Info") = "" >>>True >> >>>?IsNumeric("") >>>False >> >>>?IsNumeric(DLookup("PurgePeriod", "CompanyInfoMaster")) >>>True >> >>>Huh? Is someone able to explain this? >> >>>Vincent >> >>The value of a boolean (yes/no) field is either -1 (representing true) >>or 0 (false). >>Try using IsNumeric in a query and you see only -1 or 0 in the column. >> >>-- >>Groeten, >> >>Peterhttp://access.xps350.com >> >>--- news://freenews.netfront.net/ - complaints: n...(a)netfront.net --- > > > The "PurgePeriod" field is an integer field. If I try using IsNumeric > in a query, I see a value of 0 in the column, which would indicate > false. But, why is the above scenario showing a value of true? I have an existing table called Junk. It has some records in it. I added a field called Junk1, type number longint. I run a query and it I enter Expr1: IsNumeric([junk1]). It returns 0. If open the table, Junk1 is blank. When I run an update query to set Junk1 to 0, it of couse returns -1 when I check the numeric status. I created a new table Table1. I created 2 fields Junk and Junk1. I left the default as 0 in Junk and removed the default in Junk1. I entered 123 in Junk. Then checked Isnumeric on both fields. Junk was -1, Junk1 was 0.
From: Bob Barrows on 8 Jun 2010 11:31 Salad wrote: > I have an existing table called Junk. It has some records in it. I > added a field called Junk1, type number longint. I run a query and > it I enter Expr1: IsNumeric([junk1]). It returns 0. If open the > table, Junk1 is blank. When I run an update query to set Junk1 to 0, > it of couse returns -1 when I check the numeric status. > > I created a new table Table1. I created 2 fields Junk and Junk1. I > left the default as 0 in Junk and removed the default in Junk1. I > entered 123 in Junk. Then checked Isnumeric on both fields. Junk was > -1, Junk1 was 0. That's because Junk1 contains Null and IsNumeric(null) returns false. -- Bob Barrows
From: Bob Barrows on 8 Jun 2010 11:59 Vincent wrote: > On Jun 8, 10:06 am, XPS35 <xps...(a)gmail.com> wrote: >> Vincent wrote: >>> The following was ran from the immediate window of Access 2007: >> >>> ?DLookup("PurgePeriod", "Info") = "" >>> True >> >>> ?IsNumeric("") >>> False >> >>> ?IsNumeric(DLookup("PurgePeriod", "CompanyInfoMaster")) >>> True >> >>> Huh? Is someone able to explain this? >> >>> Vincent >> >> The value of a boolean (yes/no) field is either -1 (representing >> true) or 0 (false). >> Try using IsNumeric in a query and you see only -1 or 0 in the >> column. >> >> -- >> Groeten, >> >> Peterhttp://access.xps350.com >> >> --- news://freenews.netfront.net/ - complaints: n...(a)netfront.net --- > > The "PurgePeriod" field is an integer field. If I try using IsNumeric > in a query, I see a value of 0 in the column, which would indicate > false. But, why is the above scenario showing a value of true? First of all, a Number field can never contain an empty string. Strings (Text - empty or otherwise) and Numbers are mutually exclusive. So, given that PurgePeriod is indeed a Number field with a Long or Short "Field Size" setting, your first test (?DLookup("PurgePeriod", "Info") = "") should always return false. Now,you say the test returned True. I cannot duplicate this result, even with a Text field - if there is no value in the field (Null), or the field contains an empty string, that test never returns true, whether it's a Text or a Number field). The test returns true in a query, Select [textfield]="" AS EmptyString? From Test WHERE Field1=1 but my attempts with Dlookup have never returned True. If it is a Text field containing an empty string (as confirmed in a query) Dlookup always returns Null for some reason. So I guess it's time for the guessing to stop. What is the actual datatype of the field in question? Text or Number? What is the Required property set to? If it is a Text field, is Allow Zero String set to true? If it is Required, does it have a default value set? And lastly, what is the value contained in the field? If it is a Number field, why on earth are you bothering with IsNumeric()? Either the field contains a value which has to be numeric, or it contains Null. There is no third choice which would allow it to contain non-numeric value. If you are concerned as to whether it contains a value, just use IsNull() to check the value returned. Or use Nz as the expression in your call to DLookup: PurgePeriod=Dlookup("Nz([PurgePeriod],0)","CompanyInfoMaster") PS. There is a documented behavior of Isnumeric that some people think is a bug: some alpha characters are considered to be numeric by IsNumeric: "e" and "d" are used for entering exponents (scientific notation) in Single and Double fields so IsNumeric will return true for a string containing those characters. Actually, I'm through guessing. -- Bob Barrows
First
|
Prev
|
Pages: 1 2 Prev: Track Name Autocorrect error when running mdb in 2007 Next: Access 2010 webbrowser control |