iif statement in a select query

[Walter Steadman]

I have a table and I am trying to get a query to create a value. sanfu
showed me how to get it to work with a sum funciton, but I am curious if I
can get the query to create a field with a date, based on a date equation.
I am trying to put the following in design mode of a query that has one
table below:

tblPilots
PilotID (PK)
LastName
FirstName
Birthmonth mm/dd/yyyy

I drag * into the design grid and then in the next block I put the
following:

starting: IIf(Month(Date()) >Month([birthmonth]),
(dateserial(year(date()-1), month([birthmonth]),
day([birthmonth])),(dateserial(year(date()), month([birthmonth]),
day([birthmonth])

doesn't this mean:

Expression: IIf(Month(Date()) >Month([birthmonth])
result if True: dateserial(year(date()-1), month([birthmonth]),
day([birthmonth]))
result if False: dateserial(year(date()), month([birthmonth]),
day([birthmonth])

I was thinking it would put the following in the new field called starting

Johns birthday is 15 Feb 65
it would put 15 Feb 05

Dan's birthday is 15 Nov 66
it would put 15 Nov 04

when I try to run this query I get the error:

the following expression you entered is missing a closing parenthasis,
bracket or vertical bar

I am using the iif function incorrectly? Can I have it create this type of
query?

When sanfu showed me it was about summing hours. Can you do an iif
statement in a query to create another field with additional data in it? so
that I don't have to store the data in a table?

TIA.

 

[Douglas J. Steele]

Your parentheses are wrong.

starting: IIf(Month(Date()) >Month([birthmonth]), dateserial(year(date()-1),
month([birthmonth]), day([birthmonth])),dateserial(year(date()),
month([birthmonth]), day([birthmonth])))

 

[Walter Steadman]

I played with those parentheses for hours. Is there a good way to break
them down to see if they are all matching or in the right place? I tried
changing the font size of all parenthesis and even changing colors on those
sets that matched up. Thanks for the pointer.

Your parentheses are wrong.

starting: IIf(Month(Date()) >Month([birthmonth]),
dateserial(year(date()-1), month([birthmonth]),
day([birthmonth])),dateserial(year(date()), month([birthmonth]),
day([birthmonth])))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a table and I am trying to get a query to create a value. sanfu
showed me how to get it to work with a sum funciton, but I am curious if I
can get the query to create a field with a date, based on a date equation.
I am trying to put the following in design mode of a query that has one
table below:

tblPilots
PilotID (PK)
LastName
FirstName
Birthmonth mm/dd/yyyy

I drag * into the design grid and then in the next block I put the
following:

starting: IIf(Month(Date()) >Month([birthmonth]),
(dateserial(year(date()-1), month([birthmonth]),
day([birthmonth])),(dateserial(year(date()), month([birthmonth]),
day([birthmonth])

doesn't this mean:

Expression: IIf(Month(Date()) >Month([birthmonth])
result if True: dateserial(year(date()-1), month([birthmonth]),
day([birthmonth]))
result if False: dateserial(year(date()), month([birthmonth]),
day([birthmonth])

I was thinking it would put the following in the new field called
starting

Johns birthday is 15 Feb 65
it would put 15 Feb 05

Dan's birthday is 15 Nov 66
it would put 15 Nov 04

when I try to run this query I get the error:

the following expression you entered is missing a closing parenthasis,
bracket or vertical bar

I am using the iif function incorrectly? Can I have it create this type
of query?

When sanfu showed me it was about summing hours. Can you do an iif
statement in a query to create another field with additional data in it?
so that I don't have to store the data in a table?

TIA.

 

[Van T. Dinh]

The technique I use is to count the parentheses: 1 for opening parenthesis
and -1 for closing parentheses.

At the end, the count should be zero. If the count is +ve, I have too many
opening parentheses and if the count is -ve, I have too many closing
parentheses.

If it is really complex (and in complex SQL), I use extra spaces / indents /
line feeds to work out like:


starting: IIf
(
Month(Date()) >Month([birthmonth]),
DateSerial( Year(Date())-1, Month([birthmonth]),
y([birthmonth]) ),
DateSerial( Year(Date()), Month([birthmonth]), Day([birthmonth]) )
)



You can see that the 3 lines in the IIf statement is for criteria,
expression if True and expression if False respectively. In each of these
lines, you can check the parentheses (by counting as described earlier). The
parentheses for the IIf function also match vertically.

Note that your expression to work out the year for the True part is
incorrect
w.r.t. your description. Also, you have unnecessary parentheses in your
expression. I have corrected these in the posted example above.

 

[Van T. Dinh]

Posting mucked up my SQL String. Here the correct one (and hopefully won't
be mucked up again)

starting: IIf
(
Month(Date()) >Month([birthmonth]),
DateSerial( Year(Date())-1, Month([birthmonth]),
y([birthmonth]) ),
DateSerial( Year(Date()), Month([birthmonth]), Day([birthmonth]) )
)

 

[Van T. Dinh]

Still mucked up. Try again:


starting: IIf
(
Month(Date()) >Month([birthmonth]),
DateSerial( Year(Date())-1, Month([birthmonth]),
y([birthmonth]) ),
DateSerial( Year(Date()), Month([birthmonth]), Day([birthmonth]) )
)



(I give up if it fails again. You get the general idea).

--
HTH
Van T. Dinh
MVP (Access)

The technique I use is to count the parentheses: 1 for opening parenthesis
and -1 for closing parentheses.

At the end, the count should be zero. If the count is +ve, I have too many
opening parentheses and if the count is -ve, I have too many closing
parentheses.

If it is really complex (and in complex SQL), I use extra spaces / indents /
line feeds to work out like:


starting: IIf
(
Month(Date()) >Month([birthmonth]),
DateSerial( Year(Date())-1, Month([birthmonth]),
y([birthmonth]) ),
DateSerial( Year(Date()), Month([birthmonth]), Day([birthmonth]) )
)



You can see that the 3 lines in the IIf statement is for criteria,
expression if True and expression if False respectively. In each of these
lines, you can check the parentheses (by counting as described earlier). The
parentheses for the IIf function also match vertically.

Note that your expression to work out the year for the True part is
incorrect
w.r.t. your description. Also, you have unnecessary parentheses in your
expression. I have corrected these in the posted example above.

--
HTH
Van T. Dinh
MVP (Access)

I played with those parentheses for hours. Is there a good way to break
them down to see if they are all matching or in the right place? I tried
changing the font size of all parenthesis and even changing colors on those
sets that matched up. Thanks for the pointer.

 

[Walter Steadman]

Van and Doug,
Thanks again for your posts. Van I like your ideat about Indenting.
Thanks a bunch.

Wally Steadman
US Army in Iraq

The technique I use is to count the parentheses: 1 for opening parenthesis
and -1 for closing parentheses.

At the end, the count should be zero. If the count is +ve, I have too
many
opening parentheses and if the count is -ve, I have too many closing
parentheses.

If it is really complex (and in complex SQL), I use extra spaces / indents
/
line feeds to work out like:


starting: IIf
(
Month(Date()) >Month([birthmonth]),
DateSerial( Year(Date())-1, Month([birthmonth]),
y([birthmonth]) ),
DateSerial( Year(Date()), Month([birthmonth]), Day([birthmonth]) )
)



You can see that the 3 lines in the IIf statement is for criteria,
expression if True and expression if False respectively. In each of these
lines, you can check the parentheses (by counting as described earlier).
The
parentheses for the IIf function also match vertically.

Note that your expression to work out the year for the True part is
incorrect
w.r.t. your description. Also, you have unnecessary parentheses in your
expression. I have corrected these in the posted example above.

--
HTH
Van T. Dinh
MVP (Access)


message

I played with those parentheses for hours. Is there a good way to break
them down to see if they are all matching or in the right place? I tried
changing the font size of all parenthesis and even changing colors on those
sets that matched up. Thanks for the pointer.