report output help

[pikoy]

If anyone could lend me a hand on how to perform this:

I have a report that contains 3 data elements: Location, name, unit.

the location have these values in them: 2A, 2B, 2C, 3, 4, 5, 6A, 6B, 6C, 7, 8

How can i merge the results of 2A, 2B and 2C as "2"; and 6A, 6B and 6C as "6"?

I did a filter from the query side but it displayed 2A, 2B and 2C - not merged and I am missing the other locations ( naturally ).

Does anyone know how this could be done?

Any help is very much appreciated.

 

[DCrake]

If the locations ar eonly going to be one digit long then create another field in your query

NLocation:Left(Location,1)

And filter on that column.

 

[pikoy]

If the locations ar eonly going to be one digit long then create another field in your query

NLocation:Left(Location,1)

And filter on that column.

Drake, thank you for your time and help.

If i create another field then the other reports we have running on Data element "Location" would have to be changed to accomodate this change...

Do you know of a less invasive change that will not alter the fields... kind of an if and then clause and put in the code section... unfortunately i am not that good with VB.

example: if report for 2A is run then run 2B and 2C and merge results as 2

 

[vbaInet]

Based on what David (aka DCrake) has already mentioned, why not:

1. Create a query and order by the Location field
2. Create an aliased field that will extract the left part of the Location, or you could use Val([Location]) and that will return 2 for 2A, 2B, 2C etc...
3. Group your report on the aliased field in step 2

 

[pikoy]

I am so lost now...

So i created this ( see included code ) and its giving me syntax error... what am i doing wrong?

=Sum(IIf([LOCATION]='2AB' AND '2AC'))

 

[DCrake]

Adding an extra hidden column to a query is far less intrusive of haing to mess with SQL/VBA in your report.

In your reports underlying query you would perform a filter based on the first digit of the known location field. So if you have locations named 2AB, 2AD, 2YA, etc. then simply add another column in your query by dragging down the location code from the table and change it as follow

NumLocation:Left([LocationFieldName],1)

uncheck the tickbox so that is does not appear in the list when viewed in datasheet mode (though this is not really needed). Then in the condition row enter

=2

Now view the query in datasheet mode and you will not that only locations that commence with a 2 are visible. Nothing else has changed whatsoever.

With this bit of extended knowledge I am sure you will be able to adapt the logic to suit your needs.

 

[DCrake]

Where did you come across NrsStnInList([LOCATION])?
What version of Access are you using?
Are you actually reading what is being offered?

 

[pikoy]

Where did you come across NrsStnInList([LOCATION])?
What version of Access are you using?
Are you actually reading what is being offered?

Used your Solution and modified it a bit:

On the qry: added another field with this on

Mid([LOCATION],1,2)

with a filter criteria of "2AB"

I was able to get what i want out.

I had that code NrsStn - since its part of a drop down selection - that was a mispost.

 

[DCrake]

Mid([LOCATION],1,2) is the same as Left([Location],2)

In your example

2AB would return 2A

 

[pikoy]

Mid([LOCATION],1,2) is the same as Left([Location],2)

In your example

2AB would return 2A

Drake... thank you you have helped me alot.