IIF problem

[Travis]

I have a query that takes a date from a table and then shows a due date on
the datasheet for it. Here is what I have:

DateDue:
IIf(IsNull([DateDone]),DateValue(Now()),IIf([Training].[Course_Frequency]="Onetime","Onetime",DateAdd("m",[Training].[Course_Frequency],[DateDone])))

For some reason there is a problem with the Onetime part... it is coming up
with #Error... could anyone throw some insight please?

 

[KC-Mass]

Assuming your DateDue is a date field it will not accept text.


Regards

Kevin

 

[John Spencer MVP]

DateDue:
IIf(IsNull([DateDone]),Date(),
IIf([Training].[Course_Frequency]="Onetime","Onetime",
DateAdd("m",[Training].[Course_Frequency],[DateDone])))

I would guess that Course_Frequency (a text field) contains a value that
cannot be interpreted as a number and therefore is generating an error. Are
you getting any error message? Perhaps Error 13: Type Mismatch.

If Course_Frequency contains values like 1 month, 2 months, 3 Months, etc.
then you could use Val(Course_Frequency) to force a number value.

DateDue:
IIf(IsNull([DateDone]),Date(),
IIf([Training].[Course_Frequency]="Onetime","Onetime",
DateAdd("m",Val([Training].[Course_Frequency] & ""),[DateDone])))

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

 

[Travis]

Its a datasheet that's simply displaying that info. its not a table field.
Basically i have a subform that in its query builder it has that statement in
its own column. I dont even know how to make it a date field

 

[Travis]

Besides the #Error in the datasheet field, there is nothing that describes
what is wrong. Course_Frequency has fields that are mostly numbers such as
12,15,20,24 but it also has "Onetime" as a value. That's why I made the IIf
statement to check if it has that as the "Onetime" value? If it does then
simply write than if not then the do the DateAdd function.

 

[KC-Mass]

Travis

If it is not deeply embedded in forms, reports and queries, what
if you try setting the course_frequency notation for one time courses
to 0 rather than "OneTime". That would make the datedue the same
as the datedone which seems a logical portrayal. It also makes your
query work.

Just a thought but I have found that having a field that has some records
with wholey numeric data and some with Alpha data will eventually lead
to problems.

Its a datasheet that's simply displaying that info. its not a table field.
Basically i have a subform that in its query builder it has that statement
in
its own column. I dont even know how to make it a date field

I have a query that takes a date from a table and then shows a due date
on
the datasheet for it. Here is what I have:

DateDue:
IIf(IsNull([DateDone]),DateValue(Now()),IIf([Training].[Course_Frequency]="Onetime","Onetime",DateAdd("m",[Training].[Course_Frequency],[DateDone])))

For some reason there is a problem with the Onetime part... it is coming
up
with #Error... could anyone throw some insight please?

 

[Travis]

I definately see what you're getting at, although, to make it user friendly
especially to those who will be looking at this to make sure all training is
being complied with, having it show the same date as the date done could get
confusing and lead people to believe that the employee is due for the
training, maybe i'm going at this the wrong way? is there another way I can
achieve the same outcome?

Just to reiterate, I am wanting to grab DateDone from the query and grab the
course_frequency then use the course_frequency to formulate a DateDue field
to be shown in the datasheet. Although there are some courses that are only
taken once, therefore that needs to be shown in a user friendly way.

Travis

If it is not deeply embedded in forms, reports and queries, what
if you try setting the course_frequency notation for one time courses
to 0 rather than "OneTime". That would make the datedue the same
as the datedone which seems a logical portrayal. It also makes your
query work.

Just a thought but I have found that having a field that has some records
with wholey numeric data and some with Alpha data will eventually lead
to problems.

Its a datasheet that's simply displaying that info. its not a table field.
Basically i have a subform that in its query builder it has that statement
in
its own column. I dont even know how to make it a date field

I have a query that takes a date from a table and then shows a due date
on
the datasheet for it. Here is what I have:

DateDue:
IIf(IsNull([DateDone]),DateValue(Now()),IIf([Training].[Course_Frequency]="Onetime","Onetime",DateAdd("m",[Training].[Course_Frequency],[DateDone])))

For some reason there is a problem with the Onetime part... it is coming
up
with #Error... could anyone throw some insight please?

 

[KC-Mass]

Hi Travis,

Lets say you change that onetime to 0. You add another column to be a
textual representation of the retrain period "Semi-Annual", "Bi-Monthly",
"OneTime", etc.

