Date Range On Report - Solution Required

[aamer]

I am using the following code which works perfectly.
What am unable to get is the date range is not present on the report.

How and what should I add to this code to get the date range mentioned on the report.

example: Between Date: 01-Jan-2015 To Date: 26-Jan-2015





Private Sub cmdOpenReportSingle_Click()
On Error GoTo Err_Handler

Const REPORTNAME = "General Sales Dialog"
Const MESSAGETEXT = "Both a start and end date must be selected."
Dim strCriteria As String
Dim strDateFrom As String, strDateTo As String

' make sure a Dates are selected
If Not IsNull(Me.cboDateFrom) And Not IsNull(Me.cboDateTo) Then
strDateFrom = "#" & Format(Me.cboDateFrom, "yyyy-mm-dd") & "#"
strDateTo = "#" & Format(DateAdd("d", 1, Me.cboDateTo), "yyyy-mm-dd") & "#"
' build string expression to filter report
' to selected date range
strCriteria = "TransactionDate >= " & strDateFrom & " And TransactionDate < " & strDateTo

' open report filtered to selected Dates
DoCmd.OpenReport REPORTNAME, _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox MESSAGETEXT, vbExclamation, "Invalid Date Operation"
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here
End Sub

[orange]

Do you get an error message?
Why are your Dates dimmed as strings?

[aamer]

I have 2 combo boxes "cboDateFrom" and "cboDateFrom" where I select the dates.
On report query I don't know how I should add these so I get it on the report. Presently I have the following query on report.

SELECT Sales.TransId, Sales.TransactionDate, Company.ComapnyName, Company.CoId, MPOM.[Bill #], Challan.[Challan #], IIf([Amount Received]=0,([Qty Returned]*[Price Refund]),[Amount Received]) AS Debit, Sales.[Transaction Description], Sales.Odered, Sales.Units, Sales.Price, Sales.Seller, Sales.[Income Tax], Sales.[Sales Tax], Company.Catagory, Sales.remarks, Sales.[Amount Received], Sales.Discount, IIf([Amount Paid]=0,([Odered]*[Price]),[Amount Paid]) AS Credit, (-[Discount]) AS Dcnt, Sales.[Sales Varification], [Transaction Description] & " " & [Sub Description] AS Descrip, Sales.[Sub Description], Sales.[Sales Type], Sales.[Sale Status], IIf([Qty Returned]>0,(-[Qty Returned]),[Odered]) AS QTY, ([debit]-[Discount])-[credit] AS TBalance, IIf([Price Refund]>0,[Price Refund],[Price]) AS Rate, Sales.[Free Item], IIf([Sale Status]="Refund Bill",("Refund Bill " & [Remarks] & " " & [AFreeItem]),([Remarks] & " " & [AFreeItem] & " " & [Adv Description] & " " & [ARate] & " " & [AQty] & " " & [AAdvUnits] & " " & [Var Status] & " " & [Sale Book Ref No])) AS GSRem, IIf([Adv Rate]=0,Null,"Rs." & [Adv Rate]) AS ARate, IIf([Adv Qty]=0,Null," - " & [Adv Qty]) AS AQty, IIf([Free Item]="Nil",Null,[Free Item]) AS AFreeItem, IIf([Adv Units]="Nill",Null,[Adv Units]) AS AAdvUnits, IIf([Sales Varification]="Verified","Entry Verified",Null) AS [Var Status], Sales.[Sale Book Ref No]
FROM Company INNER JOIN ((Sales LEFT JOIN Challan ON Sales.TransId=Challan.[Trans ID]) LEFT JOIN MPOM ON Challan.[Challan #]=MPOM.[Challan #]) ON Company.CoId=Sales.CoId
WHERE (((Sales.[Sales Type])<>"Returnable Entry") AND ((IIf([Qty Returned]>0,(-[Qty Returned]),[Odered]))>0))
ORDER BY Sales.TransactionDate
WITH OWNERACCESS OPTION;

[Paul H]

If you already have the date fields in the combo box, why don't you just populate a text box or create a new field in your query with a formatted string.

"From: " & Format(Me.cboDateFrom, "dd-mmm-yyyy") & " To: " & Format(DateAdd("d", 1, Me.cboDateTo), "dd-mmm-yyyy")

[aamer]

I did try to get it done by the help from the forum but may be I could not exactly explain the problem or I did not understand properly. So I have attached a sample db with the problem I am facing.

I have a form named: "Dialog Sale Date Range" with 2 combo boxes "cboDateFrom" & "cboDateTo" Where I select the date range.
On the same form I have a button "Open Report" when I open the report I am unable to add the fields of the date range to this report.

I have uploaded the Sample Database for your better understanding for the problem I am facing in this report.

I shall be grateful for your kind help.

Date Range Sample DB.zip

[cbende2]

Aamer, check the message I sent you.

In the attachment you can see the changes I made.

It's a simple fix, just set your report criteria for whatever date field(TransactionDate, in your case) to "Between [Forms]![MyForm]![cboBox] And [Forms]![MyForm]![cboBox2]"

Date Range Sample DB.zip

[aamer]

thank u so much

In my report control source, under Transaction Date's criteria, add Between [Forms]![Dialog Sale Date Range]![cboDateFrom] And [Forms]![Dialog Sale Date Range]![cboDateTo].

This part I was doing correctly.


But, in the report header Following was missing and this I was not understanding:

set your 2 unbound text boxes like this, "=[Forms]![Dialog Sale Date Range]![cboDateFrom]" and "=[Forms]![Dialog Sale Date Range]![cboDateTo]"



thx very much for your help and making me understand the solution.

regards
aamer