Problem sorting and filtering on a calculated field

[erick-flores]

Hello all

The form is based on a query that calculates the field with the
problem. This is the field calculated in the query:
Current: Format(IIf(DateDiff("d",[inv_date],Date())<=30,
[total],"0"),"Standard")

The form is in datasheet view. When I tried to sort or filter (ex.

5000) for this calculated field it gaves me wrong data. Here is an

example of how my data is showing when I apply a sorting from Z-A:
Whats happening: The right way (or what I want):
996.57 9,427.34
995.84 9,193.88
952.90 8,987.89
9,427.34 8,789.89
9,193.88 7,893.34
877.64 996.57
856.09 995.84
8,987.89 952.90
8,789.89 877.64
764.90 856.09
7,893.34 764.90

I dont know whats happening, any ideas???


Thanks in advance

 

[kingston via AccessMonster.com]

Instead of using the function Format(), try CDbl(). Also, use 0 instead of
"0" in the IIF() statement to specify the value zero instead of the string
"0"; with CDbl(), this doesn't matter anymore.

Hello all

The form is based on a query that calculates the field with the
problem. This is the field calculated in the query:
Current: Format(IIf(DateDiff("d",[inv_date],Date())<=30,
[total],"0"),"Standard")

The form is in datasheet view. When I tried to sort or filter (ex.

5000) for this calculated field it gaves me wrong data. Here is an

example of how my data is showing when I apply a sorting from Z-A:
Whats happening: The right way (or what I want):
996.57 9,427.34
995.84 9,193.88
952.90 8,987.89
9,427.34 8,789.89
9,193.88 7,893.34
877.64 996.57
856.09 995.84
8,987.89 952.90
8,789.89 877.64
764.90 856.09
7,893.34 764.90

I dont know whats happening, any ideas???

Thanks in advance

 

['69 Camaro]

Hi, Erick.

I dont know whats happening

It's sorting on the characters in the text string, not the digits you
expect. Before sorting on this column, use the Val( ) function on the
string first. For example:

Val(Format(IIf(DateDiff("d",[inv_date],Date())<=30,>
[total],"0"),"Standard"))

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Hello all

The form is based on a query that calculates the field with the
problem. This is the field calculated in the query:
Current: Format(IIf(DateDiff("d",[inv_date],Date())<=30,
[total],"0"),"Standard")

The form is in datasheet view. When I tried to sort or filter (ex.

5000) for this calculated field it gaves me wrong data. Here is an

example of how my data is showing when I apply a sorting from Z-A:
Whats happening: The right way (or what I want):
996.57 9,427.34
995.84 9,193.88
952.90 8,987.89
9,427.34 8,789.89
9,193.88 7,893.34
877.64 996.57
856.09 995.84
8,987.89 952.90
8,789.89 877.64
764.90 856.09
7,893.34 764.90

I dont know whats happening, any ideas???


Thanks in advance

 

[John W. Vinson]

Hello all

The form is based on a query that calculates the field with the
problem. This is the field calculated in the query:
Current: Format(IIf(DateDiff("d",[inv_date],Date())<=30,
[total],"0"),"Standard")

The form is in datasheet view. When I tried to sort or filter (ex.

5000) for this calculated field it gaves me wrong data. Here is an

example of how my data is showing when I apply a sorting from Z-A:
Whats happening: The right way (or what I want):
996.57 9,427.34
995.84 9,193.88
952.90 8,987.89
9,427.34 8,789.89
9,193.88 7,893.34
877.64 996.57
856.09 995.84
8,987.89 952.90
8,789.89 877.64
764.90 856.09
7,893.34 764.90

I dont know whats happening, any ideas???

The Format() function does not return a number; it returns a text
string. A text string beginning with "99" sorts higher than a text
string beginning with "76".

Just leave off the format, and change the "0" to 0. You can set the
Format property of the field to Standard (or whatever format you wish)
in query design view or - better - set the format property of the Form
or Report textbox in which you're displaying this field.

John W. Vinson [MVP]

 

[erick-flores]

Thanks for your answer. This is what I did...and its finally working:

Current: CDbl(Format(IIf(DateDiff("d",[inv_date],Date())<=30,[total],
0),"Standard"))