CURRENCY QUERIES 1

[egrant]

Hi,

I need to run a query that searches a Dollar Value disregarding any cents it may have. For eg 25.00 returns as well as 25.50, 25.25, 25.01 etc

I designed a simple query that prompts for a Dollar amount, without the cents. I use LIKE to find the dollar amount. So I tried a few different queries...

23 is just a sample number btw

Select * from table
where DollarAmount Like "23" & "*"
No work - that would give me amounts like 230, 2300, not just 23

so I tried this
select * from table
where DollarAmount Like "23" & "." & "*"
That only gave me 23.50 not 23.00

Now I tried to make the decimal places 0. That rounded my 23.50 off to 24.00 so that ain't gonna work either.

Anyone got any ideas???

Thanks,

EMG

 

[Steve101]

Try something like:

Select * from table
where int(DollarAmount) = 23

This uses the Int function to truncate the dollar amount to an integer, after which it will comparely successfully to the value to the right of the equal sign.

You could also use:

Select * from table
where DollarAmount Between 23 and 23.99

This would give the same result, but is slightly longer.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[egrant]

Thanks, the second query did the trick

Incase this helps anyone this is the query I used

Select * from table
Where DollarAmnt
Between [Forms]![SEARCH FORM]![INVOICE$$] And [Forms]![SEARCH FORM]![INVOICE$$] & ".99"