I have an advance search form that utilizes queries. Right now I am trying to pinpoint specific data by entering a particular month. My Month filter does not seem to work properly and was wonder if anyone would be able to help.
Below is my code. ::::

DrawingNumberFilter = [Drawing Number Filter]
MonthFilter = [Month Filter]



whereStmnt = ""

'Drawing Number Filter
If DrawingNumberFilter <> "" Then
whereStmnt = "[DrawingNumber Field] like '*" & DrawingNumberFilter & "*'"
End If

'Month Filter
If MonthFilter <> "" Then
If whereStmnt <> "" Then whereStmnt = whereStmnt & " And "
whereStmnt = whereStmnt & "[LastOfWeekEnding] like "MonthFilter/*/year(now())"
End If

If whereStmnt <> "" Then DoCmd.ApplyFilter , whereStmnt

End Sub

I would do this a little different:



This is untested air code, but should get in going in the right direction.

HTH.

If I use that code than I still get an error. I am trying to pull all of Aprils Data by using a search criteria "April" (or any other month. Currently the "LastOfWeekEnding" is in date formate mm/dd/yyyy. How would I retrive all the records that are populated in April or any month for that matter? The day "dd" and the year "yyyy" can be wild cards if the search.


Edited by: seximexi6969 on Fri Jun 23 12:03:02 EDT 2006.

So you want the records to be pulled based on month and don't care what day of the month or the year that month fell into. In which case, I'd do:

whereStmnt = whereStmnt & " AND Month([LastOfWeekEnding]) =#" & MonthFilter & "#"

HTH.

That command is giving me this error: Syntax error (missing operator) in query expression 'AND Month([LastOFWeekEnding]) = #July#'

Here's my code for this part:

If MonthFilter <> "" Then
If whereStmnt <> "" Then whereStmnt = whereStmnt & " And "
whereStmnt = whereStmnt & " AND Month([LastOfWeekEnding]) =#" & MonthFilter & "#"
End If
If whereStmnt <> "" Then DoCmd.ApplyFilter , whereStmn


By the way thank you for all your help

What I think is happening is the Month(LastOfWeekEnding) results in a numberical value for the month (1 - 12) and you have July in your text box. You can either use a combo box to select the month using the bound column for 1 - 12 and the visible column to actually select the month. Or use the MonthName function and remove the # from the string. I personally would use the Combo Box option, then you don't have to worry about users misspelling the month name.

Edited by: dashiellx2000 on Fri Jun 23 14:20:04 EDT 2006.

I have tried that also, But I get the same error. Could there be a problem with this wexpression:

whereStmnt = whereStmnt & " AND Month([LastOfWeekEnding]) =#" & MonthFilter & "#"

Am I missing a bracket() somewhere? should the code be something like...just spectulating...

whereStmnt = whereStmnt & " AND (Month([LastOfWeekEnding]) =#" & MonthFilter & "#")

I got it...with this code:

If MonthFilter <> "" Then
If whereStmnt <> "" Then whereStmnt = whereStmnt & " And "
whereStmnt = whereStmnt & "Month([LastOfWeekEnding]) like '*" & MonthFilter & "*'"
End If

Thanks you so much for your help...you have been a big help....Thanks again for you inputs.

SM

I see you did get it to work using WildCards, but you should need them. I'm sorry and I forgot to explain that you need to get rid of the # in the string as well for this method.

whereStmnt = whereStmnt & " AND Month([LastOfWeekEnding]) =" & MonthFilter

HTH.

Edited by: dashiellx2000 on Fri Jun 23 14:37:53 EDT 2006.