Creating calculated fields on report

[WLC]

I have a field I need to create on my report that needs to be based on what is in another field on my report.

If Note (that's my field name) = 1,2,3,4,5, or 6, I want to sum a field called PlateNumbers.

Can anyone help me with the syntax?

 

[pr2-eugin]

Something like this?

Sum(IIf(Note >=1 And Note <=6, PlateNumber, 0))

Not tested !

 

[WLC]

tested and worked.

add 2 more numbers in there:

If Note = 1,2,3,4,5, 6,23, or 24, I want to sum a field called PlateNumbers.

I tried:

Sum(IIf((Note >=1 And Note <=6) and (Note >=23 and Note<=24), PlateNumber, 0))

but that didn't work (I think this is a case of I can't see the forest for the trees kind of thing.)

 

[CazB]

try an OR instead of the AND?

as in

Sum(IIf((Note >=1 And Note <=6) or (Note >=23 and Note<=24), PlateNumber, 0))

Again, not tested

 

[pr2-eugin]

Change the AND to OR.

Sum(IIf((Note >= 1 And Note <= 6) [COLOR=Red][B]OR [/B][B]Note = 23 OR Note = 24[/B][/COLOR], PlateNumber, 0))

This will also work.

Sum(IIf((Note >= 1 And Note <= 6) [COLOR=Red][B]OR[/B][/COLOR] (Note >= 23 And Note <= 24), PlateNumber, 0))

 

[jdraw]

Try this -- Untested

Sum(IIf((Note Between 1 And 6) OR (Note = 23) OR (Note = 24)), PlateNumber, 0))

 

[pr2-eugin]

Try this -- Untested

Sum(IIf((Note Between 1 And 6) OR (Note = 23) OR (Note = 24)), PlateNumber, 0))

I was under the impression, BETWEEN is not really liked by VBA (unless used in dynamic queries)? That's the reason I went with <= And >=

Again, I am not sure, might even be wrong here !

 

[WLC]

Sum(IIf((Note >= 1 And Note <= 6) OR Note = 23 OR Note = 24, PlateNumber, 0))

worked for me

Thanks for the quick responses.

 

[jdraw]

I'm not sure Paul -- I know that Between in Access/Jet/ACC is inclusive of end points.

 

[pr2-eugin]

I think I am correct in aspect that BETWEEN cannot be used in VBA, when I used in an If, it came up with "Expected Then or GoTo" Compiler error, when I tried using Between in Select case, I get "Expected End of Statement" Compiler error. IIF also comes with the compiler error.

I agree Between will include the end points, just not sure if it can be used in conjunction with IIF statement.

 

[Wiz47]

I think I am correct in aspect that BETWEEN cannot be used in VBA, when I used in an If, it came up with "Expected Then or GoTo" Compiler error, when I tried using Between in Select case, I get "Expected End of Statement" Compiler error. IIF also comes with the compiler error.

I agree Between will include the end points, just not sure if it can be used in conjunction with IIF statement.

I use the BETWEEN (for dates normally) in a query all the time, but find that VBA does not like the term; even when using an IIF statement.