Group (not sort) on alpha-numeric text field

[UnrulyJulie]

The report is for physical inventory sorted by bin number, so I have more than one item stored in a bin, and need to group by bin, an alpha-numeric text field.

I cannot change the field type. I tried the solution in the thread here:

http://www.access-programmers.co.uk/forums/showthread.php?t=236440

I created an expression using MyGroup rather than MySort - I can't get it to work. I've downloaded and looked at the sample db and have everything set up the same, but simply changed MySort to MyGroup and called it as the group by.

I've read other threads, but they all deal with sorting, not grouping.

Any ideas?

Thanks,
Julie

 

[RainLover]

Sorting in Reports is different to Form.

Reports have their own sorting Tools.

I don't know what version you are using so more advise may be a waste.

Just go to HELP or google and search Sorting and Grouping in Reports.

Hope this helps.

 

[UnrulyJulie]

No, that's no help at all. I have been looking. I don't even know what you meant. This is the Report Forum, right? It has nothing to do with forms.

Thanks,
Julie

 

[RainLover]

The answer had nothing to do with Forms.

It was about Reports.

Did you not find anything in your search.

Would help I we knew what version Access you have.

 

[RainLover]

Try going to View, Sorting and Grouping.

 

[UnrulyJulie]

OK. MS Access version 2007.

The problem was very well outlined in the previous post that I linked to. I need to Group records based on a bin number/alpha-number text field.
For example:
1
2
3
A-1
11
21
1-B

I am pulling all of the records together that have the same bin number, that's no problem. The trouble comes in the ordering of the groups (the bin number) in the report- I get:
1
11
1-B
2
21
3

I need
1
1-B
2
3
21
A-1 (the ones starting with an alpha could go first or last, makes no difference.)

So in the previous post, a solution was raised for a sort issue, using MySort. I tried creating the same solution using MyGroup and defining as instructed in that solution, but it didn't work. I responded to the thread but never heard anything from the original solver, so thought I'd repost, since this isn't really a sort issue, but getting an alpha-num. text field to print numerically at the group level, not the sort level.

Thanks,
Julie

 

[RainLover]

My best solution would be to separate into two separate Fields. One Numeric and the other Alpha.
You can always join them for display purposes.
Other than that why doesn’t the advise from the other help?

 

[Pat Hartman]

Grouping implies sorting. How else would Access be able to get all like values together?

Take a look again at the suggestions in the post you referred to.

 

[Brianwarnock]

The way I would attempt this is in the query on which the Report is based I would include binnum:Val(Bin)
them this would be my first level grouping in the report with Bin as level 2
I would then make the binnum level not visible.

Bins starting with non numerics would be first as the Val would return 0.

Brian

 

[UnrulyJulie]

Thanks for your response, Brian.

I attempted this. It seemed to work! But for one thing I should have mentioned. Some of these bin numbers use a dash. Ugh.

Any way to modify this to ignore it, or add it into the equation if it is present?

I have 27 inventory locations across the country that all use this field a little differently - otherwise I'd put some control on it for format. This is a massive shrub of a DB (instead of a nicely shaped tree) handed down to me, so making changes like this would be difficult across the board.

Any other suggestions would be appreciated!
Thanks,
Julie

 

[Brianwarnock]

Odd, after posting I created a simple small DB including all the bins in you post and it appeared to work. Preparing my evening meal at the mo but later I will find it and check.

Brian

 

[UnrulyJulie]

Wow you are fast! I realized I had made a mistake so edited my initial response. But you had already replied. If yours works with dashes, I'm intrigued. Mine skips them completely and doesn't shoe them, but the ones that are showing are correctly ordered now.

Thanks Brian!!

 

[Brianwarnock]

Here is my little tester
1-a comes after 1a.

As I said leading alph come first then numerics in which 1 1a 1-a 1b 2 etc is the order.

Hope it's what you want.

Brian

 

[Brianwarnock]

After rereading your post added a-1, it comes between a1 and a2,
plus -a and -1 they come first as -1 -a

Brian

 

[UnrulyJulie]

I have it working in datasheet view. Almost. I get:

A2
A-1
plt-1 <------ this is the problem.
D-7
22
23
222

p is after D... is it multiple alphas? Can't be case sensitive. I didn't realize there were any records with more than one alpha... let me know if that matters. I'm sure it does. I am not sure on how to adjust for it.

However, the report still only shows A-1... then 22, 23, 222. Everything else is skipped. Checking formatting on the layout - troublesome to see something in common with the missing records.

Thanks so much for your help,
Julie

 

[Brianwarnock]

p is after D... is it multiple alphas?

No the Bin group sort is an alpha sort within the grouping caused by the val, when all leading alpha return a 0

It works OK for me c-5 FED-4 pit-2 x-1

Not sure, or rather, don't know why you are having problems.

Brian

 

[Brianwarnock]

Spaces

If you have spaces in the Bin it will affect the sort order, not sure what you can do about that especially if they are part of the requirement.

Brian

 

[Brianwarnock]

OK if you are allowed to remove spaces then use replace
In my query I coded Bin:Replace(tablebin.bin," ","") and all is fine again.

Brian

 

[Brianwarnock]

I notice that you are not on now, I've gotta go and get things ready for a days walking tomorrow so will look in on Friday.

Hope it all goes well

Brian

 

[UnrulyJulie]

No spaces. I can't figure it out. It is set up exactly as yours is. Of course, there's a lot more to the query itself, but I can't figure out what's wrong.

Thanks for your help, though. I appreciate your time.

Julie