Iif Statment

[Thorson]

I have written several iif statements, but I am not sure how to write one to
fit this problem. I have two fields in my query: EntryDateMonth and Month. I
would like to do an iif statment that "iif the fields EntryDateMonth and
Month are not equal then put nothing other wise put the text correction"

Thanks!

 

[fredg]

I have written several iif statements, but I am not sure how to write one to
fit this problem. I have two fields in my query: EntryDateMonth and Month. I
would like to do an iif statment that "iif the fields EntryDateMonth and
Month are not equal then put nothing other wise put the text correction"

Thanks!

NewColumn:IIf([EntryDateMonth] <> [Month],Null,"Correcton")

Be careful, as a month value entered as 04 is not the same as a month
value entered as 4, nor is Apr the same as 04 or 4 or April.

I suspect your database is not properly designed in this respect.

Note:
Month is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

 

[Thorson]

Thank you, I was not aware that 04 would not work the same as 4. I am sure
my database is not properly designed in this area.

What changes would I need to make to fix the design of this? Just go
through and change the reserved word "Month"?

Thank you for your help.

--
Thorson

I have written several iif statements, but I am not sure how to write one to
fit this problem. I have two fields in my query: EntryDateMonth and Month. I
would like to do an iif statment that "iif the fields EntryDateMonth and
Month are not equal then put nothing other wise put the text correction"

Thanks!

NewColumn:IIf([EntryDateMonth] <> [Month],Null,"Correcton")

Be careful, as a month value entered as 04 is not the same as a month
value entered as 4, nor is Apr the same as 04 or 4 or April.

I suspect your database is not properly designed in this respect.

Note:
Month is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

 

[Thorson]

For some reason I can't get the query to recognize the field
"EntryDateMonth". This is a experiment field in this query that pulls the
month from the entry date field in a different query. I tried putting
[qryMonthlyReportPG1DSAC]![EntryDateMonth] but it still wouldn't recognize
it.

What am I doing wrong?

Thanks
--
Thorson

I have written several iif statements, but I am not sure how to write one to
fit this problem. I have two fields in my query: EntryDateMonth and Month. I
would like to do an iif statment that "iif the fields EntryDateMonth and
Month are not equal then put nothing other wise put the text correction"

Thanks!

NewColumn:IIf([EntryDateMonth] <> [Month],Null,"Correcton")

Be careful, as a month value entered as 04 is not the same as a month
value entered as 4, nor is Apr the same as 04 or 4 or April.

I suspect your database is not properly designed in this respect.

Note:
Month is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

 

[John Spencer]

Is the other query part of this query? If the query is not displayed in
the upper half of the query window, then the fields in that query are
not available.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Thorson]

The field EntryDateMonth is actually a calculated field in the current query,
the same query that I want to put the new column:
NewColumn:IIf([EntryDateMonth] <> [Month],Null,"Correcton")

It is not pulling this from a different query but is instead pulling it from
the query it is in. Do I need to split them out into 2 queries?

The field EntryDateMonth is just pulls the month of the entry date of the
record: EntryDateMonth: Month([qryDSACMonthlyDispositionRecords]![EntryDate])

--
Thorson

Is the other query part of this query? If the query is not displayed in
the upper half of the query window, then the fields in that query are
not available.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

For some reason I can't get the query to recognize the field
"EntryDateMonth". This is a experiment field in this query that pulls the
month from the entry date field in a different query. I tried putting
[qryMonthlyReportPG1DSAC]![EntryDateMonth] but it still wouldn't recognize
it.

What am I doing wrong?

Thanks

 

[John Spencer MVP]

It is often (almost always) necessary to repeat the calculation instead of
just referring to the result. It is always necessary to repeat the
calculation if you are applying any criteria to the new column in the query.

Try this expression.

NewColumn:IIf(Month([qryDSACMonthlyDispositionRecords]![EntryDate])<>[Month],Null,"Correction")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Thorson]

Works Perfectly!!! Thanks.
--
Thorson

It is often (almost always) necessary to repeat the calculation instead of
just referring to the result. It is always necessary to repeat the
calculation if you are applying any criteria to the new column in the query.

Try this expression.

NewColumn:IIf(Month([qryDSACMonthlyDispositionRecords]![EntryDate])<>[Month],Null,"Correction")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

The field EntryDateMonth is actually a calculated field in the current query,
the same query that I want to put the new column:
NewColumn:IIf([EntryDateMonth] <> [Month],Null,"Correcton")

It is not pulling this from a different query but is instead pulling it from
the query it is in. Do I need to split them out into 2 queries?

The field EntryDateMonth is just pulls the month of the entry date of the
record: EntryDateMonth: Month([qryDSACMonthlyDispositionRecords]![EntryDate])