Can't get Order by Desc to work 1

[0212]

Hi, all! I hope one of you can help me. I am trying to sort by a field [Budgetot]from a Dialog Box VBA to a form. It will sort in ascending order. However, I have tried all kinds of ways and I can't get it to sort in Descending Order. I thought that I just needed to add the string "DESC" after the sort field. But, "DESC" doesn't get transferred to the Form's OrderBy Property - only the sort field [Budgetot]. Any ideas? Thanks!

DoCmd.OpenForm " FRM_3_8_2007_Budget_Form ", acNormal, acEdit, Cri

'Sort by Budget Total or Budget Remaining'
With Forms!FRM_3_8_2007_Budget_Form

.OrderBy = strOrder
.OrderByOn = True

 

[Eupher]

0212,

You haven't shown us any examples of what you've tried that doesn't work, so it's hard to offer any corrections. However, the following *should* work:

.OrderBy = strOrder[highlight] & " DESC"[/highlight]

HTH,

Ken S.

 

[0212]

Thank you, Ken! It worked!!!. Apparently, when I tried a similar statement: .OrderBy = strOrder & "DESC", it didn't work. Can you explain the necessity for a space after the first quote? When do I need to do that? What is the purpose? Also I tried to set strOrder to [BudgTot] by using the following statement:

strOrder = strOrder & " DESC", but it didn't work. I assume that you have to indicate it only on the assigment of .OrderBy. Thanks again!

Jim.

 

[Eupher]

Hi, Jim,

The space is necessary so you end up with the proper string. If, for example, the value of strOrder is "Field1", concatenating without the space would yield "Field1DESC" - but with the space, it's "Field1 DESC" - a valid value for the OrderBy property.

WHERE you do the concatenation should not matter. Assuming the value of strOrder is, for example, "Field1" again, the following two examples should both concatenate to "Field1 DESC" and would therefore be equivalent:

.OrderBy = strOrder & " DESC"

strOrder = strOrder & " DESC"
.OrderBy = strOrder

\
HTH,

Ken S.

 

[PHV]

In fact, you wanted this ?
strOrder = "BudgTot DESC"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[0212]

Thank you, Ken, for the explanation! Even though it is simple, it helped alot!

Jim.

 

[0212]

Thanks PHV, but I thought I tried it, but I wasn't able to get it to work. BudgeTot is a field in a form/table so brackets are needed around the field name, correct? If not, please let me know. I certainly could have done something wrong! Thanks

Jim.

 

[Eupher]

Jim,

Brackets are not needed unless the field name contains special characters (& # , . / \ etc.) or a space. Not a bad habit to use brackets, though.

Ken S.

 

[0212]

Ken, so, I could have used PHV's method above - strOrder = "BudgTot DESC" - if I didn't use brackets. If I do use brackets for the field name, I need to used your method -strOrder = strOrder & " DESC" - above that. I Appreciate all of your help!

Jim.

 

[Eupher]

Jim,

Brackets are not needed in either case.

Ken S.