Originally Posted by
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)
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:
Code:
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;