RePost- Currency Range

[Justin83716]

Damian was helping me with this, and I thought that we had it. However it now
appears I was wrong. So I'm re-posting my problem.

I have a form that is being used to select criteria for a search. One of the
criteria I have is a weekly allowance criteria with a low and high text box.
I would like the user to be able to enter either a low amount or high amount
or both, and have the results fall within the range specified. With the help
of Damian I have accomplished this. The problem now is that when no criteria
is selected I want the search to ignore the fields and move to the next
criteria. If no criteria is specified anywhere I want ALL records to be
returned. As this is a very small DB this should not be any problem. Here is
my code so far.

Private Sub cmdSearch_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String

strWhere = "1=1 "

'If Weekly Allowance (tblEmployeeProjectDetails)
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 for all the help...Again!

 

[SteveS]

Justin,

It looks like the logic is backwards....

First, you have to check if both controls have values (ie not null)

If both *do not* have values, then you check if a value is entered in the
Low (or high) text box. If that text box is empty (null), then you check the
other text box.

To check if the text boxes are not null (empty), you use the (you guessed
it) the NOT operator. (see help)

So the code (as much as you posted) would look like this:

!! watch for line wrap !!
I added comments and white space for ease of reading

'--------------------------
Private Sub cmdSearch_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String

strWhere = "1=1 "

' are values entered in both controls?
If Not IsNull(Me.txtWeeklyAllowanceLow) And Not
IsNull(Me.txtWeeklyAllowanceHigh) Then

'this should be one line
strWhere = strWhere & " AND " & "[Wk/Allow] between " &
Me.txtWeeklyAllowanceLow & " AND " & Me.txtWeeklyAllowanceHigh

' is a value entered in txtWeeklyAllowanceLow?
ElseIf Not IsNull(Me.txtWeeklyAllowanceLow) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow

' is a value entered in txtWeeklyAllowanceHigh?
ElseIf Not IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh

' OK, no values entered
' the ELSE is optional - can be deleted unless you want an
' error message
' Else
' msgbox "Enter at least one value"

End If

'-----------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Damian was helping me with this, and I thought that we had it. However it now
appears I was wrong. So I'm re-posting my problem.

I have a form that is being used to select criteria for a search. One of the
criteria I have is a weekly allowance criteria with a low and high text box.
I would like the user to be able to enter either a low amount or high amount
or both, and have the results fall within the range specified. With the help
of Damian I have accomplished this. The problem now is that when no criteria
is selected I want the search to ignore the fields and move to the next
criteria. If no criteria is specified anywhere I want ALL records to be
returned. As this is a very small DB this should not be any problem. Here is
my code so far.

Private Sub cmdSearch_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String

strWhere = "1=1 "

'If Weekly Allowance (tblEmployeeProjectDetails)
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 for all the help...Again!

 

[Justin83716]

Thanks for the help. I have tried using the code you have below, and it works
great except that when I try and put a value in BOTH low and high fields I
get and error #2448 "You can't assign a value to this object." The debugger
highlights the below portion of code:

Debug.Print strWhere
Me.fsubRecordSearch.Form.Filter = strWhere ***Highlighted Line***
Me.fsubRecordSearch.Form.FilterOn = True
End If
End Sub

Also when I print to immediate window (Debug.Print strWhere) the code seems
like it's working.

1=1 AND [Wk/Allow] betweeen 500 AND 1000

Thanks again.

Justin,

It looks like the logic is backwards....

First, you have to check if both controls have values (ie not null)

If both *do not* have values, then you check if a value is entered in the
Low (or high) text box. If that text box is empty (null), then you check the
other text box.

To check if the text boxes are not null (empty), you use the (you guessed
it) the NOT operator. (see help)

So the code (as much as you posted) would look like this:

!! watch for line wrap !!
I added comments and white space for ease of reading

'--------------------------
Private Sub cmdSearch_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String

strWhere = "1=1 "

' are values entered in both controls?
If Not IsNull(Me.txtWeeklyAllowanceLow) And Not
IsNull(Me.txtWeeklyAllowanceHigh) Then

'this should be one line
strWhere = strWhere & " AND " & "[Wk/Allow] between " &
Me.txtWeeklyAllowanceLow & " AND " & Me.txtWeeklyAllowanceHigh

' is a value entered in txtWeeklyAllowanceLow?
ElseIf Not IsNull(Me.txtWeeklyAllowanceLow) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow

' is a value entered in txtWeeklyAllowanceHigh?
ElseIf Not IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh

' OK, no values entered
' the ELSE is optional - can be deleted unless you want an
' error message
' Else
' msgbox "Enter at least one value"

End If

'-----------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Damian was helping me with this, and I thought that we had it. However it now
appears I was wrong. So I'm re-posting my problem.

I have a form that is being used to select criteria for a search. One of the
criteria I have is a weekly allowance criteria with a low and high text box.
I would like the user to be able to enter either a low amount or high amount
or both, and have the results fall within the range specified. With the help
of Damian I have accomplished this. The problem now is that when no criteria
is selected I want the search to ignore the fields and move to the next
criteria. If no criteria is specified anywhere I want ALL records to be
returned. As this is a very small DB this should not be any problem. Here is
my code so far.

Private Sub cmdSearch_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String

strWhere = "1=1 "

'If Weekly Allowance (tblEmployeeProjectDetails)
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 for all the help...Again!