How do I write an IIf......Between.....And expression?

G

Guest

I am trying to write an expression in a date field in a query that says:

IIf([Date] isBetween [Q1 Start Date] And [Seminar Start Date],
Sum([Production Amount],0))

I know I have it written wrong here - but I don't know how to do an
IIf....Between....And....

Please help!! I have been working on this query in hopes to build a report
off of it for weeks now - and I have hit a wall. I appreciate any assistance.

Thanks. You all are great!
 
T

Tom Lake

Erika M. said:
I am trying to write an expression in a date field in a query that says:

IIf([Date] isBetween [Q1 Start Date] And [Seminar Start Date],
Sum([Production Amount],0))

I know I have it written wrong here - but I don't know how to do an
IIf....Between....And....

Please help!! I have been working on this query in hopes to build a
report
off of it for weeks now - and I have hit a wall. I appreciate any
assistance.

Thanks. You all are great!
Click to expand...

I'd do this:

IIf([Date] >= [Q1 Start Date] And [Date] <= [Seminar Start Date],
Sum([Production Amount], 0))

Tom Lake
 
G

Guest

Here is what I have now:

Q1 NAFYC:IIf( [PRODUCTION_SUMMARY_CONCATENATED]![SOURCE_DATE] >= [Seminar
Tracking Quarters]![Q1 Start Date] And
[PRODUCTION_SUMMARY_CONCATENATED]![SOURCE_DATE] <= [Seminar Tracking
Quarters]![Seminar Start Date] ,Sum(
[PRODUCTION_SUMMARY_CONCATENATED]![MO_NAFYC_CR_AMT] ,0))

But now I am getting an error message stating that I have the wrong number
of arguments. Help!!!

Tom Lake said:
Erika M. said:
I am trying to write an expression in a date field in a query that says:

IIf([Date] isBetween [Q1 Start Date] And [Seminar Start Date],
Sum([Production Amount],0))

I know I have it written wrong here - but I don't know how to do an
IIf....Between....And....

Please help!! I have been working on this query in hopes to build a
report
off of it for weeks now - and I have hit a wall. I appreciate any
assistance.

Thanks. You all are great!
Click to expand...

I'd do this:

IIf([Date] >= [Q1 Start Date] And [Date] <= [Seminar Start Date],
Sum([Production Amount], 0))

Tom Lake
Click to expand...
 
G

Guest

I have written it as you said and I am getting an error message that says I
have the wrong number of arguments. Here is what I have:

Q1 NAFYC:IIf( [PRODUCTION_SUMMARY_CONCATENATED]![SOURCE_DATE] >= [Seminar
Tracking Quarters]![Q1 Start Date] And
[PRODUCTION_SUMMARY_CONCATENATED]![SOURCE_DATE] <= [Seminar Tracking
Quarters]![Seminar Start Date] ,Sum(
[PRODUCTION_SUMMARY_CONCATENATED]![MO_NAFYC_CR_AMT] ,0))

What am I doing wrong??

Tom Lake said:
Erika M. said:
I am trying to write an expression in a date field in a query that says:

IIf([Date] isBetween [Q1 Start Date] And [Seminar Start Date],
Sum([Production Amount],0))

I know I have it written wrong here - but I don't know how to do an
IIf....Between....And....

Please help!! I have been working on this query in hopes to build a
report
off of it for weeks now - and I have hit a wall. I appreciate any
assistance.

Thanks. You all are great!
Click to expand...

I'd do this:

IIf([Date] >= [Q1 Start Date] And [Date] <= [Seminar Start Date],
Sum([Production Amount], 0))

Tom Lake
Click to expand...
 
G

Guest

I have written that and here is what it looks like:

IIf( [PRODUCTION_SUMMARY_CONCATENATED]![SOURCE_DATE] >= [Seminar Tracking
Quarters]![Q1 Start Date] And
[PRODUCTION_SUMMARY_CONCATENATED]![SOURCE_DATE] <= [Seminar Tracking
Quarters]![Seminar Start Date] ,Sum(
[PRODUCTION_SUMMARY_CONCATENATED]![MO_NAFYC_CR_AMT])),"0

But I am getting an error message that I have the wrong number of arguments.
What am I doing wrong??

Tom Lake said:
Erika M. said:
I am trying to write an expression in a date field in a query that says:

IIf([Date] isBetween [Q1 Start Date] And [Seminar Start Date],
Sum([Production Amount],0))

I know I have it written wrong here - but I don't know how to do an
IIf....Between....And....

Please help!! I have been working on this query in hopes to build a
report
off of it for weeks now - and I have hit a wall. I appreciate any
assistance.

Thanks. You all are great!
Click to expand...

I'd do this:

IIf([Date] >= [Q1 Start Date] And [Date] <= [Seminar Start Date],
Sum([Production Amount], 0))

Tom Lake
Click to expand...
 
G

Guest

Still not working. I have checked all fields to make sure they are formatted
as dates. Neither your suggestion or Tom's suggestion are working. Now I am
getting "Not part of an aggregate function" - Any other ideas??

