Filter Question

[fenhow]

Hi, I have a continuous form. In the header I have two unbound txtboxes.
StartDate and EndDate.

In my data I have a field called DueDate

What I am trying to do is if there is a value in StartDate and EndDate filter DueDate between those values.

The code I have does not work.. Can anyone help?

Private Sub Command36_Click()
Me.Filter = "[DueDate]= Between [Forms]![frm_Payment_Manager]![StartDate] AND [Forms]![frm_Payment_Manager]![EndDate]"
Me.FilterOn = True

Me.Filter = ""
Me.FilterOn = False

End Sub

Thanks.

 

[jdraw]

fenhow,

The code I have does not work.. Can anyone help?

That does not help a reader who is interested in helping you.
What doesn't work? Do you get an Error? Is there an error number?

Do you know how to debug/step debug/break points? Did you try any- and results?

It seems you want the values from some of the controls to be part of your filter.

Me.Filter = "[DueDate]= Between [Forms]![frm_Payment_Manager]![StartDate] AND [Forms]![frm_Payment_Manager]![EndDate]"

The whole statement is a string --you aren't getting any values from your form's controls.

[DueDate]= Between

=Between NO!!
It's either = to some value OR Between 2 values

Try:

"[DueDate] Between #" & [Forms]![frm_Payment_Manager]![StartDate] & "# AND #" & [Forms]![frm_Payment_Manager]![EndDate] & "#"

where

"[DueDate] Between #" & [Forms]![frm_Payment_Manager]![StartDate] & "# AND #" & [Forms]![frm_Payment_Manager]![EndDate] & "#"

blue is a string
orange is a value

Also, if you want to filter your data, you have to turn it ON

Me.FilterON = TRUE


Me.Filter = "" <---- resets the filter to a ZLS

You can turn the filter Off with Me.FilterOn =False

 

[fenhow]

Thank you so much for assisting. I apologize.
I tried what you sent and when I compile I get a "Sub or Function" not defined error.
My code looks like this now.

Private Sub Command64_Click()
Me.Filter = "[DueDate] Between #" & [Forms]![frm_Payment_Manager]![StartDate] & "# AND #" & [Forms]![frm_Payment_Manager]![EndDate]
where "[DueDate] Between #" & [Forms]![frm_Payment_Manager]![StartDate] & "# AND #" & [Forms]![frm_Payment_Manager]![EndDate] & "#"

Me.FilterOn = True

Me.Filter = ""
Me.FilterOn = False
End Sub

 

[jdraw]

Can you post a copy of the database? Only enough records to show the problem.
Compact and repair, then zip.

Not enough info to isolate the issue.

Form recordsource? Controls?

 

[fenhow]

I would love to but it is a very large DB with a lot of moving parts. I appreciate your help.
Fen

 

[jdraw]

I have mocked up a filter sample in the attached database.
Note: My regional settings Canada show dates in DD/MM/YYYY format - and that is true in the subform display.

When entering a DueDate --that format is USA/Access MM/DD/YYYY

Good luck.