Multiple items on a form

[drake]

New user to Access and having some trouble putting or asking the user for more than one item on a form. I need to ask the user for a date range, use a list box to ask the user for [HARV] which is a list of harvesters and have the user input a variable which will be used in a formula in a table/query. Here is the code I have so far but the date range is not working:

Private Sub Command0_Click()
Dim strReport As String 'Name of report to open.
Dim strField1 As String 'Name of your Harvest field.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.

strReport = "Contractor's Weekly Summary Report"
strField = "HARV"
strField1 = "DATE"


' Debug.Print strWhere 'For debugging purposes only.
If Not IsNull(Me.cboHARV) Then 'block combo box
strWhere = "[HARV] = """ & Me.cboHARV & """"
End If


If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere1 = strField1 & " < " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere1 = strField1 & " > " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere1 = strField1 & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If


DoCmd.OpenReport strReport, acViewPreview, , strWhere
Me.Visible = False
End Sub



I really do not if calling strfield1 and strwhere1 is a proper syntax or not but I do not know what else to try. I have not put the code in yet for the variable because I really have not clue how to do that. This has really bogged me down the last few days and noone locally can help me. I know this is an easy thing to do but not for such a noob like me!! Please help and give me some idea's that can help me!!

Thanks

 

[Fizzio]

Try surrounding your dates with # ie

strWhere1 = strField1 & " < # " & Format(Me.txtEndDate, conDateFormat) & " # "

On a side note:
If you are using between, should you also not be using <= and >= in the other evaluations?

 

[Rich]

What's conDateFormat? and you shouldn't have a field named Date, it's a reserved word in Access and will cause you problems

 

[drake]

Update

Well here is an update to the earlier post. I figured out a few things I was doing wrong and here is the updated code:

Private Sub Command0_Click()
Dim strReport As String 'Name of report to open.
Dim strField1 As String 'Name of your Harvest field.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Contractor's Weekly Summary Report"
strField = "HARV"
strField1 = "DATE1"


' Debug.Print strWhere 'For debugging purposes only.
If Not IsNull(Me.cboHARV) Then 'block combo box
strWhere = "[HARV] = """ & Me.cboHARV & """"
End If


If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField1 & " < " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField1 & " > " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField1 & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If


DoCmd.OpenReport strReport, acViewPreview, , strWhere
Me.Visible = False
End Sub


Private Sub Command1_Click()
DoCmd.Close acForm, Me.Name
End Sub


The last and final thing I need to accomplish on this form is to let the user input a variable which will be used in a calculation query and thus reported on the report we are trying to generate here. I have created a text box on the form and need to store a variable [CHAPPX] in a query. I know it is not much but what code do I need to include to do this??

Thanks