Function to test integer numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a validation of a database and I need to check whether or not
a field contains positive integer numbers.
I have created a function called "is_pos_integer" which accepts a variant
parameter and returns a boolean result.
My problem is that if I test my function on a simple query like this:

SELECT tbl_students_import.record_id, tbl_students_import.years_prior_study,
is_pos_integer([years_prior_study]) AS Test
FROM tbl_students_import;

the quey shows all 0s in the field "Test". Actually my recordset contains
some negative numbers in "years_prior_study" along with letters
(years_prior_study is defined as text field in the table). The strange thing
is that the debugger shows me that my function correctly detects the values
which are not positive integers but the query does not.
Does anyone have any idea why this happens?
If needed I can post the code of the funtion.

Many thanks,
Paolov
 
Function IsPositiveInteger(varIn As Variant) As Boolean
On Error Resume Next
Dim iResult As Integer

If Not IsEmpty(varIn) Then
If IsNumeric(varIn) Then
iResult = CInt(varIn)
End If
End If

IsPositiveInteger = (iResult > 0)
End Function

If your field contains numbers as well as text, and you want the leading
numbers treated as text, replace:
If IsNumeric(varIn) Then
iResult = CInt(varIn)
End If
with:
If Not IsNull(varIn) Then
iResult = CInt(Val(varIn))
End If
 
It would help if you posted your function. This will check for positive
numbers.

IsPositive: IsNumeric(Years_Prior_Study) and (Val(Nz(Years_Prior_Study,"0"))
= Abs(Val(Nz(Years_Prior_Study,"1"))))
 
SELECT tbl_students_import.record_id,
tbl_students_import.years_prior_study,
is_pos_integer([years_prior_study]) AS Test FROM tbl_students_import;
Click to expand...

SELECT
record_id,
years_prior_study,
(years_prior_study >= 0) AS NotNeg
FROM students_import


I don't see the point of the custom function, when it takes approx three
characters to do the test inside the db engine. If you want to correct
the data on the import, you can collapse the whole thing like this


SELECT
record_id,
IIF(years_prior_study<0, NULL, years_prior_study)
AS experience
FROM students_import


Hope that helps


Tim F
 
Tim,

That fails to detect the value being an integer. It does take care of it being positive.

123.45 is not an integer number although it is a number and is positive. The
user also stated that years_prior_study was a text field. Well, kind of stated it.

Quote
Actually my recordset contains some negative numbers in "years_prior_study"
along with letters
(years_prior_study is defined as text field in the table).
End Quote

Tim said:
SELECT tbl_students_import.record_id,
tbl_students_import.years_prior_study,
is_pos_integer([years_prior_study]) AS Test FROM tbl_students_import;
Click to expand...

SELECT
record_id,
years_prior_study,
(years_prior_study >= 0) AS NotNeg
FROM students_import

I don't see the point of the custom function, when it takes approx three
characters to do the test inside the db engine. If you want to correct
the data on the import, you can collapse the whole thing like this

SELECT
record_id,
IIF(years_prior_study<0, NULL, years_prior_study)
AS experience
FROM students_import

Hope that helps

Tim F
Click to expand...
 
That fails to detect the value being an integer. It does take care of
it being positive.
Click to expand...

Okay, fair cop: the full answer would be something like

SELECT
record_id,
IIF(years_prior_study IS NOT NULL AND
IsNumeric(years_prior_study) AND
CInt(years_prior_study)=CDbl(years_prior_study) AND
CInt(years_prior_study)>0,
CInt(years_prior_study),
NULL) AS Experience
FROM students_import

I still don't see the point of a custom VBA function.


B Wishes


Tim F
 
Back
Top