It appears the problem lies in how I have the fields default format setup. I
originally had it set to currency, but this seemed to confuse the filter when
I only entered a low amount. (ie. Low $1000 High $0.00 default) so my
filtered results were weird. I then changed my default format from currency,
but that gave me a default value of 0 in both fields. This caused a problem
because I have my search form setup so that when NO criteria is entered ALL
records are returned. (this is a very small database), but with 0 in both low
and high fields access interprets that as criteria and returns NO results. So
I made the fields default to having NOTHING. This brough up the debugger
highlighting
Me.fsubRecordSearch.Form.Filter = strWhere
Any ideas would be great.
Damian S said:
Hi again Justin,
Your code looks fine, but I would recommend that you include the check that
both fields are null, otherwise you will end up with an issue.
The use of strWhere = "1=1" is used to return all records so that you don't
have to worry about whether you have commenced your where clause already, you
simply "AND" criteria onto it...
What line exactly isn't working, because your code looks as though it should
work.
Damian.
:
Ok. Update. I seem to have the filter working for the high as well as the
between, but now the low seems to not work. Here is the relevant code so far..
'If Weekly Allowance (tblEmployeeProjectDetails)
*****BROKE*****
If IsNull(Me.txtWeeklyAllowanceLow) Then
'Create Predicate
strWhere = strWhere & " AND " & "[Wk/Allow] <= " & Me.txtWeeklyAllowanceHigh
ElseIf IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " & Me.txtWeeklyAllowanceLow
Else: strWhere = strWhere & " AND " & "[Wk/Allow] between " &
Me.txtWeeklyAllowanceLow & " AND " & Me.txtWeeklyAllowanceHigh
End If
Thanks
:
Damian that was very helpful, but I'm still not quite there.
First. Just as an FYI, the weekly allowance fields are part of a criteria
search so I don't want to make the fields mandatory.
Second. I was still able to use your code to get the search to work if I
fill out EITHER the txtWeeklyAllowanceLow control or the
txtWeeklyAllowanceHigh control, but when I use BOTH the search fails. By
fails I mean no errors, just pulls data that doesn't fit the criteria.
Also, I'm only a novice with code, but I omitted your strWhere =
"WeeklyAllowance....whatever" because I already have strWhere = "1=1" and
don't see how I could use it.
This leads me to my problem.. Your code uses;
strWhere = "WeeklyAllowance between " & Me.txtweeklyAllowanceLow & " and " &
me.txtweeklyAllowanceHigh
end if
How do I work around the fact that I already have strWhere = "1=1"
Thanks for the help!
:
Hi Justin,
Build your where clause like this:
dim strWhere as string
if isnull(me.txtWeeklyAllowanceLow) and isnull(me.txtWeeklyAllowanceHigh) then
msgbox "You must enter something"
elseif isnull(me.txtWeeklyAllowanceLow) then
strWhere = "WeeklyAllowance <= " & me.txtWeeklyAllowanceHigh
elseif isnull(me.txtWeeklyAllowanceHigh) then
strWhere = "WeeklyAllowance >= " & me.txtWeeklyAllowanceLow
else
strWhere = "WeeklyAllowance between " & me.txtWeeklyAllowanceLow & " and "
& me.txtWeeklyAllowanceHigh
end if
Then apply that where clause. Hope that helps.
Damian.
:
I have two txtboxes (txtWeeklyAllowanceLow, txtWeeklyAllowanceHigh) on a
search form that I would like to use to find all weekly allowances on a table
that fall within the range specified. Also if only a low amount is entered,
then I would like all allowances greater than the amount to display, or if
only a high amount then all allowances less than what is given. Any help with
how I should code this would be great.
thanks!