When you run the query you get the next date for training of each course.
In the case of the "OneTime"
Its the same date as the DateDone. Add a new column, "Next" in the query
that test whether the dueDate and Done Date are the same. If true, put Null
in the column, otherwise put DateDue in the Column. Add one more column
that grabs the textual representation of the retrain Period. Your query
would then produce something like this:


qryProblem DateDone DateDue Next TextOfFrequency
12/3/2009 1/3/2010 1/3/2010 Monthly
12/3/2007 12/3/2007
OneTime
10/2/2008 12/2/2008 12/2/2008 BiMonthly



Regards
Kevin

I definately see what you're getting at, although, to make it user friendly
especially to those who will be looking at this to make sure all training
is
being complied with, having it show the same date as the date done could
get
confusing and lead people to believe that the employee is due for the
training, maybe i'm going at this the wrong way? is there another way I
can
achieve the same outcome?

Just to reiterate, I am wanting to grab DateDone from the query and grab
the
course_frequency then use the course_frequency to formulate a DateDue
field
to be shown in the datasheet. Although there are some courses that are
only
taken once, therefore that needs to be shown in a user friendly way.

Travis

If it is not deeply embedded in forms, reports and queries, what
if you try setting the course_frequency notation for one time courses
to 0 rather than "OneTime". That would make the datedue the same
as the datedone which seems a logical portrayal. It also makes your
query work.

Just a thought but I have found that having a field that has some records
with wholey numeric data and some with Alpha data will eventually lead
to problems.

Its a datasheet that's simply displaying that info. its not a table
field.
Basically i have a subform that in its query builder it has that
statement
in
its own column. I dont even know how to make it a date field

:

I have a query that takes a date from a table and then shows a due
date
on
the datasheet for it. Here is what I have:

DateDue:
IIf(IsNull([DateDone]),DateValue(Now()),IIf([Training].[Course_Frequency]="Onetime","Onetime",DateAdd("m",[Training].[Course_Frequency],[DateDone])))

For some reason there is a problem with the Onetime part... it is
coming
up
with #Error... could anyone throw some insight please?

 

[KC-Mass]

Hi Travis,

Just tried to fix the format here

Hi Travis,

Lets say you change that onetime to 0. You add another column to be a
textual representation of the retrain period "Semi-Annual", "Bi-Monthly",
"OneTime", etc.

When you run the query you get the next date for training of each course.
In the case of the "OneTime"
Its the same date as the DateDone. Add a new column, "Next" in the query
that test whether the dueDate and Done Date are the same. If true, put
Null in the column, otherwise put DateDue in the Column. Add one more
column that grabs the textual representation of the retrain Period. Your
query would then produce something like this:


DateDone DateDue Next TextOfFrequency
12/3/2009 1/3/2010 1/3/2010 Monthly
12/3/2007 12/3/2007 OneTime
10/2/2008 12/2/2008 12/2/2008 BiMonthly



Regards
Kevin

I definately see what you're getting at, although, to make it user
friendly
especially to those who will be looking at this to make sure all training
is
being complied with, having it show the same date as the date done could
get
confusing and lead people to believe that the employee is due for the
training, maybe i'm going at this the wrong way? is there another way I
can
achieve the same outcome?

Just to reiterate, I am wanting to grab DateDone from the query and grab
the
course_frequency then use the course_frequency to formulate a DateDue
field
to be shown in the datasheet. Although there are some courses that are
only
taken once, therefore that needs to be shown in a user friendly way.

Travis

If it is not deeply embedded in forms, reports and queries, what
if you try setting the course_frequency notation for one time courses
to 0 rather than "OneTime". That would make the datedue the same
as the datedone which seems a logical portrayal. It also makes your
query work.

Just a thought but I have found that having a field that has some
records
with wholey numeric data and some with Alpha data will eventually lead
to problems.




Its a datasheet that's simply displaying that info. its not a table
field.
Basically i have a subform that in its query builder it has that
statement
in
its own column. I dont even know how to make it a date field

:

I have a query that takes a date from a table and then shows a due
date
on
the datasheet for it. Here is what I have:

DateDue:
IIf(IsNull([DateDone]),DateValue(Now()),IIf([Training].[Course_Frequency]="Onetime","Onetime",DateAdd("m",[Training].[Course_Frequency],[DateDone])))

For some reason there is a problem with the Onetime part... it is
coming
up
with #Error... could anyone throw some insight please?