If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with IIF expression
Hi,
I’m trying to get my column heading in my report to show the month from the parameter input. I’m not sure how to write this expression with the following expression contained in my query: ODCPrevious: Sum(IIf([Mnth]=2,[ODC_Cost],0)) Many thanks, Kay |
#2
|
|||
|
|||
Help with IIF expression
Where does the parameter fit into the expression? Is Mnth the parameter for
which the user is prompted? AccessKay wrote: Hi, I’m trying to get my column heading in my report to show the month from the parameter input. I’m not sure how to write this expression with the following expression contained in my query: ODCPrevious: Sum(IIf([Mnth]=2,[ODC_Cost],0)) Many thanks, Kay -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Help with IIF expression
Hi Bruce,
I usually post from the Microsoft forum but it doesn't appear to be working. I thought I'd check here and glad I did. I hope I can explain this...I set up two queries initially, one for current month and one for previous month and put a field in called Mnth:1 and Mnth:2 (assigning numbers 1 or 2). My expression below is for Mnth:2 or the Previous Month. Then I did a Union query and merged the two. And then I did my final query that shows the expression that I typed below. So, the parameter is located in the query that I created first for previous month under MnthC [TransDate]). But for some reason the MnthC: disappears when I reopen the query. I hope you understand. TransDate is my full date for each transaction. Thanks, Kay BruceM wrote: Where does the parameter fit into the expression? Is Mnth the parameter for which the user is prompted? Hi, I’m trying to get my column heading in my report to show the month from the [quoted text clipped - 5 lines] Many thanks, Kay |
#4
|
|||
|
|||
Help with IIF expression
You seem to be taking the long way around, but I can't quite understand what
you are trying to do. The thing that has me especially puzzled is that you have created queries for the previous month and the current month, but then added another field that is a fixed value. Please post the SQL for the Mnth: 1 query. To do that, open the query. Click View SQL. Copy what you see there, and post it here. It seems you have Mnth: 1 and Mnth: 2 queries, and a union query to put them together, but then there is a final query, I think. When referring to queries in postings it would be best to give them names. You mention "the query that I created first for previous month under MnthC", but I can't tell if this is yet another query, or the Mnth: 1 query, or something else; and I don't know what MnthC is. AccessKay wrote: Hi Bruce, I usually post from the Microsoft forum but it doesn't appear to be working. I thought I'd check here and glad I did. I hope I can explain this...I set up two queries initially, one for current month and one for previous month and put a field in called Mnth:1 and Mnth:2 (assigning numbers 1 or 2). My expression below is for Mnth:2 or the Previous Month. Then I did a Union query and merged the two. And then I did my final query that shows the expression that I typed below. So, the parameter is located in the query that I created first for previous month under MnthC [TransDate]). But for some reason the MnthC: disappears when I reopen the query. I hope you understand. TransDate is my full date for each transaction. Thanks, Kay Where does the parameter fit into the expression? Is Mnth the parameter for which the user is prompted? [quoted text clipped - 4 lines] Many thanks, Kay -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Help with IIF expression
Bruce,
This is confusing…I know. Sorry about that. I think it might be best to give you the SQL from each query. Thanks for looking at this! If you happen to know a better way to do this, then I'm all for it because I have to do the same for year and quarter and then again by a different category. Name: bqODCVar1 SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 AS Mnth FROM tblTrans_Mstr WHERE (((([TransDate])) Like [Enter Current Month] & "*") AND ((Year( [TransDate]))=[Enter Current Year]) AND ((Format([TransDate],"q"))=[Enter Current QTR])) GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 HAVING (((tblTrans_Mstr.Group)="ODC")); Name: bqODCVar2 SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2 AS Mnth FROM tblTrans_Mstr WHERE (((([TransDate])) Like [Enter Previous Month] & "*") AND ((Year( [TransDate]))=[Enter Current or Previous Year]) AND ((Format([TransDate],"q")) =[Enter Previous QTR])) GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2 HAVING (((tblTrans_Mstr.Group)="ODC")); Name: bqODCVar (this is my Union Query…as you will see) SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 AS Mnth FROM tblTrans_Mstr WHERE (((([TransDate])) Like [Enter Current Month] & "*") AND ((Year( [TransDate]))=[Enter Current Year]) AND ((Format([TransDate],"q"))=[Enter Current QTR])) GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 HAVING (((tblTrans_Mstr.Group)="ODC")); UNION ALL SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2 AS Mnth FROM tblTrans_Mstr WHERE (((Month([TransDate])) Like [Enter Previous Month] & "*") AND ((Year( [TransDate]))=[Enter Previous Year]) AND ((Format([TransDate],"q"))=[Enter Previous QTR])) GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2 HAVING (((tblTrans_Mstr.Group)="ODC")); Name: qryODCVariance (my final query that I’m using for my report) SELECT bqODCVar.Category, bqODCVar.Group, bqODCVar.Product, bqODCVar. PA_Descriptions, bqODCVar.Credit_GL_Acct, Sum(IIf([Mnth]=2,[ODC_Cost],0)) AS ODCPrevious, Sum(IIf([Mnth]=1,[ODC_Cost],0)) AS ODCCurrent, [ODCCurrent]- [ODCPrevious] AS ODCDelta FROM bqODCVar GROUP BY bqODCVar.Category, bqODCVar.Group, bqODCVar.Product, bqODCVar. PA_Descriptions, bqODCVar.Credit_GL_Acct HAVING (((bqODCVar.Group)="ODC")); BruceM wrote: You seem to be taking the long way around, but I can't quite understand what you are trying to do. The thing that has me especially puzzled is that you have created queries for the previous month and the current month, but then added another field that is a fixed value. Please post the SQL for the Mnth: 1 query. To do that, open the query. Click View SQL. Copy what you see there, and post it here. It seems you have Mnth: 1 and Mnth: 2 queries, and a union query to put them together, but then there is a final query, I think. When referring to queries in postings it would be best to give them names. You mention "the query that I created first for previous month under MnthC", but I can't tell if this is yet another query, or the Mnth: 1 query, or something else; and I don't know what MnthC is. Hi Bruce, [quoted text clipped - 19 lines] Many thanks, Kay |
#6
|
|||
|
|||
Help with IIF expression
It seems the first three queries are intended to retrieve records from this
month and last month. The way the parameter is set up the user could specify any two months (one in each query), but since the prompt is to enter the current month, year, and quarter (?), it seems you mean for the user to select the months and years you have specified. If that is the case, there is no need for the user to enter a parameter. You could replace the first three queries with this: SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 AS Mnth FROM tblTrans_Mstr WHERE tblTrans_Mstr.[TransDate] = DateSerial(Year(Date()),Month(Date())-1,1) AND tblTrans_Mstr.Group = "ODC" I left out the grouping, but you could add it back if it is needed. For the report you could group by month. Go to View Sorting and Grouping. In Field/Expression enter on the top line: Month(TransDate) Sort Ascending In the next line: Format([TransDate],"mmmm") Select Group Header and Group Footer at the bottom of the dialog box, and close the dialog box. In the Report, place in the group header a text box bound to TransDate. Set its format to "mmmm". In the group footer, place a combo box with its Control Source set to: =Sum([TransDate]) This is one of several options you have. It can be refined or adjusted as needed. In any case, the report can use the simple query I showed. If the idea is to show a different range of months than this month and last month, that can be done. Non-contiguous months are more difficult, but can be done also. Access has a lot of built-in capabilities that can be harnessed to make your task simpler. Perhaps I have misread the situation, but I am quite certain now that you were taking the long way around. AccessKay wrote: Bruce, This is confusing…I know. Sorry about that. I think it might be best to give you the SQL from each query. Thanks for looking at this! If you happen to know a better way to do this, then I'm all for it because I have to do the same for year and quarter and then again by a different category. Name: bqODCVar1 SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 AS Mnth FROM tblTrans_Mstr WHERE (((([TransDate])) Like [Enter Current Month] & "*") AND ((Year( [TransDate]))=[Enter Current Year]) AND ((Format([TransDate],"q"))=[Enter Current QTR])) GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 HAVING (((tblTrans_Mstr.Group)="ODC")); Name: bqODCVar2 SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2 AS Mnth FROM tblTrans_Mstr WHERE (((([TransDate])) Like [Enter Previous Month] & "*") AND ((Year( [TransDate]))=[Enter Current or Previous Year]) AND ((Format([TransDate],"q")) =[Enter Previous QTR])) GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2 HAVING (((tblTrans_Mstr.Group)="ODC")); Name: bqODCVar (this is my Union Query…as you will see) SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 AS Mnth FROM tblTrans_Mstr WHERE (((([TransDate])) Like [Enter Current Month] & "*") AND ((Year( [TransDate]))=[Enter Current Year]) AND ((Format([TransDate],"q"))=[Enter Current QTR])) GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 HAVING (((tblTrans_Mstr.Group)="ODC")); UNION ALL SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2 AS Mnth FROM tblTrans_Mstr WHERE (((Month([TransDate])) Like [Enter Previous Month] & "*") AND ((Year( [TransDate]))=[Enter Previous Year]) AND ((Format([TransDate],"q"))=[Enter Previous QTR])) GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2 HAVING (((tblTrans_Mstr.Group)="ODC")); Name: qryODCVariance (my final query that I’m using for my report) SELECT bqODCVar.Category, bqODCVar.Group, bqODCVar.Product, bqODCVar. PA_Descriptions, bqODCVar.Credit_GL_Acct, Sum(IIf([Mnth]=2,[ODC_Cost],0)) AS ODCPrevious, Sum(IIf([Mnth]=1,[ODC_Cost],0)) AS ODCCurrent, [ODCCurrent]- [ODCPrevious] AS ODCDelta FROM bqODCVar GROUP BY bqODCVar.Category, bqODCVar.Group, bqODCVar.Product, bqODCVar. PA_Descriptions, bqODCVar.Credit_GL_Acct HAVING (((bqODCVar.Group)="ODC")); You seem to be taking the long way around, but I can't quite understand what you are trying to do. The thing that has me especially puzzled is that you [quoted text clipped - 16 lines] Many thanks, Kay -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201004/1 |
#7
|
|||
|
|||
Help with IIF expression
Bruce,
I got stuck on the part where you said “On the next line” where I should enter Format([TransDate],"mmmm"). I don’t think I have a dialog box. I’m using 2007 so all that sorting and grouping stuff is at the bottom. I put the rest in though. I never knew you could put a combo box in a report! I’m not yet sure of its purpose because it’s a number I don’t recognize (not the sum of ODC_Cost). And that’s neat how the quarter shows up. But this isn’t what I wanted to do in this case though I hope to use this for my current month reports. I was needing to build a variance report where the user can pick any two months and the report will show those months along with the variance. I have to also do this for Current Month in Current Year compared with the same Previous Year’s Month. And then the current Qtr vs the previous year’s Qtr. That’s how I came up with my complicated assortment of queries I don’t want to take up too much of your time. So if it’s too long of a process to make suggestions about how I can get what I just mentioned, then I just need to know how in my report, I can get the Headings for the ODC Cost to show the months chosen from the parameter. I wish I knew how to do this better. I’ve read all kinds of stuff but can’t get anything specific to what I’m trying to do. I really do appreciate the help! BruceM wrote: It seems the first three queries are intended to retrieve records from this month and last month. The way the parameter is set up the user could specify any two months (one in each query), but since the prompt is to enter the current month, year, and quarter (?), it seems you mean for the user to select the months and years you have specified. If that is the case, there is no need for the user to enter a parameter. You could replace the first three queries with this: SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr. Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 AS Mnth FROM tblTrans_Mstr WHERE tblTrans_Mstr.[TransDate] = DateSerial(Year(Date()),Month(Date())-1,1) AND tblTrans_Mstr.Group = "ODC" I left out the grouping, but you could add it back if it is needed. For the report you could group by month. Go to View Sorting and Grouping. In Field/Expression enter on the top line: Month(TransDate) Sort Ascending In the next line: Format([TransDate],"mmmm") Select Group Header and Group Footer at the bottom of the dialog box, and close the dialog box. In the Report, place in the group header a text box bound to TransDate. Set its format to "mmmm". In the group footer, place a combo box with its Control Source set to: =Sum([TransDate]) This is one of several options you have. It can be refined or adjusted as needed. In any case, the report can use the simple query I showed. If the idea is to show a different range of months than this month and last month, that can be done. Non-contiguous months are more difficult, but can be done also. Access has a lot of built-in capabilities that can be harnessed to make your task simpler. Perhaps I have misread the situation, but I am quite certain now that you were taking the long way around. Bruce, [quoted text clipped - 72 lines] Many thanks, Kay |
#8
|
|||
|
|||
Help with IIF expression
I don't have Access 2007 here. I'll try to take a look tonight to see how
this works in Access 2007. Maybe somebody else will jump in. AccessKay wrote: Bruce, I got stuck on the part where you said “On the next line” where I should enter Format([TransDate],"mmmm"). I don’t think I have a dialog box. I’m using 2007 so all that sorting and grouping stuff is at the bottom. I put the rest in though. I never knew you could put a combo box in a report! I’m not yet sure of its purpose because it’s a number I don’t recognize (not the sum of ODC_Cost). And that’s neat how the quarter shows up. But this isn’t what I wanted to do in this case though I hope to use this for my current month reports. I was needing to build a variance report where the user can pick any two months and the report will show those months along with the variance. I have to also do this for Current Month in Current Year compared with the same Previous Year’s Month. And then the current Qtr vs the previous year’s Qtr. That’s how I came up with my complicated assortment of queries I don’t want to take up too much of your time. So if it’s too long of a process to make suggestions about how I can get what I just mentioned, then I just need to know how in my report, I can get the Headings for the ODC Cost to show the months chosen from the parameter. I wish I knew how to do this better. I’ve read all kinds of stuff but can’t get anything specific to what I’m trying to do. I really do appreciate the help! It seems the first three queries are intended to retrieve records from this month and last month. The way the parameter is set up the user could specify [quoted text clipped - 49 lines] Many thanks, Kay -- Message posted via http://www.accessmonster.com |
#9
|
|||
|
|||
Help with IIF expression
I made a mistake. I should have said text box, not combo box. There is not
much point to a combo box on a report. I checked Access 2007 at home, but by mistake I checked the Index dialog rather than Sorting and Grouping. However, I found this in a posting: "From the report design, click on the Design tab in the ribbon and then the 'Group and Sort' icon. You will see the sorting and grouping levels at the bottom of the screen -- you can define them by clicking on the 'Add a Group' or 'Add a Sort' button" A direct way to get non-contiguous months is to have something like this as the criteria for the date field: (Between [Month 1 start] And [Month 1 end]) Or (Between [Month 2 start] And [Month 2 end]) It would be necessary to use the complete date. Better would be to make an unbound form frmParam, on which you would have 2 unbound combo boxes for month and another 2 for year. Make a two-field Month table like this: MoNum MoName 1 January 2 February etc. Create a query based on the table. Sort ascending on MoNum. Use the query as the Row Source for the month combo boxes. Set the Bound Column to 1, the Column Count to 2, and the Column Widths to something like 0";1". For the Year combo boxes, make a query something like: SELECT DISTINCT Year([TransDate]) AS TransYear FROM TableName ORDER BY Year([TransDate]) Use the query as the Row Source for the year combo boxes. The month combo boxes are cboMo1, cboMo2. The year combo boxes are cboY1 and cboY2. Call them what you like, but these are the names I will use. In the report's Open event: DoCmd OpenForm "frmParam",WindowMode:=acDialog Have the user select the first and second month and year. Place a command button on frmParam with the Click event: Me.Form.Visible = False In the query, the criteria for TransDate: Between (DateSerial(Forms!frmParam!cboY1,Forms!frmParam!cb oMo1,1) And DateSerial(Forms!frmParam!cboY1,Forms!frmParam1!cb oMo1 + 1,0)) Or Between (DateSerial(Forms!frmParam!cboY2,Forms!frmParam1!c boMo2,1) And DateSerial (Forms!frmParam!cboY2,Forms!frmParam!cboMo2 + 1,0)) See Help for information about DateSerial. In the report's Close event: DoCmd.Close acForm,"frmParam" There are other ways you could do this. It could be that some of them improve on what I have suggested. Also, this could be refined so that, for instance, there are default month and years in the combo boxes on frmParam. For comparing quarters you could use DatePart. See Help for more information. I won't add anything more to this posting, as there is probably enough now to keep you busy for a little while. AccessKay wrote: Bruce, I got stuck on the part where you said “On the next line” where I should enter Format([TransDate],"mmmm"). I don’t think I have a dialog box. I’m using 2007 so all that sorting and grouping stuff is at the bottom. I put the rest in though. I never knew you could put a combo box in a report! I’m not yet sure of its purpose because it’s a number I don’t recognize (not the sum of ODC_Cost). And that’s neat how the quarter shows up. But this isn’t what I wanted to do in this case though I hope to use this for my current month reports. I was needing to build a variance report where the user can pick any two months and the report will show those months along with the variance. I have to also do this for Current Month in Current Year compared with the same Previous Year’s Month. And then the current Qtr vs the previous year’s Qtr. That’s how I came up with my complicated assortment of queries I don’t want to take up too much of your time. So if it’s too long of a process to make suggestions about how I can get what I just mentioned, then I just need to know how in my report, I can get the Headings for the ODC Cost to show the months chosen from the parameter. I wish I knew how to do this better. I’ve read all kinds of stuff but can’t get anything specific to what I’m trying to do. I really do appreciate the help! It seems the first three queries are intended to retrieve records from this month and last month. The way the parameter is set up the user could specify [quoted text clipped - 49 lines] Many thanks, Kay -- Message posted via http://www.accessmonster.com |
#10
|
|||
|
|||
Help with IIF expression
Thank you so much Bruce. Before I saw your post, I put a new thread out
there to find a way to build a parameter form to avoid inserting paramenters into the query. I'm going to take a look at what you sent in detail and let you know how it goes. This is a big help to me and I really appreciate it! BruceM wrote: I made a mistake. I should have said text box, not combo box. There is not much point to a combo box on a report. I checked Access 2007 at home, but by mistake I checked the Index dialog rather than Sorting and Grouping. However, I found this in a posting: "From the report design, click on the Design tab in the ribbon and then the 'Group and Sort' icon. You will see the sorting and grouping levels at the bottom of the screen -- you can define them by clicking on the 'Add a Group' or 'Add a Sort' button" A direct way to get non-contiguous months is to have something like this as the criteria for the date field: (Between [Month 1 start] And [Month 1 end]) Or (Between [Month 2 start] And [Month 2 end]) It would be necessary to use the complete date. Better would be to make an unbound form frmParam, on which you would have 2 unbound combo boxes for month and another 2 for year. Make a two-field Month table like this: MoNum MoName 1 January 2 February etc. Create a query based on the table. Sort ascending on MoNum. Use the query as the Row Source for the month combo boxes. Set the Bound Column to 1, the Column Count to 2, and the Column Widths to something like 0";1". For the Year combo boxes, make a query something like: SELECT DISTINCT Year([TransDate]) AS TransYear FROM TableName ORDER BY Year([TransDate]) Use the query as the Row Source for the year combo boxes. The month combo boxes are cboMo1, cboMo2. The year combo boxes are cboY1 and cboY2. Call them what you like, but these are the names I will use. In the report's Open event: DoCmd OpenForm "frmParam",WindowMode:=acDialog Have the user select the first and second month and year. Place a command button on frmParam with the Click event: Me.Form.Visible = False In the query, the criteria for TransDate: Between (DateSerial(Forms!frmParam!cboY1,Forms!frmParam!cb oMo1,1) And DateSerial(Forms!frmParam!cboY1,Forms!frmParam1!c boMo1 + 1,0)) Or Between (DateSerial(Forms!frmParam!cboY2,Forms!frmParam1! cboMo2,1) And DateSerial (Forms!frmParam!cboY2,Forms!frmParam!cboMo2 + 1,0)) See Help for information about DateSerial. In the report's Close event: DoCmd.Close acForm,"frmParam" There are other ways you could do this. It could be that some of them improve on what I have suggested. Also, this could be refined so that, for instance, there are default month and years in the combo boxes on frmParam. For comparing quarters you could use DatePart. See Help for more information. I won't add anything more to this posting, as there is probably enough now to keep you busy for a little while. Bruce, [quoted text clipped - 28 lines] Many thanks, Kay -- Message posted via http://www.accessmonster.com |
|
Thread Tools | |
Display Modes | |
|
|