Solved Form Filter in VBA

[MattBaldry]

Hi all,

I am trying to apply a form filter on a continuous form on a reset button. When I save the form filter, it gives me the below.

(Year(tblAppointmentList.AppointmentDate)=Year(Date()) And DatePart("ww",tblAppointmentList.AppointmentDate,0)=DatePart("ww",Date(),0))

But when I apply this to the button via VBA, I get the "can't find the field '|1'" error. I assume this is something to do with the DatePart section, but I cannot get it to work.

Can anyone point me into the right direction please?

~Matt

 

[CJ_London]

to clarify what you want is to filter the records to show the current month? (Edit- current week) If so there are simpler methods

 

[DHookom]

I never use the table name in these situations. This also assumes you won’t have repeated field names from more than one table.

 

[RonPaii]

If you are looking for the current month, use Month instead of DatePart.
(Year(tblAppointmentList.AppointmentDate)=Year(Date()) And Month(tblAppointmentList.AppointmentDate)=Month(Date()))

"ww" in DatePart returns the current week, not month.

As for the error you many need to escape the quotes in "ww" i.e. ""ww""

 

[CJ_London]

to preserve the use of indexes for better performance, better to not use a formula on the target field.
Assuming your week starts on a Sunday and your date field does not contain a time element, try

AppointmentDate between date()-weekday(date(),1) and date()-weekday(date(),1)+7

If your week starts on a Monday, change the 1 to 2 in the two weekday functions

If your AppointmentDate does contain a time element
then you need to use
AppointmentDate between date()-weekday(date(),1) and date()-weekday(date(),1)+8

I never use the table name in these situations.

I suspect the OP is using the QBE

 

[DHookom]

to preserve the use of indexes for better performance, better to not use a formula on the target field.
Assuming your week starts on a Sunday and your date field does not contain a time element, try

AppointmentDate between date()-weekday(date(),1) and date()-weekday(date(),1)+7

If your week starts on a Monday, change the 1 to 2 in the two weekday functions

If your AppointmentDate does contain a time element
then you need to use
AppointmentDate between date()-weekday(date(),1) and date()-weekday(date(),1)+8



I suspect the OP is using the QBE

The OP stated “When I save the form filter” which isn’t the same as QBE.

 

[CJ_London]

good point

 

[MattBaldry]

I never use the table name in these situations. This also assumes you won’t have repeated field names from more than one table.

I wouldn't either, that is the filter the Access generated for me, but it doesn't work

to clarify what you want is to filter the records to show the current month? (Edit- current week) If so there are simpler methods

Yes, I want the continuous form to display the current week every time it is opened, or if the reset button is clicked default back to this.

~Matt

 

[MattBaldry]

If you are looking for the current month, use Month instead of DatePart.
(Year(tblAppointmentList.AppointmentDate)=Year(Date()) And Month(tblAppointmentList.AppointmentDate)=Month(Date()))

"ww" in DatePart returns the current week, not month.

As for the error you many need to escape the quotes in "ww" i.e. ""ww""

I am trying to filter by the current week as default, adding the ""ww"" does not work though, I have tried this and get the "Expected list separator" error.

~Matt

 

[MattBaldry]

I never use the table name in these situations. This also assumes you won’t have repeated field names from more than one table.

I wouldn't either, this is just the filter that access generated when I filtered it using my command buttons. But pasting this into VBA to load/reset doesn't work.

~Matt

 

[arnelgp]

how did you saved the filter?

 

[Gasman]

I am trying to filter by the current week as default, adding the ""ww"" does not work though, I have tried this and get the "Expected list separator" error.

~Matt

This works for me?

DoCmd.OpenForm "frmtbldaily", , , "Format([DailyDate], 'wwyy') = Format(Date(), 'wwyy')"

 

[MattBaldry]

how did you saved the filter?

I just filtered the form and then saved it. It then added it to the filter field in the form properties.

~Matt

 

[CJ_London]

per post #5
AppointmentDate between date()-weekday(date(),1) and date()-weekday(date(),1)+7

you could of course just apply that as a criteria to the form's recordsource

 

[MattBaldry]

This works for me?

DoCmd.OpenForm "frmtbldaily", , , "Format([DailyDate], 'wwyy') = Format(Date(), 'wwyy')"

Thank you, this worked. I had to edit it for the reset button as below, but it works.

Me.Form.Filter = "Format([AppointmentDate], 'wwyy') = Format(Date(), 'wwyy')"

~Matt

 

[MattBaldry]

per post #5
AppointmentDate between date()-weekday(date(),1) and date()-weekday(date(),1)+7

you could of course just apply that as a criteria to the form's recordsource

I tried this approach but it didn't work. Loading the form with the filter wasn't the main issue, it was creating a reset. But I now have a working solution

~Matt