Filtering in Access 2003 or 2010

[jtalbot0001]

I can see what you mean now about "there was no need to change any of the textboxes in my report", silly me, sort that now. But you are not going to believe this, but my issue is still not resolved, and it is not your fault at all as you have been a great help so far. You wouldn't have known this on the small sample file I gave you but my table is 36000 records big so far. So guess what the report is doing now? It actually is sorting your lovely group ID numbers by ascending them, so frustrating, basicaly it is doing what I didn't want it to towards the beginning of this thread, under the group and sort section in the report it only gives you an option of sorting ascending or descending (as you now know) and nothing else (so annoying as I just want it not to sort). So with your group ID numbers it is sorting them by numbers, so all IDs with numbers 1 in front come before 2. IE, as an example, group ID 1 is first, 10 is second rather than ID number 2, as number 10 has a 1 in front so it thinks that has higher priority than number 2, and 100 will come before 2 and a 1000 etc etc, and that all comes down to the forced sort in that group and sort section in the report, I just cannot believe this is so limited, I cannot be the only person on this planet that has had an issue like this? I'm trying to come out of the dark ages from using Paradox to use Access and it's been more difficult than I thought. hmmm... Oh yes, the extra notes about "" marks etc has been noted and I will rectify all that thanks! And there was a tiny difference in Query1 and 2 which was the fonts on two boxes, but very difficult to see I know. PS, that is what I think it is doing to your group numbers anyhow from what I can tell, I may be wrong, but it seems to look that way!

[June7]

Seems value returned by DFirst() is treated like a text string regardless of what the original data is and alpha sort is applied. Two ways to deal with.

1. convert the string to number, try Val or CInt function:
GroupID: Val(DFirst("[Unique ID]","allbas","[sub-title]='" & Replace([sub-title],"'","''") & "'"))
GroupID: CInt(DFirst("[Unique ID]","allbas","[sub-title]='" & Replace([sub-title],"'","''") & "'"))

2. format the value as string with fixed length:
GroupID: Format(DFirst("[Unique ID]","allbas","[sub-title]='" & Replace([sub-title],"'","''") & "'"),"000000")

The first is probably best.

[ssanfu]

Any chance that you might save the dB in A2000 format? I am curious about the report problem. I have never run into anything like that.

---------------------------
I generally don't use the wizard to create reports, unless it is a quick and dirty report or a one time thing. Plus I am kinda a control freak...
For me, it is easier to open a new report and add the fields, grouping and sorting manually.

