Assistance needed in getting the correct sort order (1 Viewer)

Voyager · Feb 5, 2019

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.

Code:

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 · Feb 5, 2019

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

Voyager · Feb 5, 2019

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

theDBguy · Feb 5, 2019

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

Voyager · Feb 5, 2019

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

theDBguy · Feb 5, 2019

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?

Code:

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

Voyager · Feb 5, 2019

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 · Feb 5, 2019

Seems to me you are missing syntax here.

Code:

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 · Feb 6, 2019

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 · Feb 6, 2019

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 · Feb 6, 2019

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

Voyager · Feb 6, 2019

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

theDBguy · Feb 6, 2019

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?

Code:

SELECT freeat FROM TableName ORDER BY freeat

Voyager · Feb 6, 2019

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

Micron · Feb 6, 2019

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 · Feb 6, 2019

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?

Micron · Feb 6, 2019

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 · Feb 7, 2019

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 · Feb 7, 2019

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

theDBguy · Feb 7, 2019

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

Assistance needed in getting the correct sort order (1 Viewer)

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

Immoderate Moderator

Registered User.

Immoderate Moderator

I’m here to help

Registered User.

I’m here to help

Registered User.

AWF VIP

Immoderate Moderator

AWF VIP

Registered User.

Immoderate Moderator

I’m here to help

Similar threads

Users who are viewing this thread