Checking for currency value between range

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! 
0
Utf
2/26/2007 9:57:03 PM
access.formscoding 7493 articles. 0 followers. Follow

5 Replies
611 Views

Similar Articles

[PageSpeed] 46

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


"Justin83716" wrote:

> 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!
> 
> "Damian S" wrote:
> 
> > 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.
> > 
> > "Justin83716" wrote:
> > 
> > > 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! 
0
Utf
2/26/2007 11:50:08 PM
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.

"Justin83716" wrote:

> 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
> 
> 
> "Justin83716" wrote:
> 
> > 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!
> > 
> > "Damian S" wrote:
> > 
> > > 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.
> > > 
> > > "Justin83716" wrote:
> > > 
> > > > 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! 
0
Utf
2/26/2007 11:58:08 PM
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" wrote:

> 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.
> 
> "Justin83716" wrote:
> 
> > 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
> > 
> > 
> > "Justin83716" wrote:
> > 
> > > 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!
> > > 
> > > "Damian S" wrote:
> > > 
> > > > 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.
> > > > 
> > > > "Justin83716" wrote:
> > > > 
> > > > > 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! 
0
Utf
2/27/2007 12:13:13 AM
Hi again,

Highlighting that line means that it's not sure what to do with strWhere - 
have you defined it at this point?  The formatting of your fields shouldn't 
matter to you, but you could (just to be on the safe side) do something like 
this:  
format(me.txtWeeklyAllowanceLow, "0.00") 
when you are building your SQL String.

Damian.

"Justin83716" wrote:

> 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" wrote:
> 
> > 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.
> > 
> > "Justin83716" wrote:
> > 
> > > 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
> > > 
> > > 
> > > "Justin83716" wrote:
> > > 
> > > > 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!
> > > > 
> > > > "Damian S" wrote:
> > > > 
> > > > > 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.
> > > > > 
> > > > > "Justin83716" wrote:
> > > > > 
> > > > > > 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! 
0
Utf
2/27/2007 12:52:12 AM
Thanks for all the help Damian. I have it working and couldn't have done it 
without you.

"Damian S" wrote:

> Hi again,
> 
> Highlighting that line means that it's not sure what to do with strWhere - 
> have you defined it at this point?  The formatting of your fields shouldn't 
> matter to you, but you could (just to be on the safe side) do something like 
> this:  
> format(me.txtWeeklyAllowanceLow, "0.00") 
> when you are building your SQL String.
> 
> Damian.
> 
> "Justin83716" wrote:
> 
> > 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" wrote:
> > 
> > > 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.
> > > 
> > > "Justin83716" wrote:
> > > 
> > > > 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
> > > > 
> > > > 
> > > > "Justin83716" wrote:
> > > > 
> > > > > 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!
> > > > > 
> > > > > "Damian S" wrote:
> > > > > 
> > > > > > 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.
> > > > > > 
> > > > > > "Justin83716" wrote:
> > > > > > 
> > > > > > > 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! 
0
Utf
2/27/2007 4:43:35 PM
Reply:

Similar Artilces: