-
-
SELECT DISTINCTROW Format(PERIOD.START_DT,"mmmm yyyy") AS MonthYear, SALES.PERIOD_ID, Sum(SALES.SALES) AS SumOfSALES, Sum(SALES.SALES_UNITS) AS SumOfSALES_UNITS, Sum(SALES.SALES_VALUE) AS SumOfSALES_VALUE, Sum(SALES.PHILIPS) AS SumOfPHILIPS, Sum(SALES.FITTED) AS SumOfFITTED, Sum(SALES.FITTED_UNITS) AS SumOfFITTED_UNITS, Sum(SALES.FITTED_VALUE) AS SumOfFITTED_VALUE, Sum(SALES.DONE) AS SumOfDONE, Sum(SALES.BOOKED) AS SumOfBOOKED, Sum(SALES.BOOKED_NEXT) AS SumOfBOOKED_NEXT, PERIOD.END_DT, PERIOD.START_DT, Min(SALES.WorkingWeek)*-1 AS WorkingWeek
-
FROM PERIOD INNER JOIN SALES ON PERIOD.ID = SALES.PERIOD_ID
-
GROUP BY Format(PERIOD.START_DT,"mmmm yyyy"), SALES.PERIOD_ID, PERIOD.START_DT, PERIOD.END_DT;
-
-
-
-
high guys, hey above code is meant to grab the contents of a COMPANY weekly SALES, but when i change the month from the main menu,when the preview report button is pushed and the report is shown, the month does not change, it stays on AUGUST 2001 for some reason, displaying the weeks in that month.
any ideas?
preview button code -
-
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
-
-
2 1820
Put a break at the beginning of the code and step through (F8) to see if your variables are returning the right results.
I think your problem lies in your date formats.
For instance ... what does this return? - stYear = Format(CDate("1 " & Form.cbMonthYear), "yyyy")
Mary
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ken Fine |
last post by:
I want my application to maintain a directory tree based on months and
years, e.g.:
2004
January
file
file
file
February
file
|
by: Jim |
last post by:
I have been using the javascript below on a web page since last August to
show the "Site updated" month only minus a month, which has been very
successful, but January is showing a "undefined 2004"...
|
by: Pea |
last post by:
Hello,
I have a nicely running report (thanks to Reggie and others) which
I've been asked to modify to prompt for the Month (a monthly report to
be run). I used an InputBox command:
...
|
by: tfsmag |
last post by:
i need to create a date range based on the current "shown" month on a
calendar control to query a database and populate a datagrid based on
that date range.
how can i retrieve the "shown" month...
|
by: prosoft |
last post by:
When I use
Dim myDTFI As DateTimeFormatInfo = New CultureInfo("he-IL",
True).DateTimeFormat
Dim strhmon1 As String = (myDTFI.GetMonthName(hmon1))
MsgBox(strhmon1)
I get the local name of the...
|
by: Novice Computer User |
last post by:
Hi. Can somebody PLEASE help. I have spent hours on this.. but I am a
total novice and can't seem to figure it out. Here is a .php script.
Right now, the
minimum amount of time (i.e. duration)...
|
by: PC Datasheet |
last post by:
An Access user saw my name in a newsgroup and sent me a request for help on
a project. As part of the project, a list of the dates in a month was
needed. For anyone needing a list of dates in a...
|
by: Stan |
last post by:
I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for...
|
by: Randy |
last post by:
I have a MonthCalendar on one of my forms. I have disovered that the
DateChanged event is triggered not only when the user clicks on a new
date, but also if they click on the Previous or Next...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
| |