Crosstab and Date help needed!

[Becky]

Hello - I have made a crosstab query to show the net cost of utililities for
a particular month (per site).

Whilst I have got the net cost amount to go into the correct month, I also
wish to show when a payment is covering a period. For example, if I had a
bill that covered Jan, Feb and Mar - I would like to see the cost go into the
March column and something like a # or - (doesn't matter about the symbol) in
Jan and Feb, this is so I can clearly see if we are missing an invoice or
not.

I have thought about using an IF statement but I think this would overwrite
the net cost amount?

EG (I know this doesn't work as this crashed my access!)
IF [Net] Between [Start_Date] And [End_Date], "#";

Any help you can give me would be appreciated, sql isn't my strong point!

Thanks

 

[John Spencer]

The correct syntax would be
IIF([Net] Between [Start_Date] and [End_Date],"#",Null)

Now whether or not that will really give you what you are looking for is
another question.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Becky]

Ahh of course brackets ;-)

Although that doesn't crash my spreadsheet (thank you) it isn't giving me
what I am after... I am coming to the conclusion that as I can only order one
thing by Value (Which is Net at the moment) I can't add another expression in
ordered by Value as it wont let me.

The correct syntax would be
IIF([Net] Between [Start_Date] and [End_Date],"#",Null)

Now whether or not that will really give you what you are looking for is
another question.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello - I have made a crosstab query to show the net cost of utililities for
a particular month (per site).

Whilst I have got the net cost amount to go into the correct month, I also
wish to show when a payment is covering a period. For example, if I had a
bill that covered Jan, Feb and Mar - I would like to see the cost go into the
March column and something like a # or - (doesn't matter about the symbol) in
Jan and Feb, this is so I can clearly see if we are missing an invoice or
not.

I have thought about using an IF statement but I think this would overwrite
the net cost amount?

EG (I know this doesn't work as this crashed my access!)
IF [Net] Between [Start_Date] And [End_Date], "#";

Any help you can give me would be appreciated, sql isn't my strong point!

Thanks