OrderBy Getting Populated with Value Not Ordering Form (1 Viewer)

[dgreen]

Here's what I'm seeing in design view. Why is it choosing a specific date vice the function MaxDate to do this sort?

 

[theDBguy]

Step 7 on your list, after everything was looking so good. The OrderBy got populated again with a date. So, what are you thinking now?

This is expected behavior when assigning values to form properties using code and then switching to design view and then saving the form when closing, The value assigned to the property by code gets permanently assigned at that point.

 

[dgreen]

So I'm causing this to myself because of how I'm interacting with the forms?

 

[zeroaccess]

Yes. You will need to remember to clear that before saving and closing the design view every time.

 

[theDBguy]

Here's what I'm seeing in design view. Why is it choosing a specific date vice the function MaxDate to do this sort?
View attachment 79837

Hi. I haven't looked into this part of the problem because I was concentrating on why the date was getting stuck in the form properly. If your intention was to have a calculated column showing the max date among three fields and then sort the form on that column, then you don't even need code to assign an OrderBy value, just enter the name of the calculated column in there or add an OrderBy clause in your query.

 

[dgreen]

It is my intention to get the formula (see below where I hard pasted it in) or field that represents it (MaxDate) into the OrderBy not a specific date value. So this is the current state of the form. I've removed the OnLoad vba. We'll see if this holds.

 

[theDBguy]

It is my intention to get the formula (see below where I hard pasted it in) or field that represents it (MaxDate) into the OrderBy not a specific date value. So this is the current state of the form. I've removed the OnLoad vba. We'll see if this holds.

View attachment 79838

Hi. I don't think that will work either. The OrderBy property takes a String value, the name of a field or fields, not a date value.

 

[June7]

I tested this. If I return a field name, it does not work. If I return a date, it does work. Function must be set up to return a date value, not a Variant.

 

[dgreen]

So change the original code from variant to date?

I tested this. If I return a field name, it does not work. If I return a date, it does work. Function must be set up to return a date value, not a Variant.

 

[theDBguy]

I tested this. If I return a field name, it does not work. If I return a date, it does work. Function must be set up to return a date value, not a Variant.

Hi. I would like to see this in a demo please. I am always willing to learn something new. Thanks!

 

[June7]

Your original function is set to return Date.

But why use VBA when it can be done without?

Nz() returns a string value so use IIf() version in OrderBy property, except use IsNull() instead of Is Null:

IIf(Not IsNull([End_Date]), [End_Date], IIf(Not IsNull([Start_Date]), [Start_Date], [Created_Date]))

Or calculate MaxDate field in RecordSource query (Is Null works in query) and reference field name in form design.

It's a simple test: only need 2 date fields in table, build function, call function.

 

[CJ_London]

@dgreen

seems my post #6 got missed but after a nights sleep it occurs to me you don't need any code at all.

Since these are individual records you can simply put the following into the order by property of the form and job done

[End_date] desc, [Start_date] desc, [Created] desc

null values will appear at the bottom

I note from the screenshots of the form you do not have a created field displayed. Provided you do have it in your recordsource it will still sort but the results would appear random
edit: ignore the last sentence, just noticed you have it at the bottom
edit2: rather than populating the order by property, you could just sort the query providing your date fields are selected/displayed in the right order

 

[zeroaccess]

@dgreen

seems my post #6 got missed but after a nights sleep it occurs to me you don't need any code at all.

Since these are individual records you can simply put the following into the order by property of the form and job done

[End_date] desc, [Start_date] desc, [Created] desc

null values will appear at the bottom

I note from the screenshots of the form you do not have a created field displayed. Provided you do have it in your recordsource it will still sort but the results would appear random
edit: ignore the last sentence, just noticed you have it at the bottom
edit2: rather than populating the order by property, you could just sort the query providing your date fields are selected/displayed in the right order

You had it right the first time - reports ignore sorting in the recordsource and you have to use Order By on the report.

 

[CJ_London]

reports ignore sorting in the recordsource and you have to use Order By on the report.

totally agree - but the screenshot provided by the OP is for a form, not a report

 

[zeroaccess]

totally agree - but the screenshot provided by the OP is for a form, not a report

Oh, maybe I am getting this mixed up with another thread. I swore a report was involved...