-
Sub PreviewReport_Click()
-
On Error GoTo Err_PreviewReport_Click
-
-
Dim stRepName As String: Rem Holds the Report name
-
Dim stDispId As String: Rem Holds the Dispenser ID
-
Dim stQuery As String: Rem Holds the Query name
-
Dim stWhere As String: Rem Holds the where clause
-
Dim stExtra As String:
-
Dim stMonthYear, stYear, stWeekCount As String
-
Dim intX As Integer, rst As Recordset
-
Dim dtEndDate As Date
-
-
stRepName = Form.cbReports
-
-
If IsNull(Form.cbDispenser) Then
-
MsgBox ("Please pick a Dispenser.")
-
GoTo Exit_PreviewReport_Click
-
End If
-
-
stDispId = Form.cbDispenser
-
stWhere = ""
-
-
If IsNull(Form.cbMonthYear) Then
-
MsgBox ("Please pick a Month")
-
GoTo Exit_PreviewReport_Click
-
Else
-
stMonthYear = Form.cbMonthYear
-
stYear = Format(CDate("1 " & Form.cbMonthYear), "yyyy")
-
End If
-
-
-
dtEndDate = Nz(DMax("EndDate", "Dispensers", "[ID] = " & stDispId), cLowDate)
-
-
-
-
-
-
-
If stRepName = "Weekly Dispenser Sales" Then
-
stQuery = "Weekly Dispenser Sales"
-
stWhere = "format(START_DT,""MMMM YYYY"") = """ + stMonthYear + """"
-
ElseIf stRepName = "Individual Weekly Sales" Then: Rem Sheet 8
-
If dtEndDate <> cLowDate And _
-
Format(dtEndDate, "YYYYMMDD") < Format(CDate(cbMonthYear), "YYYYMMDD") Then
-
-
MsgBox ("There are no records for this Dispenser in this month")
-
Exit Sub
-
-
Else
-
stQuery = "Individual Weekly Sales"
-
stWhere = "SALES.DISPENSER_ID = " + stDispId + " and format(START_DT,""MMMM YYYY"") = """ + stMonthYear + """"
-
End If
-
ElseIf stRepName = "Company Weekly Sales" Then
-
stQuery = "Company Weekly Sales"
-
-
-
ElseIf stRepName = "Company Monthly Sales" Then
-
stQuery = "Company Monthly Sales"
-
-
ElseIf stRepName = "Company Year End" Then
-
stQuery = "Company Year End"
-
stWhere = "SALES.WorkingWeek=-1"
-
ElseIf stRepName = "Dispenser Comparison Monthly" Then: Rem Sheet 11
-
stQuery = "Dispenser Comparison Monthly"
-
-
ElseIf stRepName = "Dispenser Comparison Yearly" Then
-
stQuery = "Dispenser Comparison Yearly"
-
stWhere = "Year= " & stYear & " and SALES.WorkingWeek =-1"
-
ElseIf stRepName = "Individual Monthly Sales" Then
-
If dtEndDate <> cLowDate And _
-
Format(dtEndDate, "YYYY") < Format(CDate(cbMonthYear), "YYYY") Then
-
-
MsgBox ("There are no records for this Dispenser in this year")
-
Exit Sub
-
Else
-
stQuery = "Individual Monthly Sales"
-
stWhere = "DISPENSER_ID=" & stDispId
-
End If
-
ElseIf stRepName = "Individual Year End" Then
-
stQuery = "Individual Year End"
-
stWhere = "DISPENSERID=" & stDispId & " and SALES.WorkingWeek = -1"
-
End If
-
-
DoCmd.OpenReport stRepName, acPreview, stQuery, stWhere
-
-
Exit_PreviewReport_Click:
-
Exit Sub
-
-
Err_PreviewReport_Click:
-
MsgBox Err.Description
-
Resume Exit_PreviewReport_Click
-
-
End Sub
-
hey guys the above code is for a preview report button on a main menu.
one of the options for the report is to pick a report type, the pick the dispenser, the the month
so for example INDIVDUALmonthley SALES , JOE BLOGS for the month of JUNE.
so i would like it to look in the weekly sales report, extract the sales for JOE BLOGS for the month of june to be displayed.
below is the query im trying to use to extract the information -
-
SELECT DISTINCTROW SALES.DISPENSER_ID, DISPENSERS.FORENAME + " " + DISPENSERS.SURNAME AS Dispenser, format(START_DT,"mmmm") AS [Month], format(START_DT,"yyyy") AS [Year], Sum(SALES.WorkingWeek)*-1 AS NoOfWeeks, count(format(START_DT,"mmmm yyyy")) AS PETE, Sum(SALES.SALES) AS TotalSales, Sum(SALES.SALES_UNITS) AS TotalSalesUnits, Sum(SALES.PHILIPS) AS TotalPhilips, iif(Sum(Sales)=0,0,Sum(DONE)/Sum(SALES)) AS SellingAppDone, iif(Sum(Sales)=0,0,Sum(BOOKED)/Sum(SALES)) AS SellingAppBooked, iiF(Sum(SALES_UNITS)=0,0,Sum(SALES_VALUE)/Sum(SALES_UNITS)) AS AvgPrice, Sum(SALES_VALUE)/Sum(SALES.WorkingWeek)*-1 AS AvgSalesPerWeek, IIF(Sum(SALES_UNITS)-Sum(SALES)=0,0,Sum(SALES)/(Sum(SALES_UNITS)-Sum(SALES))) AS BinauralSales, Sum(SALES_VALUE) AS TotalSaleValue, null AS LessCancelled, Sum(FITTED_UNITS) AS FittedUnits, Sum(FITTED_VALUE) AS FittedValue, null AS LessCancel, Sum(DONE) AS AppDone, Sum(BOOKED) AS AppBooked, Sum(DONE)/Sum(SALES.WorkingWeek)*-1 AS AvgPerWeekDone, Sum(BOOKED)/Sum(SALES.WorkingWeek)*-1 AS AvgPerWeekBooked, cDate('1 ' & format(START_DT,"mmmm") & " " & format(START_DT,"yyyy") ) AS OrdDate
-
FROM PERIOD INNER JOIN (DISPENSERS INNER JOIN SALES ON DISPENSERS.ID = SALES.DISPENSER_ID) ON PERIOD.ID = SALES.PERIOD_ID
-
GROUP BY SALES.DISPENSER_ID, DISPENSERS.FORENAME, DISPENSERS.SURNAME, format(START_DT,"mmmm"), format(START_DT,"yyyy")
-
ORDER BY SALES.DISPENSER_ID, cDate('1 ' & format(START_DT,"mmmm") & " " & format(START_DT,"yyyy") ), cDate('1 ' & format(START_DT,"mmmm") & " " & format(START_DT,"yyyy") );
-
-
-
but im getin a parmeter box which is asking for me to enter parameter value cDate('1 ' & format(START_DT,"mmmm") & " " & format(START_DT,"yyyy") ) ?? i have no idea why.
i get parmeter boxes when i try and preview alot of the reports.................its so confusing, i dont kno were to start looking
6 1829 Rabbit 12,516
Recognized Expert Moderator MVP
but im getin a parmeter box which is asking for me to enter parameter value cDate('1 ' & format(START_DT,"mmmm") & " " & format(START_DT,"yyyy") ) ?? i have no idea why.
That's because you're trying to sort by a field that doesn't exist. If you look at the SORT BY statement of your SQL you will see 2 calculated fields that don't exist and hence you can't sort by them.
That's because you're trying to sort by a field that doesn't exist. If you look at the SORT BY statement of your SQL you will see 2 calculated fields that don't exist and hence you can't sort by them.
so take the ORDER BY off and it should work,
. but would that let me pick a report, chose a dispenser by say 12/05/02-17/05/02 and will display the sales for that period?
so take the ORDER BY off and it should work,
. but would that let me pick a report, chose a dispenser by say 12/05/02-17/05/02 and will display the sales for that period?
no adivce?
Rabbit 12,516
Recognized Expert Moderator MVP
so take the ORDER BY off and it should work,
. but would that let me pick a report, chose a dispenser by say 12/05/02-17/05/02 and will display the sales for that period?
Filtering for a records within a range of dates is never done through the ORDER BY as ORDER BY only sorts the records returned. If you want to filter for a date range you do it through the WHERE clause.
Filtering for a records within a range of dates is never done through the ORDER BY as ORDER BY only sorts the records returned. If you want to filter for a date range you do it through the WHERE clause.
ideas? please
Rabbit 12,516
Recognized Expert Moderator MVP
Well, to filter for a range of dates it would be WHERE #Date1# AND #Date2#.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Aaron |
last post by:
Hello,
I'm trying to limit a report to a list of parameters that I pass in.
The report and the underlying query both use the column FirstName.
Whenever I try to limit the FirstName in via the...
|
by: bruce |
last post by:
Hello,
In Access 2003 one can use OpenReport with the acDialog parameter to
open a report modally. However, doing this prevents the report's
toolbars from displaying. Is there a way around...
|
by: teddysnips |
last post by:
I have been asked to look at an Access application. It generates a
report successfully, so that you can see it on the screen with no
errors.
However, when you try to print it, the prompt dialog...
|
by: TC |
last post by:
I've produced an Access application for a client. For one report, text
gets cut-off at the right margin when we print the report. It does this
only when we print; it doesn't happen when we view the...
|
by: mudman04 |
last post by:
Hi,
I searched online for some similar issues that I am facing but was not able come up with anything. I am fairly new with Access (2 months experience) and I am trying to remove a message...
| |
by: fieldja |
last post by:
I have a form called OwnerForm. It contains a combo box called
Owner. The combo box looks up names from a table called OwnerName.
It contains fields called OwnerID and Owner. I also have a main...
|
by: Ben |
last post by:
NooB: Anyone help with what I could be doing incorrectly here? Trying to
have a parameter drop down list where a user can select one or more of the
available values contained in the table to...
|
by: waltnixon |
last post by:
I've got an MS Access query which runs fine when double clicked and returns all of the rows in a test database I'm building.
I've set up a multi group report based on the query. I immediately...
|
by: Gord |
last post by:
When I open a certain report, it runs some code that generates the records
that will be displayed in that report. This works fine. When I go to print
preview the report it appears that the code...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |