Breakdown my budget codes by type.

[Stacey Crowhurst]

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

 

[Jeff Boyce]

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]

Hi Jeff.

Thank you for the incredibly simple and effective solution!

Stacey

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

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