Date Parameter Query

[gator]

I'm getting an error in the textbox of my report.

2009TransactionDetail is a Parameter Query using BeginDate and EndDate as
Date Parameters. Can someone see something wrong with the syntax?

=DSum("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=1" And ("[TranDate]=[BeginDate]" Or "[TranDate]=[EndDate]"))
-
DSum("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=2" And ("[TranDate]=[BeginDate]" Or "[TranDate]=[EndDate]"))

 

[Michel Walsh]

The conjunctions must be inside the string.



=DSum("[ChanExp]","2009TransactionsDetail",
" [AccountCode]=1 And ([TranDate]=[BeginDate] Or [TranDate]=[EndDate]) ")



Vanderghast, Access MVP

 

[gator]

I used the following and still get an error#
=DSum("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=1 AND ([TranDate]>=[BeginDate] Or [TranDate]<=[EndDate])")
-DSum
("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=2 AND ([TranDate]>=[BeginDate] Or [TranDate]<=[EndDate])")

The conjunctions must be inside the string.



=DSum("[ChanExp]","2009TransactionsDetail",
" [AccountCode]=1 And ([TranDate]=[BeginDate] Or [TranDate]=[EndDate]) ")



Vanderghast, Access MVP

I'm getting an error in the textbox of my report.

2009TransactionDetail is a Parameter Query using BeginDate and EndDate as
Date Parameters. Can someone see something wrong with the syntax?

=DSum("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=1" And ("[TranDate]=[BeginDate]" Or
"[TranDate]=[EndDate]"))
-
DSum("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=2" And ("[TranDate]=[BeginDate]" Or
"[TranDate]=[EndDate]"))

 

[fredg]

I used the following and still get an error#
=DSum("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=1 AND ([TranDate]>=[BeginDate] Or [TranDate]<=[EndDate])")
-DSum
("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=2 AND ([TranDate]>=[BeginDate] Or [TranDate]<=[EndDate])")

The conjunctions must be inside the string.

=DSum("[ChanExp]","2009TransactionsDetail",
" [AccountCode]=1 And ([TranDate]=[BeginDate] Or [TranDate]=[EndDate]) ")

Vanderghast, Access MVP

I'm getting an error in the textbox of my report.

2009TransactionDetail is a Parameter Query using BeginDate and EndDate as
Date Parameters. Can someone see something wrong with the syntax?

=DSum("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=1" And ("[TranDate]=[BeginDate]" Or
"[TranDate]=[EndDate]"))
-
DSum("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=2" And ("[TranDate]=[BeginDate]" Or
"[TranDate]=[EndDate]"))

Date values must be enclosed within the date delimiter symbol (#),
and the value must be concatenated into the expression.

=DSum("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=1 AND ([TranDate] >= #" & [BeginDate] & "# Or
[TranDate] <= #" & [EndDate] & "#)")
-DSum("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=2 AND ([TranDate] >= #" & [BeginDate] "# Or [TranDate
<= #" & [EndDate] & "#)")

By the way, shouldn't the date part of the expression
be >= [BeginDate] AND <= [EndDate] (not OR which will always return
every date)?

 

[Duane Hookom]

If your report is based on [2009TransactionDetail] then don't use a domain
aggregate function. You can use:
=Sum([ChanExp] * Abs([AccountCode]=1))

 

[Michel Walsh]

if BeginDate and EndDate both come from a control in your report, the
syntax to use is different:



= DSum("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=1 AND
([TranDate] >= REPORTS!ReportNameHere![BeginDate]
Or [TranDate] <= REPORTS!ReportName![EndDate])")



Where you replace ReportNameHere with the real report name



Vanderghast, Access MVP

I used the following and still get an error#
=DSum("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=1 AND ([TranDate]>=[BeginDate] Or [TranDate]<=[EndDate])")
-DSum
("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=2 AND ([TranDate]>=[BeginDate] Or [TranDate]<=[EndDate])")

The conjunctions must be inside the string.



=DSum("[ChanExp]","2009TransactionsDetail",
" [AccountCode]=1 And ([TranDate]=[BeginDate] Or [TranDate]=[EndDate])
")



Vanderghast, Access MVP

I'm getting an error in the textbox of my report.

2009TransactionDetail is a Parameter Query using BeginDate and EndDate
as
Date Parameters. Can someone see something wrong with the syntax?

=DSum("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=1" And ("[TranDate]=[BeginDate]" Or
"[TranDate]=[EndDate]"))
-
DSum("[ChanExp]","2009TransactionsDetail",
"[AccountCode]=2" And ("[TranDate]=[BeginDate]" Or
"[TranDate]=[EndDate]"))