Question Filter by Parameter

[MrNooby]

I would like to include one of my parameter which I use in VB to Filter but I just can't get it to work. I can filter if I manualy enter date in Filter but don't know how with paramater.

Example code:

mydate = "3/3/2010"
Me.Filter = "[Contacts]![Date]  < " & mydate
Me.FilterOn = True
Me.Requery

What is wrong here? How should I write mydate at Me.Filter?

 

[SOS]

You don't need the requery when you set the filter On.

Dim mydate As Date
mydate = #3/3/2010#
   Me.Filter = "[Date] <#" & mydate & "#"
   Me.FilterOn = True

And DATE should not be used as a field name. It is a RESERVED ACCESS WORD and those should not be used for names of fields or objects.

 

[MrNooby]

I would like to filter days which are by the end of the week. I managed to get date of last day of the week but Filter doesn't work. This is my code now:

 Dim endOfTheWeek As Date
' endOfTheWeek gets value of last day of the week - in my case 28.2.2010
 endOfTheWeek = Format((DateAdd("d", 8 - DatePart("w", Now(), vbSunday), Now())), "dd/mm/yyyy")
 Me.Filter = "[Contacts]![Date contact] < #" & endOfTheWeek & "#" 
 Me.FilterOn = True

And if i click on the button I get error:

Run-time error '3000':
Reserved error (-3201); there is no message for this error.

 

[SOS]

Again, you don't include the table name and the field must be in the form's recordset:

Me.Filter = "[Contacts]![Date contact] < #" & endOfTheWeek & "#"

should be

Me.Filter = "[Date contact] < #" & endOfTheWeek & "#"

 

[MrNooby]

I get the same error message if I use it like this.

 

[SOS]

Any chance of getting an upload of the database? I think we're at the point of needing to actually see what is happening. If not, that's about all the help I can give because I do much better when seeing the problem than guessing. I try to guess and it works out many times but sometimes it just isn't there for me until I see what is happening. Your call.

 

[MrNooby]

I will try to ZIP the things and upload them.

I now added value of endOfTheWeek as caption on my button to see what I really get:

MyTestButton.Caption = endOfTheWeek

And his caption becomes 28.2.2010

is this right? I think that the rpoblem is because it's not 28/2/2010, but I even formated it to "dd/mm/yyyy"?

 

[MrNooby]

OK, I am attaching the basic access file for this (click on "Button for filter this week").

I hope you'll be able to find out what's causing this problem.

 

[SOS]

It works fine for me, so I'm assuming that your regional settings are playing havoc with it. Set the format of the data to US date format ( mm/dd/yyyy) when doing this and you should be good to go.

 

[MrNooby]

Where can I do that? Would that effect how dates are shown in the table? I need to have them as dd.mm.yyyy, how would I be able to use that in my code?

How come Format to "dd/mm/yyyy doesn't have any effect? Maybe if I change endOfTheWeek from date to sring?

 

[SOS]

You would do it in the function:

endOfTheWeek = Format((DateAdd("d", 8 - DatePart("w", Date, vbSunday), Date)), "#mm\/dd\/yyyy#")

And I would change the NOW to DATE because NOW has a time element that you don't want.

 

[MrNooby]

Yeah, I just figured our about \/

I am now using:

endOfTheWeek = Format((DateAdd("d", 8 - DatePart("w", Date, vbSunday), Date)), "dd\/mm\/yyyy")
Me.Filter = "[Contact date] = #" & endOfTheWeek & "#"

And it works

Thanks for your help SOS.

 

[SOS]

Glad we could help.