Access SQL Collation Sequence Issue

[JimReid]

I have a problem. One table. Two fields has "01F' in some records and "01f" in others and the date. When I run a query they are mixed together. I should be able to get "01f" first and then "01F". The second order field is a date and the results is the two mixed together but with date order. Anyone know any remedies for this? I have tried 'Collation" on the Order statement but it doesn't seem to help. The default Collation sequence for Access is 1033 which is General. Don't know where to go with this.

[June7]

Review https://docs.microsoft.com/en-us/off...iveascii-order

That method will cause all upper case to sort before all lower case because ASCII value is lower.

You could build a table that assigns your own value to each letter.

I get an error trying to use COLLATE or COLLATION. How did you get query to even run?

[Welshgasman]

I have a problem. One table. Two fields has "01F' in some records and "01f" in others and the date. When I run a query they are mixed together. I should be able to get "01f" first and then "01F". The second order field is a data and the results is the two mixed together but with date order. Anyone know any remedies for this? I have tried 'Collation" on the Order statement but it doesn't seem to help. The default Collation sequence for Access is 1033 which is General. Don't know where to go with this.

Are they even meant to indicate different items, or just bad data entry?

[JimReid]

Different items.

[orange]

??Further to welshgasman's question--- what is the issue in business terms?
Perhaps you could give us 2 examples:
1--the data and format that needs "correcting"
2--the data and format that you want

[JimReid]

They are meant to be different items. One (value of '01f') is in one table and the other (value of '01F') is in the second table. I'm doing a UNION ALL to get all records from both tables and put them in order by this field. The problem is that '01f' is considered the same as '0fF' in the ORDER BY statement and since the second parameter is by date they get intermixed. They are brought into an excel sheet where each version is a separate sheet but intermixed that doesn't happen. There should be a sheet for each iteration. Make sense? With the '01f' intermixed the break to a new sheet happens too soon with more '01F' to do. Hope that helps. I did get around it by making '01f' = '000' and then it sorted before '01F' and then I trapped it in VBA and changed to '01f' to process.

[orange]

Jim,

Access by default is case insensitive -- so 'f' and 'F' are treated as the same.
Review the strComp function (string compare) to see how you could treat these as separate values.
I see you have a work around.

[JimReid]

To June/VIP:
I'm not creating the table but the query from two existing tables. Can't change the data. The reference method is to change the table not the query. I finally got the query to work with an IIF to change '01f' to '000' and '02f' to '001' and then in VBA catching them and changing them back in the program. Didn't change the actual data only the output of the query and changed back when retrieving the results. Thanks anyway.

[June7]

The referenced method does not change data stored. It provides a function that can be called in query to calculate a field that can be used to sort records.

Why would your solution require 'changing'? Can still pull raw value into query as well as the calculated.

Are 01f and 01F the only values? If you want these on separate worksheets, why UNION?