sorting record in form view

[GelC]

I have a form to browse all records
Record Source: qryAllRecords
Order By : year
I added a sorting field based on "month" (this is a text field)
It never give me an order of Month (January, February, March and so on ).
Hope someone can help me.

GelC

 

[PHV]

Don't you have a real DateTime field ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GelC]

The reasons I have it separately are
1. Because I don't know how make 'month' to be display as January, February, March, ...... on my forms
2. When I enter new record, I just can pick a month from list
3. I want to search records based on 'month'
I wished I would know other techniques to implement this task.
Thanks

 

[PJFry]

If it were me, here is what I would do:

First, change your months from text to a number, say, long integer. January will now be 1, etc. (You may wish to create a new field for the numeric date and run an update query to populate the new month, then just delete the text months.) Call your new numeric month lngMonth.

Next, create a table that only holds dates. Two columns, a numeric month and a text month. Make the numeric month an Autonum.

lngMonth | txtMonth (or some variation of that).

Call that table tDates

Now, in your form, create a combo box and tell it to look the values up in a table, tDates.

Select both fields in tDates.

Don't worry about the sort order. It applies to the new table, not your form.

Make sure you hide the key column.
The wizard will ask you where you want to store the values. Store them in the lngMonth in your original data table.

Give the combo box a name, and you are done. The user will see:
January
February and so on.

The table sees
1
2

Sort on those values.

Hopefully this not more complex than you want to get, but it should do the trick.

PJ