Sort Desc (Access2002)

Topic

New Reply

Hi!
I have so much bad data in my Excel file so when it is gets to access table (if ever, I still can not import) I will need to sort it so empty records will be always at the buttom of the list.
So I am updating these cells with “zUndetermined”

Do you know better way to populate empty cell that will get it to the end of the recordset?

Thanks

Reply | Quote

Replies

Are you talking about blank values in a single field, or completely blank records?

Reply | Quote

HansV,
How do you determine that? I take data from Excel file, cells are blank and I am importing it into Access table and it has no value…I don’t know. I am running query and populating it with zUndetermined. What can I use that is more suitable and keep it down on the list?

Reply | Quote

You don;t need to update values, you can subtitute “zUndetermined” or whatever in a query without modifying the underlying data: create a calculated column

FieldNoBlanks: IIf(([FieldWithBlanks] & "")="","zUndetermined",[FieldWithBlanks])

where FieldWithBlanks is the name of a field. But I don’t really understand. Records in an Access table don’t have an intrinsic sort order – if you sort the records on another field, the zUndetermined values will end up all over the place.

Reply | Quote

When I am running Report I sort by that field and all zUndetermined get to a right place at the botom.
What you suggesting will give me “FieldWithBlanks” instead of “zUndetermined ” ??? If so how will I get it to the bottom??

Reply | Quote

No, it would give you “zUndetermined”. “FieldWithBlanks” is the name of the field in the table – you should change it to the actual name of your field.

Reply | Quote

No, it would give you “zUndetermined”. “FieldWithBlanks” is the name of the field in the table – you should change it to the actual name of your field.

Reply | Quote

When I am running Report I sort by that field and all zUndetermined get to a right place at the botom.
What you suggesting will give me “FieldWithBlanks” instead of “zUndetermined ” ??? If so how will I get it to the bottom??

Reply | Quote

You don;t need to update values, you can subtitute “zUndetermined” or whatever in a query without modifying the underlying data: create a calculated column

FieldNoBlanks: IIf(([FieldWithBlanks] & "")="","zUndetermined",[FieldWithBlanks])

where FieldWithBlanks is the name of a field. But I don’t really understand. Records in an Access table don’t have an intrinsic sort order – if you sort the records on another field, the zUndetermined values will end up all over the place.

Reply | Quote

HansV,
How do you determine that? I take data from Excel file, cells are blank and I am importing it into Access table and it has no value…I don’t know. I am running query and populating it with zUndetermined. What can I use that is more suitable and keep it down on the list?

Reply | Quote

Are you talking about blank values in a single field, or completely blank records?

Reply | Quote