Write a query to select records with cents

[mohebk]

Hi,
I am trying to put a criteria in the where clause to select records if the entry has a value in the cents digits (to the right of the decimal point). I can remember a way to do that. Please help.

Thanks

Mo

 

[PWise]

Select *
from TableName
where amount-int(amount)>0

 

[PHV]

<>Int(yourField)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mohebk]

I think this is not what I am looking for. My be I did not explain my self well.

The date looks like this

$10.01
$11.00
$12.34
$13.00

I am only looking to get the vlaues where the cents are > 0. In this case will be $10.01 and $12.34.

Thanks

Mo

 

[PHV]

Not Like '*.00'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mohebk]

Not quite. it is still returning all the records. I am thinking that there is a method like (right,2) = 0 but I know that only works with the left method and no right. My be even using the string method.

Mo

 

[PHV]

What is the actual SQL code ?
What is the name and the data type of the field ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mohebk]

This is the actual sql code:

SELECT NewIncrease.CPT, NewIncrease.DESCRIPTION, FEE_SCHED21_FSC583.AMT AS AMT_AFTER
FROM NewIncrease LEFT JOIN FEE_SCHED21_FSC583 ON NewIncrease.CPT = FEE_SCHED21_FSC583.CODE
WHERE (((FEE_SCHED21_FSC583.AMT) Not Like '*.00'));


Thanks again.

Mo

 

[lespaul]

what is the data type of AMT?

Leslie

Have you met Hardy Heron?

 

[mohebk]

currency.

I did actually find a way. It is not the best but it worked. I have used InStr(AMT,".") > 0. It worked fine.

But if you know if a more accurate way, that will be a lot of help for me and forum readers.

Thanks for your help.

Mo

 

[PHV]

First, due the criteria, use an INNER join instead of LEFT.
FYI, I've suggestested the following:
WHERE Int(FEE_SCHED21_FSC583.AMT)<>FEE_SCHED21_FSC583.AMT

This is really not working ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886