Formula Problem (Access 2000)

Topic

New Reply

I have converted a database from A97 to A2k without any error messages.

BUT.. a formula in my query keeps telling me that i’m missing one of the following
[,)

This message does not appear in ny A97 version.

Amazingly it still comes back with the correct data. I’ve put the formula below to see if anyone can tell me where I’m going blind. I’ve looked at this soooooooooooooooooo many times.

ptd: Sum((((IIf(IsNull([claim_os_movements]![clo_paid_to_date_fees]),0,[claim_os_movements]![clo_paid_to_date_fees])+IIf(IsNull([claim_os_movements]![clo_paid_to_date_indem]),0,[claim_os_movements]![clo_paid_to_date_indem]))*[claim_os_movements]![clo_os_line_pcnt]/100)/[claim_os_movements]![clo_sett_exchange_rate]))

Reply | Quote

Replies

you could simplify your formula by using Nz like this:

Sum(((Nz([claim_os_movements]![clo_paid_to_date_fees],0)+Nz([claim_os_movements]![clo_paid_to_date_indem],0))*[claim_os_movements]![clo_os_line_pcnt]/100)/[claim_os_movements]![clo_sett_exchange_rate])

I *think* I got the parens right, but you’ll have to double-check. The Nz function is certainly easier to read than all those “IIF(IsNull(whatever” expressions.

Reply | Quote

That is a handy little thing. I have a lot of “IIF(IsNull(whatever” expressions.

Unfortunately, I still get the same message about a missing comma or whatever, and they all look fine to me.

Reply | Quote

I have attached an mdb with your query and Charlotte’s query.
Seems to work. Maybe you can copy and past it in your mdb and see what happens.

Reply | Quote

It’s also possible that if you typed in the expression, you missed one of the square brackets around a field or table object. That message is annoying because it is so vague. Try counting the left and right brackets and make sure you have the same number of left and right. Try the same thing with parens. That will at least tell you if you left one out. Unfortunately, it won’t tell you if you misplaced one, and that could also be a problem.

Reply | Quote

OK – Found the problem.

There were to calculated fields that started the same way and I only saw the first one. The second formula was 3 times as long as the first, when all it needed to be was one field plus another (this is an inherited database)

ANYWAY, everything is fine and I got to know about the “Nz” function. – Carlotte, you’re a smoothie and thanks to you too Francois.

Are there any other groovy little functions that cut out a lot of unnecessar cr*p?

Reply | Quote

My approach would be to encapsulate this kind of expression in a VBA function because i can use the de###### and the editing tools. Using the “immediate window” allows me to plug in values and see what the result is.

There is a complete list of functions in the VBA help file – i had to use ‘Chr’ for something yesterday.

Reply | Quote

It’s always a good idea to read through the “What’s New” portion of the documentation. That will catch you up on any new features added from the previous version. I like Split() and Join() and Replace() in 2000, all so much easier than the do-it-yourself functions that had to be written in 97.

Reply | Quote