Sort by second half of field value

[Wannabeegeek]

Hi,
I have created a continuous form where one of the fields contains a user entered vaule in the format '0103','0203','0303','0104','0204','0304',etc. The first two digits are a serial number and the last two are the year in which that serial number was allocated. When the form displays it is sorted by this value.

I would like it to sort by the year segment first and then by the serial number segment as opposed to the current 0103,0104,0203,0204,etc.

I'm sure that there is a simple solution to this but as yet it escapes me. Any suggestions would be appreciated.

 

[67peshawar294]

Wanna,
in a blank entry on the query grid "field" ;
Right([yourfield],2) & Left([yourfield],2)
then use it to sort.
jim

 

[TheAceMan1]

How are ya Wannabeegeek . . . . .

If the [blue]RecordSource[/blue] of the form is not already based on a [blue]Query[/blue], make it so.

In the query your going to add two custom fields for your new sort. In trhe 1st empty field on the [blue]field row[/blue], copy/paste the fillowing:

[blue]srtYr: Year("1/1/" & Right([qCode],2))[/blue]

Set sorting Ascending/Decending.

In the next empty field copy/paste the following:

[blue]srtSer: Val(Left([qCode],2))[/blue]

Set sorting Ascending/Decending.

If you'd rather use [blue]SQL[/blue] as the [blue]RecordSource[/blue] for the form, you can copy it in SQL view, then paste it in the RecordSource of the form.

See Ya! . . . . . .

 

[TheAceMan1]

How are ya xaf294 . . . .

And if he has years that [blue]crossover 2000[/blue] or 0900?

See Ya! . . . . . .