Report Variables

[Drake12]

I need to do a weekly report that need to include date ranges, make user select a piece of data from a field or basically sort the report by HARV, and do a calculation using a variable. I have done the date ranges before so they work, I chose a combo box to ask the user for certian data from a field (HARV), but it is not sorting correctly. Lastly I need the user to enter a variable number and do a calculation and have it reported. This is what I have so far on my command button:

Private Sub Command0_Click()
Dim strReport As String 'Name of report to open.
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 = "DATE"

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

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Me.Visible = False
End Sub


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

Once the user enters the variable number I am trying to save it in CHAPPX within a query and do the calculations and then get it to the report labeled "Contractor's Weekly Summary Report".

Thanks

 

[RoyVidar]

I can't find anything here relating to sorting at all. To sort a report, use the sorting and grouping thingie within the reports design view (or do it programaticly thru for instance something like this Sorting Records in a Report)

It seems you have a field called "Date", which you shouldn't, as it is a reserwed word (Date function), I'd make sure to add [brackets] around it when referencing (or best, rename it) (strField = "[DATE]").

I can't find any calculations either, where are you trying to do those? To fetch a variable in a query, the variable need to be declared as public (in a standard module, not a forms/reports class module), and you need a public function to retrieve it.

Roy-Vidar

 

[Drake12]

Yeah I do need a date range and it is working. I need to incorporate HARV into this code somehow. I need to select a value for HARV so the report will be sorted by DATE first then HARV. Then finally I want the user to input a CHARRX variable. Do I just create a new strWhere value or what is the proper syntax?

 

[Drake12]

Well I will provide more code, but it is just not working. Maybe I should do this another way, but I do not know what that would be. Again I will try and explain a little better. First of all I will need the user to input a date range. Next the report will need to be sorted by Harvester [HARV]. And finally each Harvester gets a different rate so I would like the user to input that rate each time they run a report. I have created [CHAPPX] in the table/query to store that variable. I tried using a text box on my form but have had no luck yet. I know my syntax strfield1 is probably wrong but I do not know what to use.

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

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

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



If Not IsNull(Me.cboHARV) Then 'block combo box
strfield1 = "[HARV] = """ & Me.cboHARV & """"
End If

End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Me.Visible = False
End Sub


I hope it makes a little sense as to what I am trying to do. Thank you for your patience and all your help.