Filter form with currency and date betweens problems

[d9pierce1]

TestFilterSerch.zip

Good Evening All,


I have attached a sample db (above).
This is my final search form for my db.... thank the heavens.
I have two problems which i have not been able to solve and would appriciate some assistance in obtaining a solution.
There is one form Form2, sample data, and all filters work well except my two problems.

First Problem:
The FilterTransAmount is currency data type and not real clear on how to filter that.

Second Problem:
I Have a field in tbl/qry called TransDate and that is the date of transaction.
On form I have two filters (BeginDate and EndDate)
So, what i am trying to accomplish here is when i put a date in FilterBeginDate it starts filtering from that date (Shhow all Records from that date on) and
when i put a Date in the FilterEnd Date it would filter form list to be between BeginDate and End Date (Showing records between Begin and End).

I know this is a lot to ask but would appriciate some assistance with this.
Thanks
Dave

[davegri]

for the dates:

   'check FilterBeginDate
    Forms!form2.FilterBeginDate.SetFocus
    If Len(Forms!form2.FilterBeginDate.Text) & "" > 0 Then
      strWhere = strWhere & "([TransDate] >= #" & Forms!form2.FilterBeginDate.Text & "#) And "
    End If


   'check FilterEndDate
    Forms!form2.FilterEndDate.SetFocus
    If Len(Forms!form2.FilterEndDate.Text) & "" > 0 Then
      strWhere = strWhere & "([TransDate] <= #" & Forms!form2.FilterEndDate.Text & "#) And "
    End If

for the currency, I don't know what LIKE $20.00 is. Is it like $22 ? or $2200.00 ? Numbers should be = , not LIKE

   'check FilterTransAmount
    Forms!form2.FilterTransAmount.SetFocus
    If Len(Forms!form2.FilterTransAmount.Text) & "" > 0 Then
      strWhere = strWhere & "([TransAmount] =" & CCur(Forms!form2.FilterTransAmount.Text) & ") And "
    End If

and for all the events in the search controls, don't use on_change, use after_update and forget the cursor positioning:

Private Sub cboFilterTransMethod_afterupdate()
    Call tListBoxFilter
End Sub


Private Sub CboFilterToAccount_afterupdate()
    Call tListBoxFilter
End Sub


Private Sub cboFilterFromAccount_afterupdate()
    Call tListBoxFilter
End Sub


Private Sub FilterEndDate_afterupdate()
    Call tListBoxFilter
     
'    Me.FilterEndDate.SetFocus
'    With Me.FilterEndDate
'        .SelStart = Len(.Text)
'    End With
End Sub


Private Sub FilterTransCode_afterupdate()
    Call tListBoxFilter
        
'    Me.FilterTransCode.SetFocus
'    With Me.FilterTransCode
'        .SelStart = Len(.Text)
'    End With


End Sub


Private Sub FilterBeginDate_afterupdate()
    Call tListBoxFilter
     
'    Me.FilterBeginDate.SetFocus
'    With Me.FilterBeginDate
'        .SelStart = Len(.Text)
'    End With
End Sub


Private Sub FilterTransAmount_afterupdate()
    Call tListBoxFilter
     
'    Me.FilterTransAmount.SetFocus
'    With Me.FilterTransAmount
'        .SelStart = Len(.Text)
'    End With
End Sub

[d9pierce1]

Hi davegri,
First thank you for taking your time with this.
I attached the change you gave me and the resutls are as follows.

Trans Amunt once entered and you leave field, it filters to no records.
Example if I enter 5000 the list box goes blank.

Second, the date fields do work one at a time but not between if that makes sense?
And if you select dates begin and end, list box goes blank also.

Definately getting closer....

TestFilterSerch (2).zip

Thanks
Dave

[d9pierce1]

Hi davegri
That fixed the currency issue, just dates....
Thanks
Dave

[d9pierce1]

Thaks so much davegri,
This works so nicely now... made changes to date and cur
You are just a wonderful person...
Merry Christmas
Dave

[davegri]

Hi davegri,
First thank you for taking your time with this.
I attached the change you gave me and the resutls are as follows.

Trans Amunt once entered and you leave field, it filters to no records.
Example if I enter 5000 the list box goes blank.

Second, the date fields do work one at a time but not between if that makes sense?
And if you select dates begin and end, list box goes blank also.

Definately getting closer....

TestFilterSerch (2).zip

Thanks
Dave

You didn't incorporate any of the code changes in module1 that I indicated in post#2 for dates or currency.

[davegri]

Ah OK, our posts crossed each other. Glad it's working now.

[d9pierce1]

Thank you so much davegri
This saved me so much time as I had no idea where to even start searching for this.
Youre fantastic
Dave

ps...will mark as solved