Group on a field without sorting

[jaskew]

I need to break my report up into groups. I have no problem doing this, except that Access wants to add its own sort method to the group. This report draws from a query that has a rather complex set of expressions that sort the field the way I need it. I am just trying to figure out a way to let the report group on the field without sorting it.

For clarification, the field appears similar to an ip address, i.e. #.#.#.# The normal sort methods that Access uses are not capable of properly sorting this type of format.

 

[boblarson]

I may be wrong here but I think you will need to create a field in your query for each part of the field that is separated by the periods. And then you would create a group (although not with a header or footer) for each in the order that they come. And then AFTER that you would create a group based on the entire string.

 

[jaskew]

That's clever. I'll give it a shot. I just wonder if I can then keep those fields out of my report.

 

[vbaInet]

You can also, in a new column:

Alias: CLng(Replace([Field], ".", ""))

Sort on this field.

 

[boblarson]

Now that's the way to go!

You can also, in a new column:

Alias: CLng(Replace([Field], ".", ""))

Sort on this field.

Oooh, an even BETTER suggestion! I like it. Glad you're on the ball.

 

[jaskew]

The way Bob mentioned work. Then I just placed a transparent box over the information I didn't want. Perhaps not the cleanest way, but it worked. I'm curious, though, how would I enter the Alias expression? Would that be a new column in the source query?

 

[boblarson]

Would that be a new column in the source query?

Yes, it would.

 

[jaskew]

Unfortunately, the Replace method doesn't work for what I'm using. The structure I have is basically a numbering system for the outline of a report. For example, 1.1.1.1 = Corrective Maintenance.System Level.Identify Error.[First task at this level]. So, wind up with some weird numberings like 1.2.3.367 and 4.2.1.0. I need 1.2.3.367 to precede 4.2.1.0. Fortunately, I found out how to do this awhile back because the structure is similar to an IP address. If anyone is interested, here's the columns I added to my query:

Partl: Val(Left([Task.Task #],(InStr(1,[Task.Task #],".")-1)))
Partll: Val(Mid([Task.Task #],InStr(1,[Task.Task #],".")+1,InStr(InStr(1,[Task.Task #],".")+1,[Task.Task #],".")-InStr(1,[Task.Task #],".")-1))
Partlll: Val(Mid([Task.Task #],InStr(InStr(1,[Task.Task #],".")+1,[Task.Task #],".")+1,InStr(InStr(InStr(1,[Task.Task #],".")+1,[Task.Task #],".")+1,[Task.Task #],".")-InStr(InStr(1,[Task.Task #],".")+1,[Task.Task #],".")-1))
PartlV: Val(Right([Task.Task #],Len([Task.Task #])-InStr(InStr(InStr(1,[Task.Task #],".")+1,[Task.Task #],".")+1,[Task.Task #],".")))

 

[boblarson]

Glad you got it to work for you though. Thanks for posting back with what you ended up with.

 

[vbaInet]

Just a quick suggestion, PartIV can be:

mid([Task.Task #], instrrev([Task.Task #], ".") + 1)