Date Parameter Query


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

Advertisements

M

Michel Walsh

The conjunctions must be inside the string.



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



Vanderghast, Access MVP
 
G

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

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

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

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

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)?
 
D

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

Advertisements

M

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


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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top