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
|
|||
|
|||
Report on a selected field value
Hi,
I've been learning access by the seat-of-my-pants at work here, and I've come across a couple of stumbling blocks. I hope you all can help. This reports question is actually the simplest of my issues (I think), but my other issues are with forms and queries/tables, so I'll post elsewhere for them. Basically, let's say I have a report on Wal-Mart sales. Each sale in the Wal-Mart table is linked to a department, and to a location (separate tables with data about the departments and locations are joined). I currently have a report ordered by Location (header and footer), department (header and footer), and sale item (with various other data about it)--no header and footer on that one as it's part of the tabular section of the report. Now, each Wal-Mart location is inside one large report, called up by the switchboard item "Location Report." There is also a "Department Report," but I'm assuming the same answer will work for both... My boss wants each location to be in a separate report. I'm hoping I can make the switchboard button cause a query (or whatever the right function is) that makes you CHOOSE a location (or "all") from a drop-down list or some other list. Can you tell me how to make this happen? Second option is to have 9 (# of locations) different "Location Reports," but that means 8 more switchboard items and 8 more reports in the database, and that's just for the location reports. Not ideal. Plus, I don't even know how to do this. Please help! Thanks! -Aaron |
#2
|
|||
|
|||
Report on a selected field value
This is fairly straight forward. You need a combo box to list all your
locations. If you have a table that has each location as a unique record, use the location field from that table in the row source. If you only have tables where the location code or name is listed multiple times, you can boil it down to one row per location. SELECT DISTINCT [location] FROM LocationTable; Then to filter the report, use the Where argument of the OpenReport method to return only those records for the selected location. This you can do with the Click event of a command button: Dim strWhere As String strWhere = "[location] = '" & Me.MyCombo & "'" Docmd OpenReport, "LocationReport", , , strWhere Now, there are two things that make Access fairly simple to start using if you are not familiar with VBA. They are the Switchboard and Macros. There are also two things you should wiene yourself from as fast as you can because they limit the functionality you can achieve with Access. They are the Switchboard and Macros. What I have described above should be done in a small form. You can open the form from the switchboard, but you should put the combo box to select the location and the command button to run the report on the form. "Aaron" wrote: Hi, I've been learning access by the seat-of-my-pants at work here, and I've come across a couple of stumbling blocks. I hope you all can help. This reports question is actually the simplest of my issues (I think), but my other issues are with forms and queries/tables, so I'll post elsewhere for them. Basically, let's say I have a report on Wal-Mart sales. Each sale in the Wal-Mart table is linked to a department, and to a location (separate tables with data about the departments and locations are joined). I currently have a report ordered by Location (header and footer), department (header and footer), and sale item (with various other data about it)--no header and footer on that one as it's part of the tabular section of the report. Now, each Wal-Mart location is inside one large report, called up by the switchboard item "Location Report." There is also a "Department Report," but I'm assuming the same answer will work for both... My boss wants each location to be in a separate report. I'm hoping I can make the switchboard button cause a query (or whatever the right function is) that makes you CHOOSE a location (or "all") from a drop-down list or some other list. Can you tell me how to make this happen? Second option is to have 9 (# of locations) different "Location Reports," but that means 8 more switchboard items and 8 more reports in the database, and that's just for the location reports. Not ideal. Plus, I don't even know how to do this. Please help! Thanks! -Aaron |
#3
|
|||
|
|||
Report on a selected field value
Aaron wrote:
I've been learning access by the seat-of-my-pants at work here, and I've come across a couple of stumbling blocks. I hope you all can help. This reports question is actually the simplest of my issues (I think), but my other issues are with forms and queries/tables, so I'll post elsewhere for them. Basically, let's say I have a report on Wal-Mart sales. Each sale in the Wal-Mart table is linked to a department, and to a location (separate tables with data about the departments and locations are joined). I currently have a report ordered by Location (header and footer), department (header and footer), and sale item (with various other data about it)--no header and footer on that one as it's part of the tabular section of the report. Now, each Wal-Mart location is inside one large report, called up by the switchboard item "Location Report." There is also a "Department Report," but I'm assuming the same answer will work for both... My boss wants each location to be in a separate report. I'm hoping I can make the switchboard button cause a query (or whatever the right function is) that makes you CHOOSE a location (or "all") from a drop-down list or some other list. Can you tell me how to make this happen? Second option is to have 9 (# of locations) different "Location Reports," but that means 8 more switchboard items and 8 more reports in the database, and that's just for the location reports. Not ideal. Plus, I don't even know how to do this. Your "second option" is not an option, but a reseipe for a maintenance disaster ;-) The usual way to select data for a report is to create an unbound form with a combo box where users can select the location and a button that opens the report. The button's Click event procedure would then specify the report's where condition in the combo box: Dim strWhere As String If Not IsNull(cboLocation) Then strWhere = "[location field name] = " + cboLocation End If DoCmd.OpenReport "nameofreport", acViewPreview, , strWhere -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Report on a selected field value
Marshall Barton wrote: Ok, I combined the code that both of you were writing to get me something that works...sort of: Private Sub Command2_Click() Dim strWhere As String If Not IsNull(EIIList) Then strWhere = "[EII_Acronym] = " ' & EIIList & ' End If DoCmd.OpenReport "EII_Report", acViewPreview, strWhere End Sub Command2 is the button name, as best I can tell. EIIList is the name of the combo box, as best I can tell (I think I renamed it correctly) EII_Report is the Report I'm trying to call. EII_Acronym is the field I want the report based on. I'm still getting the entire report, instead of just the part of the report for that EII_Acronym. Why? Note: I also made it an "On Change" procedure for the combo box itself. Same result. -Aaron Aaron wrote: I've been learning access by the seat-of-my-pants at work here, and I've come across a couple of stumbling blocks. I hope you all can help. This reports question is actually the simplest of my issues (I think), but my other issues are with forms and queries/tables, so I'll post elsewhere for them. Basically, let's say I have a report on Wal-Mart sales. Each sale in the Wal-Mart table is linked to a department, and to a location (separate tables with data about the departments and locations are joined). I currently have a report ordered by Location (header and footer), department (header and footer), and sale item (with various other data about it)--no header and footer on that one as it's part of the tabular section of the report. Now, each Wal-Mart location is inside one large report, called up by the switchboard item "Location Report." There is also a "Department Report," but I'm assuming the same answer will work for both... My boss wants each location to be in a separate report. I'm hoping I can make the switchboard button cause a query (or whatever the right function is) that makes you CHOOSE a location (or "all") from a drop-down list or some other list. Can you tell me how to make this happen? Second option is to have 9 (# of locations) different "Location Reports," but that means 8 more switchboard items and 8 more reports in the database, and that's just for the location reports. Not ideal. Plus, I don't even know how to do this. Your "second option" is not an option, but a reseipe for a maintenance disaster ;-) The usual way to select data for a report is to create an unbound form with a combo box where users can select the location and a button that opens the report. The button's Click event procedure would then specify the report's where condition in the combo box: Dim strWhere As String If Not IsNull(cboLocation) Then strWhere = "[location field name] = " + cboLocation End If DoCmd.OpenReport "nameofreport", acViewPreview, , strWhere -- Marsh MVP [MS Access] |
#5
|
|||
|
|||
Report on a selected field value
When you post code with a question, please use Copy/Paste so
we don't waste time chasing typos. You have some errors in the posted code. First, the quotes are wrong. If EII_Acronym is a text field, it should be: strWhere = "[EII_Acronym] = '" & EIIList & "' " or if EII_Acronym might possible contain an apostrophe, use: strWhere = "[EII_Acronym] = """ & EIIList & """ " If EII_Acronym is a mumeric type field, use: strWhere = "[EII_Acronym] = " & EIIList The other error I spotted is the wrong number of commas in the OpenReport line: DoCmd.OpenReport "EII_Report", acViewPreview, , strWhere -- Marsh MVP [MS Access] Aaron wrote: Ok, I combined the code that both of you were writing to get me something that works...sort of: Private Sub Command2_Click() Dim strWhere As String If Not IsNull(EIIList) Then strWhere = "[EII_Acronym] = " ' & EIIList & ' End If DoCmd.OpenReport "EII_Report", acViewPreview, strWhere End Sub Command2 is the button name, as best I can tell. EIIList is the name of the combo box, as best I can tell (I think I renamed it correctly) EII_Report is the Report I'm trying to call. EII_Acronym is the field I want the report based on. I'm still getting the entire report, instead of just the part of the report for that EII_Acronym. Why? Note: I also made it an "On Change" procedure for the combo box itself. Same result. Aaron wrote: I've been learning access by the seat-of-my-pants at work here, and I've come across a couple of stumbling blocks. I hope you all can help. This reports question is actually the simplest of my issues (I think), but my other issues are with forms and queries/tables, so I'll post elsewhere for them. Basically, let's say I have a report on Wal-Mart sales. Each sale in the Wal-Mart table is linked to a department, and to a location (separate tables with data about the departments and locations are joined). I currently have a report ordered by Location (header and footer), department (header and footer), and sale item (with various other data about it)--no header and footer on that one as it's part of the tabular section of the report. Now, each Wal-Mart location is inside one large report, called up by the switchboard item "Location Report." There is also a "Department Report," but I'm assuming the same answer will work for both... My boss wants each location to be in a separate report. I'm hoping I can make the switchboard button cause a query (or whatever the right function is) that makes you CHOOSE a location (or "all") from a drop-down list or some other list. Can you tell me how to make this happen? Second option is to have 9 (# of locations) different "Location Reports," but that means 8 more switchboard items and 8 more reports in the database, and that's just for the location reports. Not ideal. Plus, I don't even know how to do this. Marshall Barton wrote: Your "second option" is not an option, but a reseipe for a maintenance disaster ;-) The usual way to select data for a report is to create an unbound form with a combo box where users can select the location and a button that opens the report. The button's Click event procedure would then specify the report's where condition in the combo box: Dim strWhere As String If Not IsNull(cboLocation) Then strWhere = "[location field name] = " + cboLocation End If DoCmd.OpenReport "nameofreport", acViewPreview, , strWhere |
#6
|
|||
|
|||
Report on a selected field value
Marshall Barton wrote: When you post code with a question, please use Copy/Paste so we don't waste time chasing typos. I DID use copy/paste. I just modified the code that was posted because it wasn't working for me. Sorry for being new at programming in Visual Basic/SQL, but I believe I already indicated that I was new to this in my original post. So please don't assume I'm trying to make this harder for you. I WANT your help. I'll try this below. What was posted before wasn't working, but perhaps with the extra explanation you provide below, I can sort it out. Thank you. I'll let you know if it works! -Aaron You have some errors in the posted code. First, the quotes are wrong. If EII_Acronym is a text field, it should be: strWhere = "[EII_Acronym] = '" & EIIList & "' " or if EII_Acronym might possible contain an apostrophe, use: strWhere = "[EII_Acronym] = """ & EIIList & """ " If EII_Acronym is a mumeric type field, use: strWhere = "[EII_Acronym] = " & EIIList The other error I spotted is the wrong number of commas in the OpenReport line: DoCmd.OpenReport "EII_Report", acViewPreview, , strWhere -- Marsh MVP [MS Access] Aaron wrote: Ok, I combined the code that both of you were writing to get me something that works...sort of: Private Sub Command2_Click() Dim strWhere As String If Not IsNull(EIIList) Then strWhere = "[EII_Acronym] = " ' & EIIList & ' End If DoCmd.OpenReport "EII_Report", acViewPreview, strWhere End Sub Command2 is the button name, as best I can tell. EIIList is the name of the combo box, as best I can tell (I think I renamed it correctly) EII_Report is the Report I'm trying to call. EII_Acronym is the field I want the report based on. I'm still getting the entire report, instead of just the part of the report for that EII_Acronym. Why? Note: I also made it an "On Change" procedure for the combo box itself. Same result. Aaron wrote: I've been learning access by the seat-of-my-pants at work here, and I've come across a couple of stumbling blocks. I hope you all can help. This reports question is actually the simplest of my issues (I think), but my other issues are with forms and queries/tables, so I'll post elsewhere for them. Basically, let's say I have a report on Wal-Mart sales. Each sale in the Wal-Mart table is linked to a department, and to a location (separate tables with data about the departments and locations are joined). I currently have a report ordered by Location (header and footer), department (header and footer), and sale item (with various other data about it)--no header and footer on that one as it's part of the tabular section of the report. Now, each Wal-Mart location is inside one large report, called up by the switchboard item "Location Report." There is also a "Department Report," but I'm assuming the same answer will work for both... My boss wants each location to be in a separate report. I'm hoping I can make the switchboard button cause a query (or whatever the right function is) that makes you CHOOSE a location (or "all") from a drop-down list or some other list. Can you tell me how to make this happen? Second option is to have 9 (# of locations) different "Location Reports," but that means 8 more switchboard items and 8 more reports in the database, and that's just for the location reports. Not ideal. Plus, I don't even know how to do this. Marshall Barton wrote: Your "second option" is not an option, but a reseipe for a maintenance disaster ;-) The usual way to select data for a report is to create an unbound form with a combo box where users can select the location and a button that opens the report. The button's Click event procedure would then specify the report's where condition in the combo box: Dim strWhere As String If Not IsNull(cboLocation) Then strWhere = "[location field name] = " + cboLocation End If DoCmd.OpenReport "nameofreport", acViewPreview, , strWhere |
#7
|
|||
|
|||
Report on a selected field value
Marshall Barton wrote: When you post code with a question, please use Copy/Paste so we don't waste time chasing typos. You have some errors in the posted code. First, the quotes are wrong. If EII_Acronym is a text field, it should be: strWhere = "[EII_Acronym] = '" & EIIList & "' " or if EII_Acronym might possible contain an apostrophe, use: strWhere = "[EII_Acronym] = """ & EIIList & """ " Bingo! This one did it. The one before probably would work, but I couldn't figure out where the apostrophes and quotes began and ended... I hope the second one won't cause errors. Thank you thank you thank you! -Aaron If EII_Acronym is a mumeric type field, use: strWhere = "[EII_Acronym] = " & EIIList The other error I spotted is the wrong number of commas in the OpenReport line: DoCmd.OpenReport "EII_Report", acViewPreview, , strWhere -- Marsh MVP [MS Access] Aaron wrote: Ok, I combined the code that both of you were writing to get me something that works...sort of: Private Sub Command2_Click() Dim strWhere As String If Not IsNull(EIIList) Then strWhere = "[EII_Acronym] = " ' & EIIList & ' End If DoCmd.OpenReport "EII_Report", acViewPreview, strWhere End Sub Command2 is the button name, as best I can tell. EIIList is the name of the combo box, as best I can tell (I think I renamed it correctly) EII_Report is the Report I'm trying to call. EII_Acronym is the field I want the report based on. I'm still getting the entire report, instead of just the part of the report for that EII_Acronym. Why? Note: I also made it an "On Change" procedure for the combo box itself. Same result. Aaron wrote: I've been learning access by the seat-of-my-pants at work here, and I've come across a couple of stumbling blocks. I hope you all can help. This reports question is actually the simplest of my issues (I think), but my other issues are with forms and queries/tables, so I'll post elsewhere for them. Basically, let's say I have a report on Wal-Mart sales. Each sale in the Wal-Mart table is linked to a department, and to a location (separate tables with data about the departments and locations are joined). I currently have a report ordered by Location (header and footer), department (header and footer), and sale item (with various other data about it)--no header and footer on that one as it's part of the tabular section of the report. Now, each Wal-Mart location is inside one large report, called up by the switchboard item "Location Report." There is also a "Department Report," but I'm assuming the same answer will work for both... My boss wants each location to be in a separate report. I'm hoping I can make the switchboard button cause a query (or whatever the right function is) that makes you CHOOSE a location (or "all") from a drop-down list or some other list. Can you tell me how to make this happen? Second option is to have 9 (# of locations) different "Location Reports," but that means 8 more switchboard items and 8 more reports in the database, and that's just for the location reports. Not ideal. Plus, I don't even know how to do this. Marshall Barton wrote: Your "second option" is not an option, but a reseipe for a maintenance disaster ;-) The usual way to select data for a report is to create an unbound form with a combo box where users can select the location and a button that opens the report. The button's Click event procedure would then specify the report's where condition in the combo box: Dim strWhere As String If Not IsNull(cboLocation) Then strWhere = "[location field name] = " + cboLocation End If DoCmd.OpenReport "nameofreport", acViewPreview, , strWhere |
#8
|
|||
|
|||
Report on a selected field value
Sorry if my advice didn't come across as intended. I was
just trying to help you help us help you reach a satisfactory solution ;-) Quoting quotes is a confusing topic. Just try to wrap your head around the following explanation if you had any thoughts of it being straightforward ;-) A general rule is to use two double quotes where you want one double quote inside an outer set of double quotes. In queries, but not in VBA, an alternative is to use a single quote (apostrophe) inside the outer double quotes instead of two double quotes (or vice versa). This makes things a little easier to read, but the odds of a name containing an apostrophe (e.g. O'Hare) are fairly high so sometimes you can run into trouble using this syntax. -- Marsh MVP [MS Access] Aaron wrote: Marshall Barton wrote: When you post code with a question, please use Copy/Paste so we don't waste time chasing typos. I DID use copy/paste. I just modified the code that was posted because it wasn't working for me. Sorry for being new at programming in Visual Basic/SQL, but I believe I already indicated that I was new to this in my original post. So please don't assume I'm trying to make this harder for you. I WANT your help. I'll try this below. What was posted before wasn't working, but perhaps with the extra explanation you provide below, I can sort it out. You have some errors in the posted code. First, the quotes are wrong. If EII_Acronym is a text field, it should be: strWhere = "[EII_Acronym] = '" & EIIList & "' " or if EII_Acronym might possible contain an apostrophe, use: strWhere = "[EII_Acronym] = """ & EIIList & """ " If EII_Acronym is a mumeric type field, use: strWhere = "[EII_Acronym] = " & EIIList The other error I spotted is the wrong number of commas in the OpenReport line: DoCmd.OpenReport "EII_Report", acViewPreview, , strWhere Aaron wrote: Ok, I combined the code that both of you were writing to get me something that works...sort of: Private Sub Command2_Click() Dim strWhere As String If Not IsNull(EIIList) Then strWhere = "[EII_Acronym] = " ' & EIIList & ' End If DoCmd.OpenReport "EII_Report", acViewPreview, strWhere End Sub Command2 is the button name, as best I can tell. EIIList is the name of the combo box, as best I can tell (I think I renamed it correctly) EII_Report is the Report I'm trying to call. EII_Acronym is the field I want the report based on. I'm still getting the entire report, instead of just the part of the report for that EII_Acronym. Why? Note: I also made it an "On Change" procedure for the combo box itself. Same result. Aaron wrote: I've been learning access by the seat-of-my-pants at work here, and I've come across a couple of stumbling blocks. I hope you all can help. This reports question is actually the simplest of my issues (I think), but my other issues are with forms and queries/tables, so I'll post elsewhere for them. Basically, let's say I have a report on Wal-Mart sales. Each sale in the Wal-Mart table is linked to a department, and to a location (separate tables with data about the departments and locations are joined). I currently have a report ordered by Location (header and footer), department (header and footer), and sale item (with various other data about it)--no header and footer on that one as it's part of the tabular section of the report. Now, each Wal-Mart location is inside one large report, called up by the switchboard item "Location Report." There is also a "Department Report," but I'm assuming the same answer will work for both... My boss wants each location to be in a separate report. I'm hoping I can make the switchboard button cause a query (or whatever the right function is) that makes you CHOOSE a location (or "all") from a drop-down list or some other list. Can you tell me how to make this happen? Second option is to have 9 (# of locations) different "Location Reports," but that means 8 more switchboard items and 8 more reports in the database, and that's just for the location reports. Not ideal. Plus, I don't even know how to do this. Marshall Barton wrote: Your "second option" is not an option, but a reseipe for a maintenance disaster ;-) The usual way to select data for a report is to create an unbound form with a combo box where users can select the location and a button that opens the report. The button's Click event procedure would then specify the report's where condition in the combo box: Dim strWhere As String If Not IsNull(cboLocation) Then strWhere = "[location field name] = " + cboLocation End If DoCmd.OpenReport "nameofreport", acViewPreview, , strWhere |
#9
|
|||
|
|||
Report on a selected field value
It's good to hear that it's working.
One way to figure out which quotes are where is to click in their area and use the right and left arrow keys to move through them one character at a time. As I kind of implied earlier, I personally prefer the syntax that you chose to use for the same reason. The only way that could cause a problem is if the value in EIIList contained a double quote character, which should be extemely unlikely. -- Marsh MVP [MS Access] Aaron wrote: Marshall Barton wrote: When you post code with a question, please use Copy/Paste so we don't waste time chasing typos. You have some errors in the posted code. First, the quotes are wrong. If EII_Acronym is a text field, it should be: strWhere = "[EII_Acronym] = '" & EIIList & "' " or if EII_Acronym might possible contain an apostrophe, use: strWhere = "[EII_Acronym] = """ & EIIList & """ " Bingo! This one did it. The one before probably would work, but I couldn't figure out where the apostrophes and quotes began and ended... I hope the second one won't cause errors. Thank you thank you thank you! If EII_Acronym is a mumeric type field, use: strWhere = "[EII_Acronym] = " & EIIList The other error I spotted is the wrong number of commas in the OpenReport line: DoCmd.OpenReport "EII_Report", acViewPreview, , strWhere |
Thread Tools | |
Display Modes | |
|
|