Form Filter in VBA

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 23:45
Joined
Feb 5, 2019
Messages
331
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
 
to clarify what you want is to filter the records to show the current month? (Edit- current week) If so there are simpler methods
 
I never use the table name in these situations. This also assumes you won’t have repeated field names from more than one table.
 
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""
 
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
 
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.
 

Users who are viewing this thread

Back
Top Bottom