Filter not working (AND)

[David Ball]

Hi,

I have a report that I am trying to filter with VBA code.

After running the code I get the following in the Property Sheet, Data, Filter row:

[Project] IN('KLE323 Piperacks EC Project') AND [Reporting Week Ending] IN(#2/06/2017#)

The report is not working and always stays blank with no records. If I manually modify the string to just [Project] IN('KLE323 Piperacks EC Project') the report opens correctly. If I manually adjust it to just [Reporting Week Ending] IN(#2/06/2017#) the report also opens correctly.

I have double checked that there are records that meet both these criteria.

Is there something wrong with the way I am expressing the "AND"?

Thanks very much

Dave

 

[David Ball]

Actually, when I adjust it to I manually adjust it to just [Reporting Week Ending] IN(#2/06/2017#) the report does not work properly. The report opens with data in it but it is not filtered to just 02/06/2017, it shows all dates.

Looks like there is something wrong with my date format? Any ideas?

 

[Galaxiom]

Date format should be mm/dd/yyyy

 

[David Ball]

Found the problem. I had to format the date to the ridiculous American date format.

For Each varItem In Me.lsbWEdate.ItemsSelected
strWEdate = strWEdate & ",#" & Format(Me.lsbWEdate.ItemData(varItem) _
, "mm\/dd\/yyyy") & "#"

 

[Minty]

Have a read here for all the date gotcha's http://allenbrowne.com/ser-36.html including some functions to save all the repetitive conversion typing.

 

[Gasman]

See where that constant for date format starts to come in handy?

Found the problem. I had to format the date to the ridiculous American date format.

For Each varItem In Me.lsbWEdate.ItemsSelected
strWEdate = strWEdate & ",#" & Format(Me.lsbWEdate.ItemData(varItem) _
, "mm\/dd\/yyyy") & "#"