Ascending Date order

[Darren Smith]

When I ascend the date order in the field it loses lots of rows of data does anyone know why this is?

 

[Joe4]

What is the data type of this date field?
Are you trying to do this in a query?
If so, can you post the SQL code of the query (switch the query to SQL View and copy/paste code here)?

 

[Darren Smith]

This field is Short Text.
I need it to be a date so I can filter this column for the current month

SELECT Table1.Todaydate, Table1.Tim1
FROM Table1
WHERE (((([Table1].[Todaydate])=Year([TodayDate]))=Year(Now())) AND ((Month([TodayDate]))=Month(Now())));

 

[Joe4]

What do the dates in this Short Text field look like?
Can you post a handful of examples?

 

[Darren Smith]

Like this 13/09/2021, 9/10/2021

 

[Joe4]

It seems a little odd that you have leading zeroes on the month ("09"), but not on the days ("9").
But if that is truly the case, try changing your criteria row to this:

WHERE (Right([Table1].[Todaydate],7) = Format(Date(),"mm/yyyy"))

 

[Micron]

It's text, so 13 comes before 9? It should be a date field, not a text field.

 

[Joe4]

It's text, so 13 comes before 9? It should be a date field, not a text field.

Good point. I lost site of the original question.
I noticed that if the field is text, then their query might not return all the correct records, so I updated the criteria (which looks like they are trying to return the records from the current month and year).

However, to address the sorting issue, then will also need to do more, such as:
1. Convert the value to a date (which can sometimes get a little tricky with regional settings).
2. Create another calculated field for sorting, such as one of these two options:
i. Extract the "day" part of the date (using the INSTR function), convert to a number, and sort by this.
ii. Add this calculated field to the query:

MySort: RIGHT("0" & [Table1].[Todaydate],8)

and sort by this.

 

[Micron]

I'd just make the field a date data type and all of the problems should go away without a lot of work. Perhaps that is not an option.
As for 'disappearing' data, I figured that there are probably fields with nulls (which will sort first) or empty strings (in which case I don't know the effect of sorting), and what is being characterized as an issue really isn't. I have never seen sorting "lose" records.

 

[Joe4]

I

I'd just make the field a date data type and all of the problems should go away without a lot of work. Perhaps that is not an option.
As for 'disappearing' data, I figured that there are probably fields with nulls (which will sort first) or empty strings (in which case I don't know the effect of sorting), and what is being characterized as an issue really isn't. I have never seen sorting "lose" records.

The original replies by etaf were Excel-related (he did not realize this was posted in the Access forum and not the Excel one), so they were deleted.
But the reply from the OP that was deleted mentioned this:

The thing is I am taking the Data linked to another Access Database which I cannot alter.

So it doesn't sound like changing the data type is an option.