Need for assistance using DSUM

[Ahmed.H.Qasim]

"I have a peculiar issue... I have a table named 'amanat' with daily data... and another table 'fordate'. I want to sum the values of the field 'pamount' based on the date 'ta'. It executes for some dates and doesn't for others, as shown in the attachment.

The desired idea: Sum the 'pamount' values from the 'amanat' table that are on or before the specified date in 'fordate'. I have constructed the following query:

DSum("pamounts", "amanat", "pdate <= #" & [ta] & "#")"


3.5

 

[CJ_London]

What format is your ta date? dd/mm/yyyy?

If so that is a common issue and easily resolved- use the format function to convert it to mm/dd/yyyy

 

[Ahmed.H.Qasim]

yes dd/mm/yyyy

 

[Ahmed.H.Qasim]

I have tried a lot... Can you try with the attached file?

 

[Gasman]

Look at this thread
Post in thread 'VBA code to display previous sale date from the same customer' https://www.access-programmers.co.u...te-from-the-same-customer.331965/post-1932676

 

[DHookom]

Your query is an update query and the only values shown are current values. Change to a select query and add the calculation:

SELECT fordate.ada, DSum("pamounts","amanat","pdate<=#" & [ta] & "#") AS Expr1, fordate.ta
FROM fordate;

Also, why is fordate hidden when it enters into this question?

 

[Ahmed.H.Qasim]

Your query is an update query and the only values shown are current values. Change to a select query and add the calculation:

SELECT fordate.ada, DSum("pamounts","amanat","pdate<=#" & [ta] & "#") AS Expr1, fordate.ta
FROM fordate;

Also, why is fordate hidden when it enters into this question?

it dosent work too ,

 

[Gasman]

You have been told. You are using UK date format.!!! You need to use USA or unambiguous format. Just surrounding date with # is only good for USA formats.

 

[MarkK]

If you use a subquery rather than DSum(), you won't need to worry about date formats, like

SELECT fordate.ada, ( SELECT Sum(pamounts) FROM Amanat WHERE pdate <= fordate.ta ) AS SumPriors, fordate.ta
FROM fordate;

 

[DHookom]

I agree with the subquery and think it is more efficient. The minor drawback is the result will not allow edits.

 

[June7]

Review http://allenbrowne.com/ser-36.html

 

[CJ_London]

another method is to use a non standard join - again not editable

SELECT ada, Sum(pamounts) as sumpriors
FROM forDate INNER JOIN Amanat ON Amanat.pdate <= fordate.ta
GROUP BY ada

 

[Gasman]

I have my laptop back up and running, so can now post this.

Option Compare Database
Option Explicit

'Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
Public Const strcJetDate = "\#yyyy-mm-dd\#"  'Needed for dates in queries as Access expects USA but will accept ISO format.

and here is an example of using it

Set db = CurrentDb

strSQL = "INSERT INTO tblDates ( StepsDate ) SELECT DateSerial([YearNo],[MonthNo],[DayNo]) AS Dates"
strSQL = strSQL & " FROM tblDay, tblMonth, tblYear WHERE ((DateSerial([YearNo],[MonthNo],[DayNo])) Between "
strSQL = strSQL & " FROMDATE AND TODATE "
strSQL = strSQL & " AND ((IsDate([MonthNo] & "" / "" & [DayNo] & "" / "" & [YearNo]))<>False))"
strSQL = strSQL & " ORDER BY DateSerial([YearNo],[MonthNo],[DayNo])"

strSQL = Replace(strSQL, "FROMDATE", Format(Me.txtFrom, strcJetDate))
strSQL = Replace(strSQL, "TODATE", Format(Me.txtTo, strcJetDate))

or

lngDate = DLookup("StepsID", "tbldates", "Stepsdate=" & Format(Me.txtDate.Text, strcJetDate))

 

[Ahmed.H.Qasim]

I have my laptop back up and running, so can now post this.

Option Compare Database
Option Explicit

'Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
Public Const strcJetDate = "\#yyyy-mm-dd\#"  'Needed for dates in queries as Access expects USA but will accept ISO format.

and here is an example of using it

Set db = CurrentDb

strSQL = "INSERT INTO tblDates ( StepsDate ) SELECT DateSerial([YearNo],[MonthNo],[DayNo]) AS Dates"
strSQL = strSQL & " FROM tblDay, tblMonth, tblYear WHERE ((DateSerial([YearNo],[MonthNo],[DayNo])) Between "
strSQL = strSQL & " FROMDATE AND TODATE "
strSQL = strSQL & " AND ((IsDate([MonthNo] & "" / "" & [DayNo] & "" / "" & [YearNo]))<>False))"
strSQL = strSQL & " ORDER BY DateSerial([YearNo],[MonthNo],[DayNo])"

strSQL = Replace(strSQL, "FROMDATE", Format(Me.txtFrom, strcJetDate))
strSQL = Replace(strSQL, "TODATE", Format(Me.txtTo, strcJetDate))

or

lngDate = DLookup("StepsID", "tbldates", "Stepsdate=" & Format(Me.txtDate.Text, strcJetDate))

thank you