Assistance needed in getting the correct sort order

[Voyager]

Hello Experts,
I am using the below code to find the top value ( minimum value ) of a recordset but sometimes this is
not showing the correct value when I open the table I was able to identify this code is not reflecting the correct ascending order for dates in this field.
I browsed through our forum and found some suggestions to use Cdate but even after using cdate I am not getting the right order. Can you guide me in getting the correct order.

Set rsemp = CurrentDb.OpenRecordset("select top 1 Ename, avlto, freeat  from ests order by freeat", dbOpenSnapshot)
             rsemp.MoveFirst
                     .Edit
                        !employee = rsemp!ename
                        ![avlto] = rsemp![avlto]
                        ![freeat] = DateAdd("n", Nz(rsTask![tasktime]) * 60, DMax("[freeat]", "ests", "[Ename]='" & rsemp!ename & "'"))
                        .Update

 

[theDBguy]

Hi. Since you're dealing with dates, I have to ask, what is your Regional Settings?

 

[Voyager]

Hi DBguy,
General date format is the one I am using

 

[theDBguy]

Hi. I meant, does your Regional Settings reflect U.S.A. (mm/dd/yyyy) or European (dd/mm/yyyy) date format?

 

[Voyager]

dd/mm/yyyy hh:nn AM/PM format European format.

 

[theDBguy]

Hi. Thanks for clarifying that. However, looking back at your original post, I am not sure where this information can affect the outcome. I don't you using a date value to find the max record. If you simply create a DMax() expression in the Immediate Window or create a Totals query, do you not get the correct result? For example, if your table has a record for [Ename]='test', do you not get the correct result by doing something like this?

?DMax("freeat","ests","Ename='test'")

 

[Voyager]

Hi,
The select query helps me to identify the correct “test” value or the correct Ename. So if the sort order is not correct I am not getting the correct value

 

[The_Doc_Man]

Seems to me you are missing syntax here.

Set rsemp = CurrentDb.OpenRecordset("select top 1 Ename, avlto, freeat  from ests order by freeat", dbOpenSnapshot)
             rsemp.MoveFirst
             [COLOR="Red"]With rsemp[/COLOR]
                     .Edit
                        !employee = rsemp!ename
                        ![avlto] = rsemp![avlto]
                        ![freeat] = DateAdd("n", Nz(rsTask![tasktime]) * 60, DMax("[freeat]", "ests", "[Ename]='" & rsemp!ename & "'"))
                        .Update
                    [COLOR="red"].End With[/COLOR]

However, beyond that: When you say you are getting the wrong answer, give us a scenario (it can be contrived if you wish) in which you give it some values and it gives you back a value (that you say is wrong.) Show us the inputs, the answer you get, and the answer you expected to get.

Maybe only one or two contrived examples would be enough to help us focus in on what goes wrong.

 

[Voyager]

Hi dbguy/ Docman,
This is what I am getting as result when I run the query “ests” and use * instead of top 1 parameter.
6/2/2019 4:00 PM
6/2/2019 6:10 AM
6/2/2019 7:00 PM
6/2/2019 7:45 AM
Look how access ignores (am and pm ) but sorts only the numbers. LOL.
But I want result as
6/2/2019 6:10 AM
6/2/2019 7:45 AM
6/2/2019 4:00 PM
6/2/2019 7:00 PM
I am using the corresponding table fields in correct general date format. Can you help ?

 

[The_Doc_Man]

At first glance it appears to me that you are sorting as a TEXT field would be sorted when what you really want is sorting as a DATE field would be sorted. The sample that would make a difference in deciding this would be a contrived example that contained a few times between 10 and 12 (AM or PM, doesn't matter). THAT would reveal exactly what is going on here.

 

[theDBguy]

Hi. Just to confirm, what is the data type of the field "freeat?"

 

[Voyager]

Hi,
Other than “ename” field all the others are date fields using general date format. Freeat is date field too.

 

[theDBguy]

Hi. Thanks. Without seeing it in action, it's a bit hard to help troubleshoot. For example, are you saying the result of the following query is not sorted correctly?

SELECT freeat FROM TableName ORDER BY freeat

 

[Voyager]

Yes. You are correct. I am just travelling back to home. Will reach and share the requested data.

 

[Deleted member 147267]

Assuming the example isn't a text field (sure sorts like one) it may be worth noting that it's generally accepted that while a table might appear to be in some sort of order, it's not (or at least not guaranteed to be). That's why it's better to use a sorted query as a domain rather than a table when order is important. Second, I've found that when opening a recordset on a table, the first record in that recordset often isn't what appears first in the table, which supports the notion that a table is like a bucket of marbles (records) with no guaranteed order. Again, presuming that the field is really a date field, then the SELECT statement shown ought to have an ORDER BY clause just to be sure that's not the issue. Regardless of whether or not it's the solution, I believe it should because sorting is at play.

 

[The_Doc_Man]

The recordset is presented as a text SQL string that includes an ORDER BY so the problem has to be something about the recordset itself.

As an experiment, can you define a (named & stored) query that is defined using the same SQL string you used for that recordset? You can go into SQL design view and copy/paste that string to do so. Then open it as a SELECT query and see what it returns. That is the starting point for troubleshooting. If it returns what you expected, see if you can then update the record through that query.

When you did that substitution of "*" for "TOP 1 " did you already have a stored query to do that or did you just cobble together some SQL?

And I have a question that suddenly slammed me: To what does the ![avlto] refer in this line:

![avlto] = rsemp![avlto]

By that I mean, is there a form in place and open with a control named ![Avlto]? Because if that is the case, then the WITH syntax gets more confused. You would still need that WITH because of the .Edit and .Update operations. But those other items in that sequence might have fooled me. Can you elucidate?

 

[Deleted member 147267]

The recordset is presented as a text SQL string that includes an ORDER BY so the problem

I should pay more attention when I have to scroll

 

[Voyager]

Hi docman, dbguy, micron,
My sincere apologies. Though I have cross checked the format of form field, query fields, It looks like I didn’t not change the format of specific field in the table. When I checked today the field was remaining as a text field which is the route cause of this result. Sorry sorry. I should have cross checked it well when docman insisted.
However thanks for all the support but I need some assistance in some other event I will post that in a new thread.

 

[The_Doc_Man]

Glad we could help and, as you should realize by now, we don't charge if you open up another thread.

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Cheer!