Help with syntax error 2

[rudolfelizabeth]

access 2003
I am trying to get a sum between two dates input by the user, for the positive amounts in the price field .
I get a syntax error below the code

Positivesum: DSum("quantity*transactions!price","transactions"," and format([Transdate],'yyyymmdd') between format([Begindate],'yyyymmdd') and <= format([Enddate],'yyyymmdd') and Transactions!price >0")

 

[dhookom]

You will never get = Null to work since Null is like an unknown. Also, you can't set a value in you IIf(). [Beginbal]=[BeginBalance] is going to return either 0 or -1

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Duane is exactly right. But it seems like you are trying to get this...

BeginBalForDisplay: Nz([Beginbal],[BeginBalance])

To test for null you would either use the ISnull fucntion or the criteria IS Null...

Another way to do the above...

IIF(Isnull([Beginbal], [BeginBalance],[Beginbal])

Criteria might look like...

Where [BeginBal] Is Null

 

[rudolfelizabeth]

I don't want this in the query, in the query I have

both the beginingbalance
and the beginbal
the query is bound to the report

what is the code for the report
control source

to evaluate that if in the query the beginbal is null
then use the beginingbalance instead if this is > null

 

[rudolfelizabeth]

=IIF(Isnull([Beginbal]), [BeginBalance],[Beginbal])
In the control source of the report does give me an error

when I run the report

 

[lameid]

What error? A hunch is that one of your control names is the same as one of the fields you are using in the expression.

 

[rudolfelizabeth]

box name aliasBeginbal

it just puts the word error on the report

code like above
=IIF(Isnull([Beginbal]), [BeginBalance],[Beginbal])

 

[lameid]

In design view, Select a control.

On the formatting (form/report) toolbar drop down the contol list and try to find beginbal or beginbalance. If you do select it so you know where it is. If it is not exactly equal to the field name, you need to give it a different name.

 

[rudolfelizabeth]

Lameid
Thank you very much

I had another textbox with beginbal
I changed it
everything is working ok

Thank Thank you