David F Cox said:
BETWEEN

not isbetween

Erika M. said:
I am trying to write an expression in a date field in a query that says:

IIf([Date] isBetween [Q1 Start Date] And [Seminar Start Date],
Sum([Production Amount],0))

I know I have it written wrong here - but I don't know how to do an
IIf....Between....And....

Please help!! I have been working on this query in hopes to build a
report
off of it for weeks now - and I have hit a wall. I appreciate any
assistance.

Thanks. You all are great!
Click to expand...
Click to expand...
 
G

Guest

Try this --

Q1 NAFYC:IIf([PRODUCTION_SUMMARY_CONCATENATED]![SOURCE_DATE] Between
[Seminar Tracking Quarters]![Q1 Start Date] And [Seminar Tracking
Quarters]![Seminar Start Date],
Sum([PRODUCTION_SUMMARY_CONCATENATED]![MO_NAFYC_CR_AMT]) ,0)



Erika M. said:
Still not working. I have checked all fields to make sure they are formatted
as dates. Neither your suggestion or Tom's suggestion are working. Now I am
getting "Not part of an aggregate function" - Any other ideas??

David F Cox said:
BETWEEN

not isbetween

Erika M. said:
I am trying to write an expression in a date field in a query that says:

IIf([Date] isBetween [Q1 Start Date] And [Seminar Start Date],
Sum([Production Amount],0))

I know I have it written wrong here - but I don't know how to do an
IIf....Between....And....

Please help!! I have been working on this query in hopes to build a
report
off of it for weeks now - and I have hit a wall. I appreciate any
assistance.

Thanks. You all are great!
Click to expand...
Click to expand...
Click to expand...
 
D

David F Cox

Date is an Access reserved word?


Erika M. said:
Still not working. I have checked all fields to make sure they are
formatted
as dates. Neither your suggestion or Tom's suggestion are working. Now I
am
getting "Not part of an aggregate function" - Any other ideas??

David F Cox said:
BETWEEN

not isbetween

Erika M. said:
I am trying to write an expression in a date field in a query that says:

IIf([Date] isBetween [Q1 Start Date] And [Seminar Start Date],
Sum([Production Amount],0))

I know I have it written wrong here - but I don't know how to do an
IIf....Between....And....

Please help!! I have been working on this query in hopes to build a
report
off of it for weeks now - and I have hit a wall. I appreciate any
assistance.

Thanks. You all are great!
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

I believe your "argument" error was caused by
missing ")" before ",0)"

so you would also lose ending ")"

i.e., there's no such thing as

SUM(somefield, 0)

only

SUM(somefield)

or

SUM(some expression)

But...the SUM needs to be "outside" the IIF...

the IIF part needs to be an expression within the SUM( )

each field in a totals query needs to be either:
(in design grid -- the "Totals" row)

-- group by
-- expression
-- an aggregate function

try this (all one line w/o word wrapping):

Q1 NAFYC: SUM(
IIF(
PRODUCTION_SUMMARY_CONCATENATED.SOURCE_DATE >=
[Seminar Tracking Quarters].[Q1 Start Date]
AND
PRODUCTION_SUMMARY_CONCATENATED.SOURCE_DATE <=
[Seminar Tracking Quarters].[Seminar Start Date] ,
PRODUCTION_SUMMARY_CONCATENATED.MO_NAFYC_CR_AMT ,0))

or

Q1 NAFYC: SUM(
IIF(
PRODUCTION_SUMMARY_CONCATENATED.SOURCE_DATE
BETWEEN
[Seminar Tracking Quarters].[Q1 Start Date]
AND
[Seminar Tracking Quarters].[Seminar Start Date] ,
PRODUCTION_SUMMARY_CONCATENATED.MO_NAFYC_CR_AMT ,0))

Erika M. said:
Here is what I have now:

Q1 NAFYC:IIf( [PRODUCTION_SUMMARY_CONCATENATED]![SOURCE_DATE] >= [Seminar
Tracking Quarters]![Q1 Start Date] And
[PRODUCTION_SUMMARY_CONCATENATED]![SOURCE_DATE] <= [Seminar Tracking
Quarters]![Seminar Start Date] ,Sum(
[PRODUCTION_SUMMARY_CONCATENATED]![MO_NAFYC_CR_AMT] ,0))

But now I am getting an error message stating that I have the wrong number
of arguments. Help!!!

Tom Lake said:
Erika M. said:
I am trying to write an expression in a date field in a query that says:

IIf([Date] isBetween [Q1 Start Date] And [Seminar Start Date],
Sum([Production Amount],0))

I know I have it written wrong here - but I don't know how to do an
IIf....Between....And....

Please help!! I have been working on this query in hopes to build a
report
off of it for weeks now - and I have hit a wall. I appreciate any
assistance.

Thanks. You all are great!
Click to expand...

I'd do this:

IIf([Date] >= [Q1 Start Date] And [Date] <= [Seminar Start Date],
Sum([Production Amount], 0))

Tom Lake
Click to expand...
Click to expand...
 
G

Gary Walter

what I should have said...

