ID numbering

[DeanO]

I have a field in my Access table (ID Number) it is based partly on a date field and Autonumber field. The two fieldss are combined and the ID Number becomes something like this (9/23/07-001). In the table the ID Number is sorting in an ascending order and works fine. Problem is in the form. When I open the completed form to view the data numericly it is not in order. I have tried refresh and this will not work. I am now up to the 10th month and it is way out of order. Is there maybe a hard code I can use? Any help would be appreciated.

[nico5038]

Did you try to sort the form's recordsource by date and then by Autonumber ?

Nic;o)

[DeanO]

Did you try to sort the form's recordsource by date and then by Autonumber ?

Nic;o)

Yes I did in the table. No luck

[nico5038]

Hmm, the the date needs to be checked to be really of the datatype "date/time".
When it's seen as a string the order will be "strange".

For this you can try e.g. to use the function CDate() in the OrderBy clause.

Nic;o)

[missinglinq]

Am I correct in thinking you want this combined ID sorted by date then autonumber? If so:

Base your form on a query based on the table.

In the query, create a calculated field named CombinedID by typing this in the Field box:

CombinedID: Format([IDa],"yyyy/mm/dd") & "-" & Format([IDb],"000")

Now sort your query on this calculated field.

When you open the form, these IDs

10/23/07-001
09/11/08-002
09/09/06-003
10/10/06-004

will appear in this order

09/09/06-003
10/10/06-004
10/23/07-001
09/11/08-002

which I believe is what you're looking for.

Linq ;0)>