dsum sytax error

[bert]

Hi All,

I try to make a running total for the stock of parts in our company.
We have a table sales
and a table purchases
I combined those two tables with union select to get all incoming and
outgoing numbers for the parts in ONE table, incoming positive and
outgoing negative.
Finally I make a table called tmp of this union select query.

Then This goes wrong, I try to use DSUM in a query to get the running
totals of our stock.

Stock: DSum("hoeveelheidinkoop";"tmp";"#[inkoopdatum]#<=" &
"#[inkoopdatumalias]#" & "")

"hoeveelheidinkoop" = the qty
"tmp"= my table
#[inkoopdatum]# - my date field
the #[inkoopdatumaliasis]# just the alias of the date

Hope I am clear of what I'm trying to accomplish

Thanks, Bert

PS: the sql for this one:
SELECT tmp.PartID, tmp.inkoopdatum AS inkoopdatuma,
Sum(tmp.hoeveelheidinkoop) AS SomVanhoeveelheidinkoop,
DSum("hoeveelheidinkoop","tmp","#[inkoopdatum]#<=" &
"#[inkoopdatuma]#" & "") AS voorraad
FROM tmp
GROUP BY tmp.PartID, tmp.inkoopdatum;

 

[John W. Vinson]

Hi All,

I try to make a running total for the stock of parts in our company.
We have a table sales
and a table purchases
I combined those two tables with union select to get all incoming and
outgoing numbers for the parts in ONE table, incoming positive and
outgoing negative.
Finally I make a table called tmp of this union select query.

Then This goes wrong, I try to use DSUM in a query to get the running
totals of our stock.

Stock: DSum("hoeveelheidinkoop";"tmp";"#[inkoopdatum]#<=" &
"#[inkoopdatumalias]#" & "")

"hoeveelheidinkoop" = the qty
"tmp"= my table
#[inkoopdatum]# - my date field
the #[inkoopdatumaliasis]# just the alias of the date

Hope I am clear of what I'm trying to accomplish

Thanks, Bert

PS: the sql for this one:
SELECT tmp.PartID, tmp.inkoopdatum AS inkoopdatuma,
Sum(tmp.hoeveelheidinkoop) AS SomVanhoeveelheidinkoop,
DSum("hoeveelheidinkoop","tmp","#[inkoopdatum]#<=" &
"#[inkoopdatuma]#" & "") AS voorraad
FROM tmp
GROUP BY tmp.PartID, tmp.inkoopdatum;

You're overusing the # character. It's a delimiter for literal date strings,
just as " or ' is a delimiter for text strings; it needs to be included around
any date string such as 5/4/2012. It is not needed around fieldnames - just
around literal dates.

Try

Stock: DSum("hoeveelheidinkoop";"tmp";"[inkoopdatum]<= [inkoopdatumalias]")

Access knows that inkoopdatumalias is a date value, so it's just comparing the
actual values.

If (in some other context) you had a Text string such as "5/4/2012" - say from
a form control - then you need the # marks; this will keep Access from
misinterpreting the string as an arithmatic operation (five divided by four
divided by 2012):

Stock: DSum("hoeveelheidinkoop";"tmp";"[inkoopdatum]<= #" &
[Forms]![MyFormName]![textboxname] & "#")

If textboxname contains that date string the criterion comes out to

[inkoopdatum]<= #5/4/2012#


--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[bert]

Hi All,

I try to make a running total for the stock of parts in our company.
We have a table sales
and a table purchases
I combined those two tables with union select to get all incoming and
outgoing numbers for the parts in ONE table, incoming positive and
outgoing negative.
Finally I make a table called tmp of this union select query.

Then This goes wrong, I try to use DSUM in a query to get the running
totals of our stock.

Stock: DSum("hoeveelheidinkoop";"tmp";"#[inkoopdatum]#<=" &
"#[inkoopdatumalias]#" & "")

"hoeveelheidinkoop" = the qty
"tmp"= my table
#[inkoopdatum]# - my date field
the #[inkoopdatumaliasis]# just the alias of the date

Hope I am clear of what I'm trying to accomplish

Thanks, Bert

PS: the sql for this one:
SELECT tmp.PartID, tmp.inkoopdatum AS inkoopdatuma,
Sum(tmp.hoeveelheidinkoop) AS SomVanhoeveelheidinkoop,
DSum("hoeveelheidinkoop","tmp","#[inkoopdatum]#<=" &
"#[inkoopdatuma]#" & "") AS voorraad
FROM tmp
GROUP BY tmp.PartID, tmp.inkoopdatum;

