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")

 

[lameid]

Two things jump out at me right away...

! instead of a .
Criteria beginning with AND

That said it my like the ! and on the other I don't ever remember using DSUM with an expression so I'm not entirely sure it will work but I suspect it will. Although since you are using only one table (D functions have to, you don't need a fully qualified field/column anyway).

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

 

[dhookom]

In addition, I think there would still be issues with the Between clause and possibly resolving the BeginDate and EndDate. Remove the "<=" and use controls on a form for entering the date range.


Duane
Hook'D on Access
MS Access MVP

 

[rudolfelizabeth]

Your answer is not correct.

I have tried that already doesnot work

 

[dhookom]

Which answer is not correct and what do you mean by "doesnot work"?

Duane
Hook'D on Access
MS Access MVP

 

[rudolfelizabeth]

the answer was not referred to you dhookkom

I had to remove also the <=

but now I have the following error

it ask me for the begindate

but the begindate is input by the user.

 

[PWise]

try

DSum("quantity*price","transactions"," #format([Transdate],'yyyymmdd')# between #format([Begindate],'yyyymmdd')# and  #format([Enddate],'yyyymmdd')# and price >0")

 

[dhookom]

rudolfelizabeth,
Did you consider using controls on a form for entering the dates as I suggested? Check the FAQ's Good Practice in this forum for reasons you should not be using parameter prompts in queries.

What is the full SQL view of your query?

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

What Duane said

Although looking closer at this I'm not sure why you are using format on your dates at all... If they are all dates, you should be able to compare them. You would have to use the [purple]#[/purple] delimiter for date values if using literals.... And you'd have to embed them in a string correctly.

 

[rudolfelizabeth]

Mr Lamei your are completly right that is why I cannot
understand why this is not working.

I used it without it didnot work.


Mr Dhookkom,

The query has the parameter you suggested.

The query has also other Dsum formula in it all are working fine only this one doesn't.

Mr Pwise

Your code isnot working

 

[rudolfelizabeth]

Another thing dot instead of
!
Why is it when you use the expression Builder it put a !
instead of a dot.

 

[dhookom]

Did you consider using controls on a form for entering the dates as I suggested? Check the FAQ's Good Practice in this forum for reasons you should not be using parameter prompts in queries.

What is the full SQL view of your query?

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

rudolfelizabeth:

To help up Debug your problem please
Create a new query with the SQL Below and let un know if this works

Select sum(quantity*price) As TotalsSales
From transactions
Where price>0 and Transdate Between #1/1/1# And #12/31/9#

Please post back any errors that you get

 

[rudolfelizabeth]

Pwise

Bingo you hit the jackpot the code is
SumofdepositsSelect sum(quantity*price) As TotalsSales
From transactions
Where price>0 and Transdate Between [Begindate] And [Enddate] and number=Clientnumber)

Can you explain the varaible totalssales

Thank you very much.

 

[rudolfelizabeth]

Now my question to you experts

Duane , Dhookom and pwise, is the following
it seems that the dsum cannot handle complicated criterias.
because we had to jump to the select statement.
Or another thing nobody knows the exact code not even
those at microsoft who produced the software.
How many columns with a dsum expressions can you use in a single query.

This single query has to use two files a main file and a transactions file.

In the main file you have the client info + beginningbalances

In the transactions file the different clients with their
transactions.

Now do this.

Dsum a running sum between begindate and enddate input by the user.
Dsum the total sum of the transactions + beggingbalance <= then begindate not a ruunning sum a total sum
Dsum the total of the withdrawals a total not a running sum
Dsum the total of the deposits a total not a running sum

I have tried it this is not possible in the same query.

You have to switch to the select statement.

So my conclusion the dsum is unreliable.

 

[dhookom]

DSum() is very accurate and fairly powerful but not very efficient, SQL statements are very accurate and more efficient and flexible, IMO parameter prompts in queries are never acceptable faq701-6763.

You have yet to provide the full SQL statement you are working with or the structures of your table(s) or sample data or expected display or date format of your location or a full specification.

I'm backing away until there is something more from you to work with. Maybe someone else has the time and patience to guess what you have and what you want.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Like duane said, dsum is reliable but slow and less flexible than a query. It returns exactly one value from a domain (query/table) using criteria specified. I believe the help topic tells you all that but I probably haven't read the whole thing in several versions. To be honest I am surprised it would work with parameters in the criteria at all.

I am also in agreement with Duane that we need a lot more information to help. I think when you say "file", you mean in Access parlance "table". Access calls Identities tables. I remember a Mainframe progammer calling them Files.

You say running sum which suggests to me you want to see detail. If so, you want a report, see the running sum property for a control.

You can also sum in the various sections. What I cannot intuit is whether the tables are related or if they are separate and perhaps you want to see two reports in one. In this situation you will want subreports.

That is the insight I can offer from what you posted. Beyond that I think we all need more information.

 

[rudolfelizabeth]

Thank you for your replies

The query is now giving me the result I want because I am using only one dsum in the query, and the other info I use the select statement.

I am using this query to build a statement off account for the clients.

everything is running fine I have only one problem when running the report

That is if there is beginning balance in the main table
ant there are no transactions in the transactions table.

I cannot get the BeginBal on the report so I decided
to use this code in event activation.

Iff Me.BeginBal = "Null" And Me.BeginBalance > 0, "[Beginbal]=[BeginBalance]", "nz([BeginBal],0)"
End If

This code should evalute after the user input the beginningdate and the enddate if the beginingbalance is >0
if so Beginbal=Beginbalance otherwise nz beginbal


Do you have to define these varaibles for this to work how.

 

[lameid]

Sounds like a query problem like you want an outer join instead of an inner join. Your criteria may cause problems...

Please post the SQL of your query.

 

[rudolfelizabeth]

Expr1: IIf([Beginbal]=Null And [BeginBalance]>0,[Beginbal]=[BeginBalance],Nz([Beginbal],0))

In the query I have the BeginBalance
The beginbal = dsum from the main table and the transaction table
but if the main table has a beginbalance >0 from previous years and there are no transaction for the currentyear the query will return a null value for the beginbal,
the code of above should return the value of the main file
it doesnot
I run the query the beginbalance was greater then 0
it returned a null value

Any idea