in the design grid, your field row would be:
(all one row)

Q1 NAFYC:
IIF(
PRODUCTION_SUMMARY_CONCATENATED.SOURCE_DATE
BETWEEN
[Seminar Tracking Quarters].[Q1 Start Date]
AND
[Seminar Tracking Quarters].[Seminar Start Date] ,
PRODUCTION_SUMMARY_CONCATENATED.MO_NAFYC_CR_AMT ,0)

and the "Totals:" row in that column would be set to SUM

if you were looking at the SQL of your query, it would look like:

SELECT
somefield,
someotherfield,

SUM(
IIF(
PRODUCTION_SUMMARY_CONCATENATED.SOURCE_DATE
BETWEEN
[Seminar Tracking Quarters].[Q1 Start Date]
AND
[Seminar Tracking Quarters].[Seminar Start Date] ,
PRODUCTION_SUMMARY_CONCATENATED.MO_NAFYC_CR_AMT ,0))
AS Q1 NAFYC,

.....

FROM sometable
GROUP BY
somefield,
someotherfield;

Gary Walter said:
I believe your "argument" error was caused by
missing ")" before ",0)"

so you would also lose ending ")"

i.e., there's no such thing as

SUM(somefield, 0)

only

SUM(somefield)

or

SUM(some expression)

But...the SUM needs to be "outside" the IIF...

the IIF part needs to be an expression within the SUM( )

each field in a totals query needs to be either:
(in design grid -- the "Totals" row)

-- group by
-- expression
-- an aggregate function

try this (all one line w/o word wrapping):

Q1 NAFYC: SUM(
IIF(
PRODUCTION_SUMMARY_CONCATENATED.SOURCE_DATE >=
[Seminar Tracking Quarters].[Q1 Start Date]
AND
PRODUCTION_SUMMARY_CONCATENATED.SOURCE_DATE <=
[Seminar Tracking Quarters].[Seminar Start Date] ,
PRODUCTION_SUMMARY_CONCATENATED.MO_NAFYC_CR_AMT ,0))

or

Q1 NAFYC: SUM(
IIF(
PRODUCTION_SUMMARY_CONCATENATED.SOURCE_DATE
BETWEEN
[Seminar Tracking Quarters].[Q1 Start Date]
AND
[Seminar Tracking Quarters].[Seminar Start Date] ,
PRODUCTION_SUMMARY_CONCATENATED.MO_NAFYC_CR_AMT ,0))

Erika M. said:
Here is what I have now:

Q1 NAFYC:IIf( [PRODUCTION_SUMMARY_CONCATENATED]![SOURCE_DATE] >= [Seminar
Tracking Quarters]![Q1 Start Date] And
[PRODUCTION_SUMMARY_CONCATENATED]![SOURCE_DATE] <= [Seminar Tracking
Quarters]![Seminar Start Date] ,Sum(
[PRODUCTION_SUMMARY_CONCATENATED]![MO_NAFYC_CR_AMT] ,0))

But now I am getting an error message stating that I have the wrong
number
of arguments. Help!!!

Tom Lake said:
I am trying to write an expression in a date field in a query that
says:

IIf([Date] isBetween [Q1 Start Date] And [Seminar Start Date],
Sum([Production Amount],0))

I know I have it written wrong here - but I don't know how to do an
IIf....Between....And....

Please help!! I have been working on this query in hopes to build a
report
off of it for weeks now - and I have hit a wall. I appreciate any
assistance.

Thanks. You all are great!

I'd do this:

IIf([Date] >= [Q1 Start Date] And [Date] <= [Seminar Start Date],
Sum([Production Amount], 0))

Tom Lake
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

of course, you would need brackets around
your field alias if you insist on using spaces
in your object names....

[Q1 NAFYC]:
IIF(
PRODUCTION_SUMMARY_CONCATENATED.SOURCE_DATE
BETWEEN
[Seminar Tracking Quarters].[Q1 Start Date]
AND
[Seminar Tracking Quarters].[Seminar Start Date] ,
PRODUCTION_SUMMARY_CONCATENATED.MO_NAFYC_CR_AMT ,0)

and the "Totals:" row in that column would be set to SUM

if you were looking at the SQL view of your query, it would look like:

SELECT
somefield,
someotherfield,

SUM(
IIF(
PRODUCTION_SUMMARY_CONCATENATED.SOURCE_DATE
BETWEEN
[Seminar Tracking Quarters].[Q1 Start Date]
AND
[Seminar Tracking Quarters].[Seminar Start Date] ,
PRODUCTION_SUMMARY_CONCATENATED.MO_NAFYC_CR_AMT ,0))
AS [Q1 NAFYC],

.....

FROM sometable
GROUP BY
somefield,
someotherfield;
 

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

Similar Threads

IIF Between (two numbers not cells) expression 2
Access Building a IIF expression in Access 0
Running sum expression 2
Between date with greater than or eaqual to 4
iif and between 3
IIF with Dateserial and Between 2
iif statement and defining calculated expression once only 1
IIF Expression with Between Dates criteria 7

Top