Form that filters with character field made into integer

[Ruegen]

I have

Me.Filter = "[numberInt]" & " between " & (PostcodeConverted + 5) & " And " & (PostcodeConverted - 5) & ""

I think the concatination is wrong

numberInt is actually cint(schoolPostcode) in the select record source

however I wouldn't mind using

Me.Filter = " cint(schoolPostcode)" & " between " & (PostcodeConverted + 5) & " And " & (PostcodeConverted - 5) & ""

though not sure how

any advice?

[June7]

Why the + and - 5?

CInt should fail for higher numbers. Try CLng.

Is your expression not working? Looks okay to me. What is PostcodeConverted?

Me.Filter = "[numberInt] BETWEEN " & (PostcodeConverted + 5) & " AND " & (PostcodeConverted - 5)

[Ruegen]

Why the + and - 5?

CInt should fail for higher numbers. Try CLng.

Is your expression not working? Looks okay to me. What is PostcodeConverted?

Me.Filter = "[numberInt] BETWEEN " & (PostcodeConverted + 5) & " AND " & (PostcodeConverted - 5)

the postcode is 4 digits long - never higher.

We minus or plus 5 to get surrounding areas.

The problem I have is that the field is a string.

I made in the select row source have an extra field that converted the string to an integer - for a while the form and functions work. Today for some odd reason it broke and stopped working

PostcodeConverted is a public variable that stores the currently opened school's postcode.

PostcodeConverted = CInt(Me.SchoolPostCode.Value)

the idea is that you open a form to a list of schools and filter it.

only because the select query is not working - I can't use filter form using the int field "[intfieldofpostcode]"

originally I had 'DoCmd.OpenForm "frmSchoolsSearchPostCode", , , "[numberInt]" & " between " & (PostcodeConverted + 5) & " And " & (PostcodeConverted - 5) & ""

even if I try to filter the form I can't convert the string field in the where to an int - this won't work because I am calling a field that doesn't exist.

not sure what to do still

added the select to this post:

SELECT tblSchools.SchoolPostCode, tblSchools.Enrollment, tblSchools.NewSchoolsID, tblSchools.Removed, Len([SchoolPostCode]) AS numberP, tblStates.StateID, tblStates.SchoolState, IIf(isnothing([SchoolPostCode]),CInt(Format(0,"0000")),Format(CInt([SchoolPostCode]),"0000")) AS numberInt, Right([numberInt],4)+10 AS test, Right([numberInt],4)-10 AS test2, tblSchools.SchoolAddress, tblSchools.SchoolName, tblAreas.Area, tblAreas.AreasID, tblSchools.AreaID, tblSchools.SchoolSuburb
FROM tblStates INNER JOIN (tblAreas INNER JOIN tblSchools ON tblAreas.AreasID = tblSchools.AreaID) ON tblStates.StateID = tblSchools.StateID
WHERE (((tblSchools.NewSchoolsID)<>9389) AND ((tblSchools.Removed) Is Null) AND ((tblAreas.AreasID)<>93))
ORDER BY tblSchools.Enrollment DESC;

[Ruegen]

The actual field is

numberInt: IIf(isnothing([SchoolPostCode]),CInt(Format(0,"0000")),Format(CInt([SchoolPostCode]),"0000"))

[Ruegen]

The function is here

Function filterSchoolPostCodes(Postcode As Control, FormToOpen As String, ControlToPassPostCode As String)


If Len(Postcode) = 5 Or Len(Postcode) = 4 Then
Dim strStore As String
strStore = Postcode


Dim PostcodeConverted As Integer
PostcodeConverted = CInt(strStore)


DoCmd.OpenForm "frmSchoolsSearchPostCode", , , "[numberInt]" & " between " & (PostcodeConverted + 5) & " And " & (PostcodeConverted - 5) & ""
Forms(FormToOpen).Controls(ControlToPassPostCode).Value = PostcodeConverted


Else
End If


End Function

[June7]

Might not even be necessary to convert the text postcode. Access will do arithmetic with string if there is a true number entity included in the expression.

"5567" + "5"

will concatenate to 55675

"5567" + 5

will sum to 5572

Although converting might be safest. However, conversion functions fail with null, so it means every record must have value, or handle possible null with Nz.

CInt(Nz([fieldname],0))

Conversion also fails with empty string. I never allow empty string in fields.

Have you step debugged?

IsNothing is not a valid intrinsic function. I am aware of IsNull, IsNumeric, IsEmpty.

[Ruegen]

Might not even be necessary to convert the text postcode. Access will do arithmetic with string if there is a true number entity included in the expression.

"5567" + "5"

will concatenate to 55675

"5567" + 5

will sum to 5572

Although converting might be safest. However, conversion functions fail with null, so it means every record must have value, or handle possible null with Nz.

CInt(Nz([fieldname],0))

Conversion also fails with empty string. I never allow empty string in fields.

Have you step debugged?

IsNothing is not a valid intrinsic function. I am aware of IsNull, IsNumeric, IsEmpty.

isnothing() checks if there is anything null/nothing/non exist

The part that doesn't work is DoCmd.OpenForm FormToOpen, , , "[numberInt] BETWEEN " & (PostcodeConverted + 5) & " AND " & (PostcodeConverted - 5)

everything else does

the function passes are

Function filterSchoolPostCodes(PostCodeControl As Control, FormToOpen As String, ControlToPassPostCode As String)

this is on the form (when you double click the postcode field)

Call filterSchoolPostCodes(Me.Postcode, "frmSchoolsSearchPostCode", "txtPostCode")

[June7]

Doesn't work means what - error message, wrong results, nothing happens?

Issue maybe with numberInt calculation: IIf(isnothing([SchoolPostCode]), CInt(Format(0,"0000")), Format(CInt([SchoolPostCode]),"0000"))

Is IsNothing a custom function? It doesn't work in the immediate window so it is not an intrinsic function.

Format within CInt is meaningless because CInt will drop the leading zeros and return just 0.

CInt within Format is useless because Format results in a string value.

So in one case it returns a number and in the other it returns a string. This is mixing data types. The expression should return the same data type for both conditions.

[Ruegen]

Although I'm not at work to check - I think that is the fault.

[Ruegen]

Doesn't work means what - error message, wrong results, nothing happens?

Issue maybe with numberInt calculation: IIf(isnothing([SchoolPostCode]), CInt(Format(0,"0000")), Format(CInt([SchoolPostCode]),"0000"))

Is IsNothing a custom function? It doesn't work in the immediate window so it is not an intrinsic function.

Format within CInt is meaningless because CInt will drop the leading zeros and return just 0.

CInt within Format is useless because Format results in a string value.

So in one case it returns a number and in the other it returns a string. This is mixing data types. The expression should return the same data type for both conditions.

The strange thing query still won't convert it to an integer

IIf(IsNull([SchoolPostCode]),CInt(0),CInt([SchoolPostCode]))

I wrapped cint around both just to be sure....

I still get a data mismatch - and this is in the query design view to be sure.

[Ruegen]

Changed it to numberInt: IIf(isnothing([SchoolPostCode]),Val(0),Val([SchoolPostCode])) and it seems to work..