IIF QUERY SYNTAX QUESTION IN ACCESS

  • Thread starter Thread starter Ashley
  • Start date Start date
A

Ashley

I have a query like

sum(If location = 'USA' or Location = Canada and date > date() or date
is null
then 1, 0)


how do I write this statement correctly in access


sum (IIF ( (location = 'USA' or location = 'CANADA' ) and (date
date() or IS NULL (date)) , 1 , 0 ))


does not works in access. Is there a way of doing this correctly in
ACCESS.
 
You shouldn't use Date as the name of a field. If you cannot (or will not)
change it, at least put square brackets around it. You could also use the Nz
function to convert Null dates to something that will be higher than today's
date, since a null value in the > comparison might be causing problems.

Try:

Sum(Iif([location[ = 'USA' or [location] = 'CANADA' ) and (Nz([Date],
#12/31/9999#) > Date()), 1, 0))

If that doesn't work, what exactly is the problem you're having? Are you
getting an error? If so, what's the error? If you're not getting an error,
are you getting a value, but it's not what you expect? If so, what's the
difference between what you're getting and what you expect to get?
 
I have a query like

sum(If location = 'USA' or Location = Canada and date > date() or date
is null
then 1, 0)

how do I write this statement correctly in access

sum (IIF ( (location = 'USA' or location = 'CANADA' ) and (date

does not works in access. Is there a way of doing this correctly in
ACCESS.

In a query?
Add a new column.
CountOfLocations:Sum(IIf(([Location] = "USA" Or [Location] = "Canada")
And ([DateField]>Date() or [DateField] Is Null),1,0)

Note: If you have a date field named date, Date is a reserved keyword
in Access and should not be used as a Field name.
 
this works, but is too smart to be good practise. It relies on True = -1

-sum((location IN ("USA","Canada")) AND (mydate>date()) OR ISNULL(mydate ))

date really is not a good name for a field in Access, it is a reserved
word.

It is good practise to group expressions in brackets to make sure Access is
evaluating things in the order you desire.

Sum(IIf((([location]="USA") Or ([location]="Canada")) And
(([mydate]>Date()) Or IsNull([mydate])),1,0))

is another way
 
Sum(IIf((([location]="USA") Or ([location]="Canada")) And
(([mydate]>Date()) Or IsNull([mydate])),1,0))

GIVES ME A SYNTAX ERROR




this works, but is too smart to be good practise. It relies on True = -1

-sum((location IN ("USA","Canada")) AND (mydate>date()) OR ISNULL(mydate ))

date really is not a good name for a field in Access, it is a reserved
word.

It is good practise to group expressions in brackets to make sure Access is
evaluating things in the order you desire.

Sum(IIf((([location]="USA") Or ([location]="Canada")) And
(([mydate]>Date()) Or IsNull([mydate])),1,0))

is another way

Ashley said:
I have a query like

sum(If location = 'USA' or Location = Canada and date > date() or date
is null
then 1, 0)


how do I write this statement correctly in access


sum (IIF ( (location = 'USA' or location = 'CANADA' ) and (date


does not works in access. Is there a way of doing this correctly in
ACCESS.
 
THATS OK I RESOLVED IT
David said:
this works, but is too smart to be good practise. It relies on True = -1

-sum((location IN ("USA","Canada")) AND (mydate>date()) OR ISNULL(mydate ))

date really is not a good name for a field in Access, it is a reserved
word.

It is good practise to group expressions in brackets to make sure Access is
evaluating things in the order you desire.

Sum(IIf((([location]="USA") Or ([location]="Canada")) And
(([mydate]>Date()) Or IsNull([mydate])),1,0))

is another way

Ashley said:
I have a query like

sum(If location = 'USA' or Location = Canada and date > date() or date
is null
then 1, 0)


how do I write this statement correctly in access


sum (IIF ( (location = 'USA' or location = 'CANADA' ) and (date


does not works in access. Is there a way of doing this correctly in
ACCESS.
 

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

Back
Top