Problem with filter string 2

Moss100 · Aug 30, 2019

Hello,

I have two filter strings which both work.

I want to combine bioth of them, but it then fails. Can someone spot the probelm. Thanks Mark

1st

Code:

Me.Form.Filter = "Trans_Date Between #" & Me.txt_Filter_Date_Start & "# AND #" & Me.txt_Filter_Date_End & "#"

2nd

Code:

"[Trans_Category_ID_Link] = " & Me.cboFilter_Category

combined

Code:

Me.Form.Filter = Me.Form.Filter = "Trans_Date Between #" & Me.txt_Filter_Date_Start & "# AND #" & Me.txt_Filter_Date_End & "#" And "[Trans_Category_ID_Link] = " & Me.cboFilter_Category

Andrzejek · Aug 30, 2019

Do yourself a favor and do this:

Code:

Dim strFilter As String

strFilter = "Trans_Date Between #" & Me.txt_Filter_Date_Start & "# AND #" & Me.txt_Filter_Date_End & "#" And "[Trans_Category_ID_Link] = " & Me.cboFilter_Category 

Debug.Print strFilter

Me.Form.Filter = strFilter

You will quick discover that your Filter should look like:
[tt]
strFilter = "[red]([/red]Trans_Date Between #" & Me.txt_Filter_Date_Start & _
"# AND #" & Me.txt_Filter_Date_End & "#[red])[/red] And [Trans_Category_ID_Link] = " & Me.cboFilter_Category [/tt]

---- Andy

There is a great need for a sarcasm font.

Moss100 · Aug 30, 2019

Thank you - all is almost working.

The problem I have is that although the code is working, the dates are wrong (i think my code is seeing the dates in US format).

For example I have a start date in the textbox as 01/08/2018 - this returns values from 8th Jan 2018. I'm wanting values from 1st August 2018.

Can you help? Thanks you Mark

My code:

Code:

If Me.chk_Filter_Between_Date = True Then
    Dim strFilter As String
    
    Me.Form.Filter = ""
    Me.Form.FilterOn = False
    strFilter = "(Trans_Date Between #" & Me.txt_Filter_Date_Start & _
    "# AND #" & Me.txt_Filter_Date_End & "#) And [Trans_Category_ID_Link] = " & Me.cboFilter_Category
    Me.Form.Filter = strFilter
    Me.Form.FilterOn = True
    Me.Form.OrderBy = "[Trans_Date] desc, [trans_id] desc"
    Me.Form.OrderByOn = True
    DoCmd.GoToControl "txt_CURSOR_Header"
 End If

Andrzejek · Aug 30, 2019

You may try:

Code:

If Me.chk_Filter_Between_Date = True Then
    Dim strFilter As String[blue]
    Dim datStart As Date
    Dim datEnd As Date

    datStart = CDate(Me.txt_Filter_Date_Start)[/blue]
    [green]'or you may need to try[/green][blue]
    datStart = CDate(Format(Me.txt_Filter_Date_Start, "DD/MM/YYYY"))[/blue]

    Me.Form.Filter = ""
    Me.Form.FilterOn = False
    strFilter = "(Trans_Date Between #" & [blue]datStart[/blue] & _
    "# AND #" & Me.txt_Filter_Date_End & "#) And [Trans_Category_ID_Link] = " & Me.cboFilter_Category
    Me.Form.Filter = strFilter
    Me.Form.FilterOn = True
    Me.Form.OrderBy = "[Trans_Date] desc, [trans_id] desc"
    Me.Form.OrderByOn = True
    DoCmd.GoToControl "txt_CURSOR_Header"
 End If

When I deal with Dates, I do NOT allow users to type dates, I give them a Calendar and have them select a date. If I need to keep any date, I declare the variable As Date. A lot easier to deal with it because a date is just a number which you can Format any way you wish.

---- Andy

There is a great need for a sarcasm font.

Moss100 · Sep 2, 2019

Thanks for your help with this.

Unfortunately your code does not solve this.

If i have a start date of 01/01/2019 and and end date of 02/09/2019, it is returning results for the period:

01/01/2019 - 09/02/2019.

Any ideas?? Thanks Mark

Andrzejek · Sep 3, 2019

Could you copy-and-paste your code?

---- Andy

There is a great need for a sarcasm font.

Moss100 · Sep 3, 2019

Thanks - (I tried both suggestions)
Mark

