How do I get a derrived point to have 2 decimals and rounded?

[kcchiefsgirl33]

How can I get a derrived field to have 2 decimal points in Access 2007 using
SQL.

ROUND((price * .90),2) AS [New Price]

Is returning a price rounded but if the amount is 21.20 it is only showing
21.2 .. If it anything other than zero then it works..

Also... how to return all characters in a field except for the last 2. Like
a name field.. I want all but the last 2 characters of each name. Any ideas!!

 

[Allen Browne]

Try:
CCur(Nz(Round([price] * 0.9,2),0))

The CCur() converts the data type to Currency.
That fails if the value is Null, so Nz() prevents that problem.

Use Left() to return the left part of a field, and Len() to determine the
length. Substitute your field name for F1:
IIf(Len([f1] > 2, Left([f1], Len([f1]) - 2, [f1])

 

[kcchiefsgirl33]

Thank you. Is there a way to do this 1st one without the $ showing. They
don't want a $. The 2nd one keeps telling me... Syntax Error (missing
operator) in query expression If(Len([city] > 2, Left([city], Len([f1]) - 2,
[city]) AS new_city ROM tblWherePracticel

HELP please!!!
Thanks!!

Try:
CCur(Nz(Round([price] * 0.9,2),0))

The CCur() converts the data type to Currency.
That fails if the value is Null, so Nz() prevents that problem.

Use Left() to return the left part of a field, and Len() to determine the
length. Substitute your field name for F1:
IIf(Len([f1] > 2, Left([f1], Len([f1]) - 2, [f1])

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

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

How can I get a derrived field to have 2 decimal points in Access 2007
using
SQL.

ROUND((price * .90),2) AS [New Price]

Is returning a price rounded but if the amount is 21.20 it is only showing
21.2 .. If it anything other than zero then it works..

Also... how to return all characters in a field except for the last 2.
Like
a name field.. I want all but the last 2 characters of each name. Any
ideas!!

 

[JP]

It looks like some ")" are missing.
Try this:

IIf(Len([city]) > 2, Left([city], Len([city]) - 2), [city])

Thank you. Is there a way to do this 1st one without the $ showing. They
don't want a $. The 2nd one keeps telling me... Syntax Error (missing
operator) in query expression If(Len([city] > 2, Left([city], Len([f1]) - 2,
[city]) AS new_city ROM tblWherePracticel

HELP please!!!
Thanks!!

Try:
CCur(Nz(Round([price] * 0.9,2),0))

The CCur() converts the data type to Currency.
That fails if the value is Null, so Nz() prevents that problem.

Use Left() to return the left part of a field, and Len() to determine the
length. Substitute your field name for F1:
IIf(Len([f1] > 2, Left([f1], Len([f1]) - 2, [f1])

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

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

How can I get a derrived field to have 2 decimal points in Access 2007
using
SQL.

ROUND((price * .90),2) AS [New Price]

Is returning a price rounded but if the amount is 21.20 it is only showing
21.2 .. If it anything other than zero then it works..

Also... how to return all characters in a field except for the last 2.
Like
a name field.. I want all but the last 2 characters of each name. Any
ideas!!

 

[Allen Browne]

If you really care about how it's displayed, you need a form.
Use datasheet view if you want it to look like a query.

In a form, you can set the properties of the text box:
Format Fixed
Decimal places 2

The other expression needs another bracket:
If(Len([f1]) > 2, Left([f1], Len([f1]) - 2, [f1])

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

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

Thank you. Is there a way to do this 1st one without the $ showing. They
don't want a $. The 2nd one keeps telling me... Syntax Error (missing
operator) in query expression If(Len([city] > 2, Left([city], Len([f1]) -
2,
[city]) AS new_city ROM tblWherePracticel

HELP please!!!
Thanks!!

Try:
CCur(Nz(Round([price] * 0.9,2),0))

The CCur() converts the data type to Currency.
That fails if the value is Null, so Nz() prevents that problem.

Use Left() to return the left part of a field, and Len() to determine the
length. Substitute your field name for F1:
IIf(Len([f1] > 2, Left([f1], Len([f1]) - 2, [f1])

message

How can I get a derrived field to have 2 decimal points in Access 2007
using
SQL.

ROUND((price * .90),2) AS [New Price]

Is returning a price rounded but if the amount is 21.20 it is only
showing
21.2 .. If it anything other than zero then it works..

Also... how to return all characters in a field except for the last 2.
Like
a name field.. I want all but the last 2 characters of each name. Any
ideas!!

 

[kcchiefsgirl33]

Thanks everyone!! This helps so much!! Fixed my problem.

Traci

If you really care about how it's displayed, you need a form.
Use datasheet view if you want it to look like a query.

In a form, you can set the properties of the text box:
Format Fixed
Decimal places 2

The other expression needs another bracket:
If(Len([f1]) > 2, Left([f1], Len([f1]) - 2, [f1])

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

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

Thank you. Is there a way to do this 1st one without the $ showing. They
don't want a $. The 2nd one keeps telling me... Syntax Error (missing
operator) in query expression If(Len([city] > 2, Left([city], Len([f1]) -
2,
[city]) AS new_city ROM tblWherePracticel

HELP please!!!
Thanks!!

Try:
CCur(Nz(Round([price] * 0.9,2),0))

The CCur() converts the data type to Currency.
That fails if the value is Null, so Nz() prevents that problem.

Use Left() to return the left part of a field, and Len() to determine the
length. Substitute your field name for F1:
IIf(Len([f1] > 2, Left([f1], Len([f1]) - 2, [f1])

message
How can I get a derrived field to have 2 decimal points in Access 2007
using
SQL.

ROUND((price * .90),2) AS [New Price]

Is returning a price rounded but if the amount is 21.20 it is only
showing
21.2 .. If it anything other than zero then it works..

Also... how to return all characters in a field except for the last 2.
Like
a name field.. I want all but the last 2 characters of each name. Any
ideas!!