You're overusing the # character. It's a delimiter for literal date strings,
just as " or ' is a delimiter for text strings; it needs to be included around
any date string such as 5/4/2012. It is not needed around fieldnames - just
around literal dates.

Try

Stock: DSum("hoeveelheidinkoop";"tmp";"[inkoopdatum]<= [inkoopdatumalias]")

Access knows that inkoopdatumalias is a date value, so it's just comparing the
actual values.

If (in some other context) you had a Text string such as "5/4/2012" - sayfrom
a form control - then you need the # marks; this will keep Access from
misinterpreting the string as an arithmatic operation (five divided by four
divided by 2012):

Stock: DSum("hoeveelheidinkoop";"tmp";"[inkoopdatum]<= #" &
[Forms]![MyFormName]![textboxname] & "#")

If textboxname contains that date string the criterion comes out to

[inkoopdatum]<= #5/4/2012#

--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi John,

Thanks!!
That was indeed wrong, thought I had already tried that but it is
solved now.
Only now the original plan trying to get the stock numbers per date
fails. I have to try something else.

Thanks again, Bert

 

[bert]

Hi All,
I try to make a running total for the stock of parts in our company.
We have a table sales
and a table purchases
I combined those two tables with union select to get all incoming and
outgoing numbers for the parts in ONE table, incoming positive and
outgoing negative.
Finally I make a table called tmp of this union select query.
Then This goes wrong, I try to use DSUM in a query to get the running
totals of our stock.
Stock: DSum("hoeveelheidinkoop";"tmp";"#[inkoopdatum]#<=" &
"#[inkoopdatumalias]#" & "")
"hoeveelheidinkoop" = the qty
"tmp"= my table
#[inkoopdatum]# - my date field
the #[inkoopdatumaliasis]# just the alias of the date
Hope I am clear of what I'm trying to accomplish
Thanks, Bert
PS: the sql for this one:
SELECT tmp.PartID, tmp.inkoopdatum AS inkoopdatuma,
Sum(tmp.hoeveelheidinkoop) AS SomVanhoeveelheidinkoop,
DSum("hoeveelheidinkoop","tmp","#[inkoopdatum]#<=" &
"#[inkoopdatuma]#" & "") AS voorraad
FROM tmp
GROUP BY tmp.PartID, tmp.inkoopdatum;

You're overusing the # character. It's a delimiter for literal date strings,
just as " or ' is a delimiter for text strings; it needs to be includedaround
any date string such as 5/4/2012. It is not needed around fieldnames - just
around literal dates.

Try

Stock: DSum("hoeveelheidinkoop";"tmp";"[inkoopdatum]<= [inkoopdatumalias]")

Access knows that inkoopdatumalias is a date value, so it's just comparing the
actual values.

If (in some other context) you had a Text string such as "5/4/2012" - say from
a form control - then you need the # marks; this will keep Access from
misinterpreting the string as an arithmatic operation (five divided by four
divided by 2012):

Stock: DSum("hoeveelheidinkoop";"tmp";"[inkoopdatum]<= #" &
[Forms]![MyFormName]![textboxname] & "#")

If textboxname contains that date string the criterion comes out to

[inkoopdatum]<= #5/4/2012#

--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi John,

Thanks!!
That was indeed wrong, thought I had already tried that but it is
solved now.
Only now the original plan trying to get the stock numbers per date
fails. I have to try something else.

Thanks again, Bert

Hi John,

Maybe I may ask a follow up question.
The result of the dsum is now the grand total, for every row in the
query (no errors)
Wthout the "="sign I get blanks.

Is there a way to get a running total over my dates?

Thanks, Bert

 

[John W. Vinson]

Maybe I may ask a follow up question.
The result of the dsum is now the grand total, for every row in the
query (no errors)
Wthout the "="sign I get blanks.

Is there a way to get a running total over my dates?

This is easy in a Report - just set the Running Sum property of a report
textbox appropriately.

It's a bit more work in a Form; you need a DSum() expression which explicitly
identifies which are the "previous records". I don't know the structure of
your tables well enough to be specific but I'd guess you'll want a two-part
criterion - one to select the rows that belong in the subform, and another to
separate out the rows before this row.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[bert]

This is easy in a Report - just set the Running Sum property of a report
textbox appropriately.

It's a bit more work in a Form; you need a DSum() expression which explicitly
identifies which are the "previous records". I don't know the structure of
your tables well enough to be specific but I'd guess you'll want a two-part
criterion - one to select the rows that belong in the subform, and another to
separate out the rows before this row.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi John,

Thx again!
The report is no problem indeed. I'll go for that option, wasted to
much time on this query already

All the best, Bert