Open Report with Date Parameters

[Gee]

I need the following report to open with date parameters.
I have the following code, but it doesn't quite work.
When an item is chosen from Modl (a list box) a box pops up asking for LowPop, then another for Start Year and then another for End Year.


Those last two aren't doing what they should. They should restrice the [Date] field to between the years entered as start and end.
I would like to put it in the "OpenReport" line, but don't think that's going to work.
Any help would be appreciated.

Private Sub Command27_Click()
 Dim varItm As Variant
    Dim ModelWhere As String
    Dim strQuery
    Dim LowPop As String
    Dim SDate As Date
    Dim EDate As Date
    Dim qdf As DAO.QueryDef
   
    If SysCmd(acSysCmdGetObjectState, acQuery, "CompactSUMQry") = acObjStateOpen Then
    DoCmd.Close acQuery, "CompactSUMQry"
    End If
        
    Set qdf = CurrentDb.QueryDefs("CompactSUMQry")
        
    LowPop = InputBox("Please Enter Minimum Population", "Population")
    SDate = InputBox("Enter Start Year")
    EDate = InputBox("Enter End Year")
    
    strQuery = "SELECT * FROM dbo_BAMtbl WHERE ((dbo_BAMtbl.Pop)>=" & LowPop & " AND "
   
    For Each varItm In Me.Modl.ItemsSelected
        If ModelWhere = "" Then
           ModelWhere = "[dbo_BAMtbl].[Model]=" & _
           Chr(34) & Me.Modl.Column(0, varItm) & Chr(34)
        Else
           ModelWhere = ModelWhere & " OR [dbo_BAMtbl].[Model]=" & _
           Chr(34) & Me.Modl.Column(0, varItm) & Chr(34)
        End If
    Next varItm
    
    strQuery = strQuery & "(" & ModelWhere & "));"
    
    qdf.SQL = strQuery
      
DoCmd.OpenReport "SUMrptCmpct", acViewPreview, , WhereCondition:="(" & [Date] & "  >=" & SDate & " And " & "<= EDate&"")"
    Set qdf = Nothing
End Sub

[pbaldy]

What is the data type of the date field (bad name by the way, as it can get confused with the Date() function)? It looks like you're comparing a date to just a year. If not, you have to repeat the field name:

FieldName >= Whatever And FieldName <= Whatever

[Gee]

Yes, I agree with the [Date] field, I would have chosen something else, but I didn't build the datasource.
The dbo_BAMtbl.Date is a date field, mm/dd/yyyy, I need the input in the inputboxes to be yyyy.
I understand that I need to say "FieldName >= Whatever And FieldName <= Whatever", I just have no clue on how to write it in the WhereCondition of the OpenReport Command.
The FieldName is dbo_BAMtbl.Date, but I need to steer it to the SDate and EDate.

[pbaldy]

If you just want to compare the year try:


DoCmd.OpenReport "SUMrptCmpct", acViewPreview, , "Year([Date]) >=" & SDate & " And Year([Date]) <= " & EDate

[Gee]

It did not work..It gave me Run-time Error '3075'
Syntax error (missing operator) in query expression 'Year([Date]) >=12:00:00AM And Year([Date])<=12:00:00Am

[pbaldy]

It doesn't look like the user entered a year for either variable.

[pbaldy]

Oh, and change your variable declarations to Integer, since you don't really want a date input.

[Gee]

I entered it myself...2011 and 2011.

[pbaldy]

Did you see:

Oh, and change your variable declarations to Integer, since you don't really want a date input.

[Gee]

I don't know how to do that.
Can you tell me?

[pbaldy]

Change these to Integer:


Dim SDate As Date
Dim EDate As Date

[Gee]

YES!!
Thank you SO much!

[pbaldy]

Excellent, glad it worked!