473,322 Members | 1,501 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

month will not change

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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
  3. FROM PERIOD INNER JOIN SALES ON PERIOD.ID = SALES.PERIOD_ID
  4. GROUP BY Format(PERIOD.START_DT,"mmmm yyyy"), SALES.PERIOD_ID, PERIOD.START_DT, PERIOD.END_DT;
  5.  
  6.  
  7.  
  8.  
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Sub PreviewReport_Click()
  3. On Error GoTo Err_PreviewReport_Click
  4.  
  5.     Dim stRepName As String:    Rem Holds the Report name
  6.     Dim stDispId  As String:    Rem Holds the Dispenser ID
  7.     Dim stQuery   As String:    Rem Holds the Query name
  8.     Dim stWhere   As String:    Rem Holds the where clause
  9.     Dim stExtra   As String:
  10.     Dim stMonthYear, stYear, stWeekCount As String
  11.     Dim intX As Integer, rst As Recordset
  12.     Dim dtEndDate As Date
  13.  
  14.     stRepName = Form.cbReports
  15.  
  16.     If IsNull(Form.cbDispenser) Then
  17.        MsgBox ("Please pick a Dispenser.")
  18.        GoTo Exit_PreviewReport_Click
  19.     End If
  20.  
  21.     stDispId = Form.cbDispenser
  22.     stWhere = ""
  23.  
  24.     If IsNull(Form.cbMonthYear) Then
  25.         MsgBox ("Please pick a Month")
  26.         GoTo Exit_PreviewReport_Click
  27.     Else
  28.         stMonthYear = Form.cbMonthYear
  29.         stYear = Format(CDate("1 " & Form.cbMonthYear), "yyyy")
  30.     End If
  31.  
  32.  
  33.     dtEndDate = Nz(DMax("EndDate", "Dispensers", "[ID] = " & stDispId), cLowDate)
  34.  
  35.  
  36.  
  37.  
  38.  
  39.  
  40. If stRepName = "Weekly Dispenser Sales" Then
  41.     stQuery = "Weekly Dispenser Sales"
  42.     stWhere = "format(START_DT,""MMMM YYYY"") = """ + stMonthYear + """"
  43. ElseIf stRepName = "Individual Weekly Sales" Then: Rem Sheet 8
  44.     If dtEndDate <> cLowDate And _
  45.         Format(dtEndDate, "YYYYMMDD") < Format(CDate(cbMonthYear), "YYYYMMDD") Then
  46.  
  47.             MsgBox ("There are no records for this Dispenser in this month")
  48.         Exit Sub
  49.  
  50.     Else
  51.         stQuery = "Individual Weekly Sales"
  52.        stWhere = "SALES.DISPENSER_ID = " + stDispId + " and format(START_DT,""MMMM YYYY"") = """ + stMonthYear + """"
  53.     End If
  54. ElseIf stRepName = "Company Weekly Sales" Then
  55.     stQuery = "Company Weekly Sales"
  56.  
  57.  
  58. ElseIf stRepName = "Company Monthly Sales" Then
  59.     stQuery = "Company Monthly Sales"
  60.  
  61. ElseIf stRepName = "Company Year End" Then
  62.     stQuery = "Company Year End"
  63.     stWhere = "SALES.WorkingWeek=-1"
  64. ElseIf stRepName = "Dispenser Comparison Monthly" Then: Rem Sheet 11
  65.     stQuery = "Dispenser Comparison Monthly"
  66.  
  67. ElseIf stRepName = "Dispenser Comparison Yearly" Then
  68.     stQuery = "Dispenser Comparison Yearly"
  69.     stWhere = "Year= " & stYear & " and SALES.WorkingWeek =-1"
  70. ElseIf stRepName = "Individual Monthly Sales" Then
  71.     If dtEndDate <> cLowDate And _
  72.         Format(dtEndDate, "YYYY") < Format(CDate(cbMonthYear), "YYYY") Then
  73.  
  74.         MsgBox ("There are no records for this Dispenser in this year")
  75.         Exit Sub
  76.     Else
  77.         stQuery = "Individual Monthly Sales"
  78.         stWhere = "DISPENSER_ID=" & stDispId
  79.     End If
  80. ElseIf stRepName = "Individual Year End" Then
  81.     stQuery = "Individual Year End"
  82.     stWhere = "DISPENSERID=" & stDispId & " and SALES.WorkingWeek = -1"
  83. End If
  84.  
  85. DoCmd.OpenReport stRepName, acPreview, stQuery, stWhere
  86.  
  87. Exit_PreviewReport_Click:
  88.     Exit Sub
  89.  
  90. Err_PreviewReport_Click:
  91.     MsgBox Err.Description
  92.     Resume Exit_PreviewReport_Click
  93.  
  94. End Sub
  95.  
  96.  
Mar 30 '07 #1
2 1820
Rabbit
12,516 Expert Mod 8TB
Put a break at the beginning of the code and step through (F8) to see if your variables are returning the right results.
Mar 30 '07 #2
MMcCarthy
14,534 Expert Mod 8TB
I think your problem lies in your date formats.

For instance ... what does this return?

Expand|Select|Wrap|Line Numbers
  1. stYear = Format(CDate("1 " & Form.cbMonthYear), "yyyy")
Mary
Apr 1 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

5
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
11
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"...
4
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: ...
8
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...
34
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...
7
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)...
18
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...
22
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...
11
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...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
1
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)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.