calculate table column expression

[shutzy]

i am trying to clean up all unneccessary queries. i am now trying to get a calculation to apear in a text box.

what i want to calculate is [tblReceipts].[ReceiptCash] If Date()= [tblReceipts].[DateOfReceipt]

am i going about this the wrong way of is my pricipal correct but my expression wrong?

thanks

 

[Severin]

=sum(iif( Date()= [tblReceipts].[DateOfReceipt], [tblReceipts].[ReceiptCash], 0))

 

[Severin]

Straight in a textbox:

=sum(iif( Date()= [DateOfReceipt], [ReceiptCash], 0))

In the query:

SumReceiptCash: sum(iif( Date()= [tblReceipts].[DateOfReceipt], [tblReceipts].[ReceiptCash], 0))

 

[shutzy]

hi thanks for the expression. it comes up with #ERROR in my text box. any reasonwhy it is doing this or am i doing something wrong

 

[shutzy]

does it make any difference if it is in a report.

 

[vbaInet]

Are you writing this calculation in a table or a query?

Your explanation isn't clear either. Do you want to sum the ReceiptCash values where their DateOfReceipt is today's date?

 

[shutzy]

to your second question yes. your first, its in a report. im wanting to put an expression in a text box that returns the value of question 2.

is it possible?

 

[vbaInet]

You put this in the Control Source of the textbox:

=Sum(IIF([DateOfReceipt] = Date(), [ReceiptCash], Null))

The code Severin gave you would have worked but you didn't change it so that it fits the Control Source syntax. Remember for calculations in a Control Source of a textbox, it must begin with the equal to sign (=).

 

[shutzy]

i put in the expression and even tried
=Sum(IIF([tblReceipts].[DateOfReceipt] = Date(), [tblReceipts].[ReceiptCash], Null))
but it is still saying #ERROR.

 

[vbaInet]

In what section of your report did you place this code?

 

[shutzy]

all i have done is manually added a txt box to drag in totals from other tables. im trying to get rid of queries and what i am finding out is that i am not only getting rid of queries i am finding more than 1 way to skin a cat. its quite facinating. the column and even table does not exist in my report at the minute. i was using list boxes that used queries. but then i thought about txt boxes. they are less messy and its easier to do calculations of them than it is from a list box

 

[vbaInet]

If the field doesn't exist in your report then it just won't work. I would expect you to have known this. The aggregate functions work only on fields that exist in the record source of the object.

 

[shutzy]

ha ha ha. what an absolute muppet. ive spent all day trying to do this and the answer was right under my nose. add the fields to the query that i am planning on keeping. the one that the report runs off. ah well. at least i wont forget this one.

thanks for all your help guys. even if it was to point out the obvious.

 

[vbaInet]

I knew that you knew it. You just needed a quick reminder.