Date Parameter Query

  • Thread starter Thread starter gator
  • Start date Start date
G

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]"))
 
The conjunctions must be inside the string.



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



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

Michel Walsh said:
The conjunctions must be inside the string.



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



Vanderghast, Access MVP



gator said:
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]"))
Click to expand...
Click to expand...
 
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])")

Michel Walsh said:
The conjunctions must be inside the string.

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

Vanderghast, Access MVP

gator said:
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]"))
Click to expand...
Click to expand...
Click to expand...

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)?
 
If your report is based on [2009TransactionDetail] then don't use a domain
aggregate function. You can use:
=Sum([ChanExp] * Abs([AccountCode]=1))
 
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


gator said:
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])")

Michel Walsh said:
The conjunctions must be inside the string.



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



Vanderghast, Access MVP



gator said:
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]"))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top