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

[dgreen]

I have a public function to help determine the maximum date from 3 different values for each record. I then want to sort the form OnLoad based on that Maximum Date.

For some reason, every couple of times I open the form, instead of sorting by date, the OrderBy gets populated with a date.

When this happens I get a parameter entry. It happens to be the start_date of record 1 in the table.

Thoughts on the below code? What am I missing?

Public Function MaxDate(createdDate As Variant, startDate As Variant, endDate As Variant) As Date
  If IsDate(endDate) Then
    MaxDate = endDate
  ElseIf IsDate(startDate) Then
    MaxDate = startDate
  ElseIf IsDate(createdDate) Then
    MaxDate = createdDate
  End If
End Function

Private Sub Form_Load()
    Me.OrderBy = MaxDate(Created, Start_Date, End_Date)
End Sub

Here's what the form looks like in design view with the date populated in the orderby, somehow. Which I don't what to have happen.

 

[dgreen]

I think I figured it out... I had the Public Function on the Form and not in it's own module? The query that the form was based on therefore couldn't see the code to generate the MaxDate. If this was true, I'm not sure why the program was populated a date in the field....

 

[theDBguy]

Hi. Did you happen to open the form normally and then change the view to Design View?

 

[dgreen]

Not that I remember. Would moving the public function from the form itself to it's own module make a difference in the process?

Did you happen to open the form normally and then change the view to Design View?

 

[June7]

What do you mean by "from the form itself to it's own module"? The code is already in the form's "own" module.

You should probably call this function in form's RecordSource query then reference that constructed field for the OrderBy property. However, since there are only 3 fields involved, this can probably be done without VBA. Why would a date field value not be a date - it is Null? Calculation in query:

MaxDate: Nz(End_Date, Nz(Start_Date, Created_Date))

Reference that field name in OrderBy property in form design or use ORDER BY clause in RecordSource query.

 

[CJ_London]

your maxdate function returns a date - would think it needs to return the name of the field as a string.

Public Function MaxDate(createdDate As Variant, startDate As Variant, endDate As Variant) As String

  If IsDate(endDate) Then

    MaxDate = "[end_Date]"

  ElseIf IsDate(startDate) Then

    MaxDate = "[start_Date]"

  ElseIf IsDate(createdDate) Then

    MaxDate = "[created]"

  End If

End Function

the function can be in your form and does not need to be public since it would appear to be specific to the form

or to follow June's suggestion

MaxDate: iif(end_Date is null, "[End_Date]", iif(start_Date is null,"[start_date]", "[created]"))

and you will need to ensure your query returns all these 3 fields to be able to order by on it

 

[theDBguy]

Not that I remember. Would moving the public function from the form itself to it's own module make a difference in the process?

It shouldn't, but try the following first.

1. Leave the form and function as before
2. Open the form in design view
3. Empty out the OrderBy property
4. Close the form and save it
5. Open it again in design view and verify OrderBy is still empty
6. Close the form and then open it in normal view
7. Switch to design view and check the OrderBy again

 

[dgreen]

What I mean is that the code was in the
Microsoft Access Class Objects
Form_f_Contact_Experience_Relationship
and it wasn't in
Modules
modMaxDate

Does it matter if the Public Function is in the MS Class Objects? Can the function be used from there to support other locations in the database?

"from the form itself to it's own module"? The code is already in the form's "own" module.

 

[June7]

dgreen, have you seen post 5?

 

[dgreen]

Done and the ORDERBY value remained empty. Then a couple of times later, the date value would populate the OrderBy.

What I've done since this posting, is move the Public Function MaxDate to a Module. Then I updated the query with a MaxDate column. I've opened the form a couple of times and the issue hasn't resurfaced, yet. Maybe its resolved?

1. Leave the form and function as before
2. Open the form in design view
3. Empty out the OrderBy property
4. Close the form and save it
5. Open it again in design view and verify OrderBy is still empty
6. Close the form and then open it in normal view
7. Switch to design view and check the OrderBy again

 

[dgreen]

There aren't any blank date values with this approach, since the created value is auto populated by the SharePoint List value. Good thought though.

dgreen, have you seen post 5?

 

[June7]

So, I am confused. Why are you using IsDate() function? Why do you have to test for a valid date? If there are no blank date fields then why would data not be a valid date? When would Start_Date or Created_Date get returned?

 

[dgreen]

Maybe that function isn't needed anymore.

The scenario is you have a partially filled out resume on a person... Maybe sometimes they provided a start date, maybe others just an end date and some records have no dates but you do know that the did a job.

I don't want to wait to populate the information into the database and don't want to use bs filler dates. I figure, I'd be logically starting at the top of the document and working from most current jobs to older/later ones. So in the absence of a date, I figured I could sort on the date the row was created in the database. But I don't want to use that date if there is one populated in either the start or end date.

 

[June7]

Okay, so fields will be null. Use either Nz() or IIf() as already demonstrated. Except I think CJ's logic is a little off.

MaxDate: IIf([End_Date] Not Is Null, [End_Date], IIf([Start_Date] Not Is Null, [Start_Date], [Created_Date]))

If you use quote marks as suggested by CJ, then field name will return.

 

[dgreen]

Thanks for the recommendation but I am looking for the date, so I can sort on it. I'll hold onto this, since it does tell me which date is being shown.

your maxdate function returns a date - would think it needs to return the name of the field as a string.

 

[June7]

The expression examples I provided will return date value, not text. Look at them again. There are no quote marks in my examples.

 

[dgreen]

When I copied and pasted from the code posted in message #6 I got the above result. It's all good. I'm fine on this one for now. Thanks.

There are no quote marks in my examples.

 

[June7]

You have both date and field name returned, great.

 

[dgreen]

Sorry, I got confused on who I was responding to.

You have both date and field name returned, great.

 

[dgreen]

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?

It shouldn't, but try the following first.

1. Leave the form and function as before
2. Open the form in design view
3. Empty out the OrderBy property
4. Close the form and save it
5. Open it again in design view and verify OrderBy is still empty
6. Close the form and then open it in normal view
7. Switch to design view and check the OrderBy again