Order By - Using abs formula in 2003

[John Childs]

Under Access97, I have a reconciliation application that
has a form that sits over a table - the form is only
showing those items that have not been reconciled (filters
where field "reconciled" = false


Relevant fields in table are:

ID (Used to identify transaction amounts)
TranAmount
Reconiled (Yes/No check box)

The form uses an order by property of:
abs(Summary.TranAmount)

This order by is used to bring "like" amounts together -
the reconciliation clerk is able to easily see if for an
ID value, the amounts are equal and opposite

e.g Data

ABC -6.55
ABC 7.55
ABD 555
ABE -100
ABC -7.55


Using the order by on the form

ABC -6.55
ABC 7.55
ABC -7.55
ABE -100
ABD 555

Reconciliation clerk would see that the 7.55 and -7.55
match off with one another and would therefore set the
reconciled check box for both records to Yes

For some reason, Access 2003 does not like using abs
(Summary.TranAmount) in the order by property of the form -
it seems to not recognise the orderby on the tranAmount
field and will orderby the ID field

Any ideas on how to get around this????

 

[Allen Browne]

John, did you set the form's OrderByOn property? You could do that in the
Open event of the form:

Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "TranAmount"
Me.OrderByOn = True
End Sub

If that does not work, you could use a query as the RecordSource of the
form, and specify the sorting in the query.

If that still does not work, tell us more about TranAmount. Is it a
calculated field in a query?
http://members.iinet.net.au/~allenbrowne/ser-45.html
Or - if it is a field in a table, is it a Number of size Decimal?
http://members.iinet.net.au/~allenbrowne/bug-08.html

 

[Guest]

Hi Allen

I have tried setting the forms orderbyon property by both
using a macro and the setvalue and by using code -
unfortunately none of that seems to work - I have tried
your code code just in case on a test database - all that
does is sort by the TranAMount and not the absolute value
of the TranAmount

I have also tried changing the Orderby = "TranAmount" to
orderby = abs([TranAMount])

When opening the form Access asks for a parameter on the
100 value - seems to be looking at the negative values and
doesn't know how to handle them

FYI The TranAmount is simply a value field from the table -
it is not calculated and it is a number of size double

Using a query as a recordsource that brings back the
TranAmount and abs(TranAmount) and sorting on this
calculated field does bring back the correct results in
the correct order. i.e. order by on the abs(TranAmount)

However - the Yes/No text field is no longer a Yes/No
check box - the user has to key in YES to state that this
transaction is reconciled - a bit laborious when one has a
long list - far easier to check on a Yes/No box

So in summary I am still at a loss as to what to do
especially as this all works beautifully in Access97

Regards
John

 

[Guest]

Allen

made a msitake when using the query

In the new form the "Reconciled" object was a text box and
not a check box - this now works fine when using the
recordsource as a query

Still doesnt explain why is doesnt work when using a form
with the recordsource as a table!!!!

At least I can now convet and get rolling

Regards

 

[Allen Browne]

Yes, the code should have read:
Me.OrderBy = "Abs([TranAmount])"
and the quotes are important.

If the query works correctly, open the form in design view, and set its
RecordSource property to the name of the query. That should solve all the
problems, and the code is not needed. The check box in the form will still
work.

If you are still stuck and want further help, we need to know the Data Type
and Size of the TranAmount field. Is it a Text type field? A Currency type
field? A Number type field? If a Number, what does the Size say in the lower
pane? e.g. Integer, Long, Double, Decimal?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

I have tried setting the forms orderbyon property by both
using a macro and the setvalue and by using code -
unfortunately none of that seems to work - I have tried
your code code just in case on a test database - all that
does is sort by the TranAMount and not the absolute value
of the TranAmount

I have also tried changing the Orderby = "TranAmount" to
orderby = abs([TranAMount])

When opening the form Access asks for a parameter on the
100 value - seems to be looking at the negative values and
doesn't know how to handle them

FYI The TranAmount is simply a value field from the table -
it is not calculated and it is a number of size double

Using a query as a recordsource that brings back the
TranAmount and abs(TranAmount) and sorting on this
calculated field does bring back the correct results in
the correct order. i.e. order by on the abs(TranAmount)

However - the Yes/No text field is no longer a Yes/No
check box - the user has to key in YES to state that this
transaction is reconciled - a bit laborious when one has a
long list - far easier to check on a Yes/No box

So in summary I am still at a loss as to what to do
especially as this all works beautifully in Access97

Regards
John

-----Original Message-----
John, did you set the form's OrderByOn property? You could do that in the
Open event of the form:

Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "TranAmount"
Me.OrderByOn = True
End Sub

If that does not work, you could use a query as the RecordSource of the
form, and specify the sorting in the query.

If that still does not work, tell us more about TranAmount. Is it a
calculated field in a query?
http://members.iinet.net.au/~allenbrowne/ser-45.html
Or - if it is a field in a table, is it a Number of size Decimal?
http://members.iinet.net.au/~allenbrowne/bug-08.html

 

[Guest]

Allen

many thanks for your help

I will use the query as this works

I have also tried using the code when using the form with
recorsurce being the table

Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "Abs([TranAmount])"
Me.OrderByOn = True
End Sub

but it doesn't appear to work

FYI - The datatype of TranAMount field is a number with
the field size set to double

Regards
John

-----Original Message-----
Yes, the code should have read:
Me.OrderBy = "Abs([TranAmount])"
and the quotes are important.

If the query works correctly, open the form in design view, and set its
RecordSource property to the name of the query. That should solve all the
problems, and the code is not needed. The check box in the form will still
work.

If you are still stuck and want further help, we need to know the Data Type
and Size of the TranAmount field. Is it a Text type field? A Currency type
field? A Number type field? If a Number, what does the Size say in the lower
pane? e.g. Integer, Long, Double, Decimal?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

I have tried setting the forms orderbyon property by both
using a macro and the setvalue and by using code -
unfortunately none of that seems to work - I have tried
your code code just in case on a test database - all that
does is sort by the TranAMount and not the absolute value
of the TranAmount

I have also tried changing the Orderby = "TranAmount" to
orderby = abs([TranAMount])

When opening the form Access asks for a parameter on the
100 value - seems to be looking at the negative values and
doesn't know how to handle them

FYI The TranAmount is simply a value field from the table -
it is not calculated and it is a number of size double

Using a query as a recordsource that brings back the
TranAmount and abs(TranAmount) and sorting on this
calculated field does bring back the correct results in
the correct order. i.e. order by on the abs(TranAmount)

However - the Yes/No text field is no longer a Yes/No
check box - the user has to key in YES to state that this
transaction is reconciled - a bit laborious when one has a
long list - far easier to check on a Yes/No box

So in summary I am still at a loss as to what to do
especially as this all works beautifully in Access97

Regards
John

-----Original Message-----
John, did you set the form's OrderByOn property? You could do that in the
Open event of the form:

Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "TranAmount"
Me.OrderByOn = True
End Sub

If that does not work, you could use a query as the RecordSource of the
form, and specify the sorting in the query.

If that still does not work, tell us more about TranAmount. Is it a
calculated field in a query?
http://members.iinet.net.au/~allenbrowne/ser-45.html
Or - if it is a field in a table, is it a Number of size Decimal?
http://members.iinet.net.au/~allenbrowne/bug-08.html


"John Childs" <[email protected]>

wrote
in message

Under Access97, I have a reconciliation application that
has a form that sits over a table - the form is only
showing those items that have not been reconciled (filters
where field "reconciled" = false


Relevant fields in table are:

ID (Used to identify transaction amounts)
TranAmount
Reconiled (Yes/No check box)

The form uses an order by property of:
abs(Summary.TranAmount)

This order by is used to bring "like" amounts together -
the reconciliation clerk is able to easily see if for an
ID value, the amounts are equal and opposite

e.g Data

ABC -6.55
ABC 7.55
ABD 555
ABE -100
ABC -7.55


Using the order by on the form

ABC -6.55
ABC 7.55
ABC -7.55
ABE -100
ABD 555

Reconciliation clerk would see that the 7.55 and -7.55
match off with one another and would therefore set the
reconciled check box for both records to Yes

For some reason, Access 2003 does not like using abs
(Summary.TranAmount) in the order by property of the form -
it seems to not recognise the orderby on the tranAmount
field and will orderby the ID field

Any ideas on how to get around this????

.