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
|
|||
|
|||
Breakdown my budget codes by type.
Hi. I have a database with about 20 budget codes. They are set up in
sequence like so... 105-xxx Construction 110-xxx Design 115-xxx Entitlement 120-xxx Fees/Assessments etc. The 'xxx' is just a chronologic count. So for the first category I have budget codes like so... 105-100 105-110 105-120 etc. What I would like to do in my query is to segregate the budget codes by category so that I can total all the 105-xxx and the 110-xxx, etc. I am thinking that it would be an IIF statement to tell Access to look at the first three characters of the budget code. Does that seem right? Any help to get me headed in the right direction is greatly appreciated. Please let me know if you need more information. My field budBudgetCodeID is a text field. THANKS Stacey |
#2
|
|||
|
|||
Breakdown my budget codes by type.
Stacey
If you are saying that the first three characters are what you need to focus on, then you don't need to use an IIF() statement at all! Instead, look into using the Left() function to 'see' those left-most three characters. You can create a new field in your query, something like: NewField: Left([YourFieldName],3) then use a totals query to group by that new field. Regards Jeff Boyce Microsoft Office/Access MVP "Stacey Crowhurst" wrote in message ... Hi. I have a database with about 20 budget codes. They are set up in sequence like so... 105-xxx Construction 110-xxx Design 115-xxx Entitlement 120-xxx Fees/Assessments etc. The 'xxx' is just a chronologic count. So for the first category I have budget codes like so... 105-100 105-110 105-120 etc. What I would like to do in my query is to segregate the budget codes by category so that I can total all the 105-xxx and the 110-xxx, etc. I am thinking that it would be an IIF statement to tell Access to look at the first three characters of the budget code. Does that seem right? Any help to get me headed in the right direction is greatly appreciated. Please let me know if you need more information. My field budBudgetCodeID is a text field. THANKS Stacey |
#3
|
|||
|
|||
Breakdown my budget codes by type.
Hi Jeff.
Thank you for the incredibly simple and effective solution! Stacey "Jeff Boyce" wrote: Stacey If you are saying that the first three characters are what you need to focus on, then you don't need to use an IIF() statement at all! Instead, look into using the Left() function to 'see' those left-most three characters. You can create a new field in your query, something like: NewField: Left([YourFieldName],3) then use a totals query to group by that new field. Regards Jeff Boyce Microsoft Office/Access MVP "Stacey Crowhurst" wrote in message ... Hi. I have a database with about 20 budget codes. They are set up in sequence like so... 105-xxx Construction 110-xxx Design 115-xxx Entitlement 120-xxx Fees/Assessments etc. The 'xxx' is just a chronologic count. So for the first category I have budget codes like so... 105-100 105-110 105-120 etc. What I would like to do in my query is to segregate the budget codes by category so that I can total all the 105-xxx and the 110-xxx, etc. I am thinking that it would be an IIF statement to tell Access to look at the first three characters of the budget code. Does that seem right? Any help to get me headed in the right direction is greatly appreciated. Please let me know if you need more information. My field budBudgetCodeID is a text field. THANKS Stacey |
Thread Tools | |
Display Modes | |
|
|