Code:

 If Me.chk_Filter_Between_Date = True Then
    Dim strFilter As String
    Dim datStart As Date
    Dim datEnd As Date

    datStart = CDate(Me.txt_Filter_Date_Start)
    datEnd = CDate(Me.txt_Filter_Date_End)
    'or you may need to try
    'datStart = CDate(Format(Me.txt_Filter_Date_Start, "DD/MM/YYYY"))
    'datEnd = CDate(Format(Me.txt_Filter_Date_End, "DD/MM/YYYY"))

    Me.Form.Filter = ""
    Me.Form.FilterOn = False
    strFilter = "(Trans_Date Between #" & datStart & _
    "# AND #" & Me.txt_Filter_Date_End & "#) And [Trans_Category_ID_Link] = " & Me.cboFilter_Category
    Me.Form.Filter = strFilter
    Me.Form.FilterOn = True
    Me.Form.OrderBy = "[Trans_Date] desc, [trans_id] desc"
    Me.Form.OrderByOn = True
    DoCmd.GoToControl "txt_CURSOR_Header"
 End If

Andrzejek · Sep 3, 2019

When I have start date of 01/01/2019 and end date of 02/09/2019 with the code:

Code:

Dim strFilter As String
Dim [blue]datStart[/blue] As Date
Dim [blue]datEnd [/blue]As Date

[blue]datStart[/blue] = CDate(Me.txt_Filter_Date_Start)
[blue]datEnd[/blue] = CDate(Me.txt_Filter_Date_End)

strFilter = "(Trans_Date Between #" & [blue]datStart[/blue] & _
    "# AND #" & [blue]datEnd[/blue] & "#) And Trans_Category_ID_Link = " & Me.cboFilter_Category
    
Debug.Print strFilter

I get the Filter:
[tt](Trans_Date Between #[blue]1/1/2019[/blue]# AND #[blue]2/9/2019[/blue]#) And Trans_Category_ID_Link = 1[/tt]

How do you get your Dates into the two text boxes? Users type them?

---- Andy

There is a great need for a sarcasm font.

dhookom · Sep 3, 2019

All dates in queries must be in US or some other format that is NOT d/m/y. You might want to review Allen Browne's page on international dates.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

Moss100 · Sep 3, 2019

Yes the dates come from unbound textboxes on the form.

I have even tried taking out the extra criteria so its only filtering on the dates.

So when i input 01/01/2019 for the start and 02/09/2019 for the end, it returns all records between 01/01/2019 and 09/02/2019

The code is:

Code:

'With Date Filter

 If Me.chk_Filter_Between_Date = True Then
    Dim strFilter As String
    Dim datStart As Date
    Dim datEnd As Date
    
    datStart = CDate(Format(Me.txt_Filter_Date_Start, "DD/MM/YYYY"))
    datEnd = CDate(Format(Me.txt_Filter_Date_End, "DD/MM/YYYY"))

    
    Me.Form.Filter = ""
    Me.Form.FilterOn = False
    strFilter = "(Trans_Date Between #" & datStart & _
    "# AND #" & Me.txt_Filter_Date_End & "#) "
    Me.Form.Filter = strFilter
    Me.Form.FilterOn = True
    Me.Form.OrderBy = "[Trans_Date] desc, [trans_id] desc"
    Me.Form.OrderByOn = True
    DoCmd.GoToControl "txt_CURSOR_Header"
 End If

dhookom · Sep 3, 2019

Change your code to use the US date format:

Code:

    datStart = CDate(Format(Me.txt_Filter_Date_Start, "[highlight #FCE94F]MM/DD/YYYY[/highlight]"))
    datEnd = CDate(Format(Me.txt_Filter_Date_End, "[highlight #FCE94F]MM/DD/YYYY[/highlight]"))

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

Andrzejek · Sep 3, 2019

And instead of:

Code:

strFilter = "(Trans_Date Between #" & datStart & _
    "# AND #" & [red]Me.txt_Filter_Date_End[/red] & "#) "

use:

Code:

strFilter = "(Trans_Date Between #" & datStart & _
    "# AND #" & [red]datEnd[/red] & "#) "

That's what I was trying to point in my previous post.

---- Andy

There is a great need for a sarcasm font.

dhookom · Sep 3, 2019

Good catch Andy. I missed that.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

Moss100 · Sep 3, 2019

That's brilliant guys - thanks so much - working great

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Problem with filter string 2

Moss100

Technical User

Andrzejek

Programmer

Moss100

Technical User

Andrzejek

Programmer

Moss100

Technical User

Andrzejek

Programmer

Moss100

Technical User

Andrzejek

Programmer

dhookom

Programmer

Moss100

Technical User

dhookom

Programmer

Andrzejek

Programmer

dhookom

Programmer

Moss100

Technical User

Similar threads

Part and Inventory Search

Sponsor