Microsoft Access help again please

[AndyM]

I know the sign says Tractor By Net, but my previous Access questions have been answered here in record time, so here goes again...

I'm trying to build an expression with an IF statement and a ROUNDUP, but there doesn't seem to be a function in Access for ROUNDUP.

Here is the formula I would use if I were in Excel, with "C3" being the third column and "F3" the sixth column...

=IF(F3<0,0,((ROUNDUP(F3/C3,0))*C3))


I started building an expression with ROUNDUP in Access, only to discover that I couldn't find the function...
=(insert correct function here([Col6]/[Col3]))*[Col3]

If anyone can help me out, I will be really grateful.
If anyone can build the entire expression with the IF statement AND the ROUNDUP, I will be REALLY REALLY grateful!

Thanks! /forums/images/graemlins/laugh.gif

 

[khd]

Roundup is there but you might need to install a file. Read the help entry below. In actual help it has a link to installing the .dll

Regards,
Kevin

EDIT: Nevermind, never realized that access help also shows excel help. But one note is, in Access IF uses IIF. Haven't used access in so long hard to remember. I have Access 2000 and I think that is the version where it wen't from Access Basic to Visual Basic. You will probably have to write a custom function to get what you want unless the function wizard has the functions you are looking for. The easiest way, least accurate, would be to evaluate your if/then and if it evalutaes to to "then" then truncate right of the decimal and add to make your roundup.

Probablly taling out of my *** here as I said been way to long since I did any Access. Convert your DB to SQL Server and I'll write you and SQL statement to do what you need.

If I get a brainstorm...unlikely.....I'll update.

Kevin

 

[KAB]

Here is an answer from another forum. You will have to look at page 2. Access help

Kurt

 

[khd]

If going by KAB's help it would look something like this:

=IIF([Col6]<0,0,(int([Col6]/[Col3]) + .5)*[Col3])

Kevin

 

[AndyM]

<font color="blue"> If going by KAB's help it would look something like this:

=IIF([Col6]<0,0,(int([Col6]/[Col3]) + .5)*[Col3])

</font>

It's close, but not quite what I'm looking for it to do. Like I say before, I dumped my report into Excel and it works perfectly, but I eventually want to set up a macro that automatically emails the report, among other things, so I need to keep it in Access.

By the way, I'm using Access 97 at work. Since so many of our databases were created before Access 2000, we just continue to use the old version... which means I can't work on them at home. /forums/images/graemlins/frown.gif

Thanks for the help... I'll mess around with what you gave me and I'll update you if I can get it to work for me.

 

[AndyM]

I changed it to
=IIf([Col6]<0,0,(Int([Col6]/[Col3])+1)*[Col3])

and that makes it do exactly what I'm trying to do. Thanks guys for pointing me in the right direction. /forums/images/graemlins/cool.gif

It would be a shame if Access and Excel functioned the same way! /forums/images/graemlins/mad.gif