Combo Box Sort by Current Date

[nsquared]

Hi everyone

Is it possible to have the current or nearest date in a Combo Box list display when a form first opens. The list that the combo box calls up is a list of dated events from past and future but I would like it to jump to the nearest event to the current date without having to scroll through the whole list.

 

[vbaInet]

Is your combo box based on a SELECT statement or a query? If it's a SELECT statement then here:

dim rs as dao.recordset, latestDate as Date

set rs = currentdb.openrecordset("Select Q.[[COLOR=Red]DateField[/COLOR]] From " &  left(Me.[COLOR=Red]Combo[/COLOR].RowSource, len(Me.[COLOR=Red]Combo[/COLOR].RowSource) - 1) & " As Q Where  Q.[[COLOR=Red]DateField[/COLOR]] <= Date Order By Q.[[COLOR=Red]DateField[/COLOR]] DESC;", dbopensnapshot)

rs.movefirst

latestDate = rs![[COLOR=Red]DateField[/COLOR]]

set rs = nothing

' If the bound column is the date field then
Me.[COLOR=Red]Combo[/COLOR].Value = latestDate

' If it's not the bound field then loop through the column until you find latestDate
' Once found, set it to the value of the bound column.

I've highlighted the bits you need to change and this will go in the form's Load event.

 

[Dairy Farmer]

I have a simple method.

In the query for form I add a field (order1) that assigns a value of 1 if date1 = Date() or 2 if it doesn't. The field is sorted Assending.

Order1: IIf([Table1]![Date1])=Date(),1,2)

This makes 1 the first record.

Then in the form load procedure I add the following:

Private Sub Form_Load()
Dim A1 As Long
    A1 = Order1
    DoCmd.GoToRecord acDataForm, "Form1", acGoTo, A1
End Sub

 

[Galaxiom]

Dairy Farmer's solution bypasses the combo entirely.

It is designed to take you to the first record on the form that matches that date. However, taken as read, it also disorders the records by forcing the current date to the top. Not really ideal.

Also it does nothing if the current date is not in the list.

vbaInet's solution only finds the nearest day before or equal to the current date.
To include future dates use the absolute difference and order ascending:
Abs(DateDiff("d", Date(), [datefield]))

 

[Dairy Farmer]

Also it does nothing if the current date is not in the list.

Missed that. Sorry.

I should have explained a bit better.

I have a table that has Date and Sold.
The query for the table has Date, Sold, YYMM

The combo box query used the table
The query for the combo box is MMYY, YYMM
Sorted by YYMM decending

The combo box is in the header of the main form
The sub form shows records for the table, sorted by date
The form and subform are joined by YYMM

The combo box default to the currect MMYY which inturn default the subform to the same.

Having reread the OP I see I misread the way the data is presented in the form. My solution will only work if there are multiple instances of the same date or if you also use YYMM to link. In other words if you only want to display the current month's data.

 

[vbaInet]

Good point GalaxiomAtHome. Having read it again it seems the OP wants it to go to the current or upcoming event. If that's the case then there will be a slight adjustment to the code. But I will leave it for the OP to clarify.

 

[nsquared]

Thanks for all the help everyone, sorry for not coming back sooner been out the country. vbaInet to answer you question I need the the combobox to select the event nearest to the current date,Thanks again.

 

[vbaInet]

In that case, maybe something like this:

dim rs as dao.recordset, latestDate as Date

set rs = currentdb.openrecordset("Select TOP 1 Q.[[COLOR=Red]DateField[/COLOR]] From " & left(Me.[COLOR=Red]Combo[/COLOR].RowSource, len(Me.[COLOR=Red]Combo[/COLOR].RowSource) - 1) & " As Q Where Q.[[COLOR=Red]DateField[/COLOR]] >= Date() Order By Q.[[COLOR=Red]DateField[/COLOR]];", dbopensnapshot)

if rs.recordcount <> 0 then
    rs.movefirst

    latestDate = rs![[COLOR=Red]DateField[/COLOR]]
end if

set rs = nothing

' If the bound column is the date field then
Me.[COLOR=Red]Combo[/COLOR].Value = latestDate

' If it's not the bound field then loop through the column until you find latestDate
' Once found, set it to the value of the bound column.

The data type of the date field must be Date/Time.

 

[DCrake]

Can't you just do a

DefaultValue = DMax("DateField","TableOrQuery","DateField <=Date()")

Where TableOrQuery is the rowsource for the combo.

 

[vbaInet]

Can't you just do a

DefaultValue = DMax("DateField","TableOrQuery","DateField <=Date()")

Where TableOrQuery is the rowsource for the combo.

Good idea David. But I think it should be:

Me.Combo.Value = DMin("DateField","TableOrQuery","DateField >= Date()")

The OP wants it when the form loads.

 

[DCrake]

The method I used finds the nearest date to today in the past


Past
01/10/2010 DMin
01/11/2010
01/12/2010
01/01/2011 DMax

Present
04/01/2011 Today

Future
01/02/2011 DMin
01/03/2011
01/04/2011 DMax

If the user want's the nearest date be it in the past or the future they would need to use a nested iif() and a DateDiff() to find the nearest to today's date to see which one to offer the user as default. Personally I would only go for one option and stick to it.

 

[vbaInet]

Yea, that was what I initially thought the OP was after which prompted my first reply.

But the OP clarified in post #7 that it's this option that he's after:

Future
01/02/2011 DMin
OR
Today

... which the code in posts #8 or #10 does.

I don't trust the Default Value property for setting unbound fields to a record when the form loads.

 

[nsquared]

Thanks Everyone again for you help, David your method does what I needed, so thanks, vbaInet your method I think will work but I was having a problem, with the LEN statement in your code as it kept losing the last letter of the name of the Qry, anyway problem fixed, thank for all your help.