-------------------
I still have copies of dBaseIII, dBaseIV and I think dBaseV, a version of Paradox, and even a copy of a database called ARGO (I think that was the name. I haven't looked at it for many,many years).

[June7]

Steve, have you ever sorted text data that contained numbers? Even if all digits, if the field is text then alpha sort applies and can get the results described by OP.

[ssanfu]

if the field is text then alpha sort applies

Yes, I understand that and have dealt with it.

I can't see the dB and have been having a hard time visualizing the report. as I said, I am just curious....

[June7]

The report design itself isn't unusual, really just simple grouping, no subreport, no summary calcs. It's the OP's requirement for records within each group to remain in the same order as they were entered into db that is out of the ordinary.

I do wonder how this will be impacted by new records.

[jtalbot0001]

Right, to start off, for the chap that doesn't have the db and anyone else who would like to play around with it, here is a small version but a larger file than what I gave to June7 so please download it too for yourself June7! https://www.dropbox.com/s/kfxfwdibbg4x7ms/allbas.mdb Its safe, so please don't worry!

Thanks for the 3 code lines, I tried all three and all produce the same results and that is the closest yet! to being all correct. It has an issue now with repeated Group ID's, IE, if you look inside the Query you will find it generates ID 53 for "1949 SIlver Wedding" (as an example) and where ever "1949 SIlver Wedding" is repeated in the sub-title it therefore will use the same Group ID 53, which I can understand why it is doing it but it musn't. Basicaly if all my sub-titles were never repeated throughout my table, like Silver Wedding, then my issue would be now resolved.

And to the others that are reading this June7 understands it perfectly, my db file is already in the correct order, so there was and is no need for access to sort it in the report, but so far it is locked into sorting it. If you look at the table then that is the order it needs to be in in the report. Maybe there is a way of telling the query to, after doing the Group ID thing, to then 'sort' the Unique ID's in the right order, since they are in the right order?

[jtalbot0001]

I should have said that as a result of "1949 Silver Wedding" being group ID 53 (example), and because it is a low ID number, it will generally place it first for that country as it is a low number in comparison to the rest of the particular country, or rather sub-title, ID numbers.

[June7]

Try including the unique record ID as a second level sort in the report setup.

[ssanfu]

my db file is already in the correct order, so there was and is no need for access to sort it in the report

Please re-read post #9.

Not sure of which fields you wanted to sort by on the report: I used [Country], [sub_title] and [S4]. (S4 is a calculated column I created)

Also, I renamed a lot of your fields: replaced the special characters with the underscore, deleted spaces and deleted the quotes.

[jtalbot0001]

Hi Steve, thanks for the reply and access file. I'm afraid it still isn't doing what I needed it to do. To go back to your post #9, I understand that probably most people out there will continually add new records to their database and thus the db will end up not being in order, but then you get somebody like me who has no need to really add any more records. IE you will note that my database is about stamps, and the db file is literally a catalogue of stamps from A-Z exactly how it looks if you bought one! Therefore my database is in the correct order already, and will always be in the correct order, as once I get this going (I hope) then all that will keep changing is either the prices or on the rare occasion catalogue numbers, but the basic record will always be the same. Take a look at the pictures, the first one is what your file did, it is forcing the sort on the sub-title line, which is wrong (see notes in picture), and basicaly picture two is where this whole issue arrises, and I have circled it in red. Access is at this point in time locked into sorting asscending or descending (but Microsoft hasn't built in the option of switching that off or to have the option NOT SORTED). They haven't assumed that somebody like myself already has data that has no need to be re-arranged it just needs to be left as is. On the second group level I can change it to say Unique ID for instance, then it does work and place it all in the right order. In your file you gave me, if you change in the report design the sub-title header to be a Unique ID header, then you will notice it places everything in the right order (brilliant) however, because the sub title line is not grouped it is repeating it throughout, wich looks ridiculous, I only need it mentioned once! I don't know if I am correct in saying this but from what I can see Access also does not let you group on a third level and beyond in a report!? It only seems to group on a level 1 or 2 only? As if I could group on a third level then all I would need to do is create a third group level which would be a sub-title line (if only) but that just repeats the sub title, unlike on level two where it groups it (how I want it to be).

PS: Hey June7, if I did what you mentioned correctly on you last post then it didn't work for me I'm afraid.

[June7]

The query returns ID 53 for all records with 1949 Silver Wedding as subtitle because that's what the DFirst() expression is set up to do, returns the first ID found for the subtitle. I see now that 5 countries have this same subtitle associated. So, need to modify the DFirst to return the first ID for the country/subtitle combination, like:

GroupID: Val(DFirst("[Unique ID]","allbas","[sub-title]='" & Replace([sub-title],"'","''") & "' AND Country='" & [Country] & "'"),)

Here is an alternative query:

SELECT Q.GroupID, allbas.*
FROM (SELECT First(allbas.[Unique ID]) AS GroupID, allbas.Country, allbas.[Sub-title] FROM albas GROUP BY allbas.Country, allbas.[Sub-title]) AS Q
INNER JOIN allbas ON (Q.[Sub-title] = allbas.[Sub-title]) AND (Q.Country = allbas.Country);

DMin or Min could be used instead of DFirst or First and is usually better option, I probably should have.

The report has a sort criteria using an expression which is a reference to field Identifier because Identifier is not in the query. Instead include Identifier in the query and use it as sort criteria.

[jtalbot0001]

Hey June7, thanks for the last post and I am delighted to say it actually now works!! What a relief! I used the first idea from the last post "GroupID: Val(DFirst("[Unique ID]","allbas","[sub-title]='" & Replace([sub-title],"'","''") & "' AND Country='" & [Country] & "'"),)" and then added the extra identifier column in the query and all is well! I have not tried the second part though. I wouldn't actually mind just trying it just to see since it still tries to sort on the St-Kitts - Nevis country in the reverse order from that of what's in the table (as it is only trying to sort alphabetically) but I'm not fussed about that really, that is too trivial to worry about, but maybe that second idea for whatever reason sorts that out too. Where would I put that code line if I was to try? Same place where the Group ID expression goes? I tried DMin and that still resulted in the same positive outcome just for interest sake. I will mark this thread as solved on your reply and I can't thank you enough for your help and everyone elses input!

[June7]

The alternative query is just that, it is replacement SQL statement for the saved query object. Open the query in SQL View and replace the entire SQL statement.

DMin or Min are still probably safer than DFirst or First.