Form that load list ->date filter

[mfaqueiroz]

Hello,

I doing one form that shows all the machine that don't work since a certain date. The user choose trought one combo box the month and the year and after click int he button "update table", after that i want to show in a list the filter table.
As the following image.


So i have write the code:

Private Sub LoadTable_Click()

DoCmd.SetWarnings False
    
    Dim strsql As String
 
    Month11 = Me.Month1
    Year11 = Me.Year1
    

 strsql = "SELECT * FROM Historic WHERE year([Date])< '& year11 &' or  (year(Date))='&year11&' and month(Date)< '&Month11&' );"

Me!List.RowSource = strsql
        
Me.Refresh
    
End Sub

but isn't working, do you know where i'm falling?

thanks

 

[sneuberg]

First you shouldn't mix single and double quotes the way you are. As it is '& year 11 &' is taken to be literally '& year 11 &' rather than concatenating the value in. These need to be double quotes, Then you have Date in brackets and then not. Assuming it's a field name (Bad choice for that) it needs to be in brackets in all places to to make sure it is not confused in the date function. Then

year([Date])< '& year11 &' or (year(Date))='&year11&'

can be simplified by using the <= operator. So maybe this is what you want.

strsql = "SELECT * FROM Historic WHERE Year([Date]) <= " & year11 & " And Month([Date]) < " & Month11

Assuming the combo boxes have numbers for the months (1-12) and years (e.g. 2016)

 

[mfaqueiroz]

Thanks SneuBerg.
It's solved
!!

 

[Tieval]

Just a small tip for that function, you need to switch the warnings back on when you are finished so DoCmd.SetWarnings True should be placed before the End Sub otherwise they will stay switched off.