IIf Expression

G

Guest

Logically I have 4 fields in a query.

ReceievedDate
DeliveredBackDate
ShippedDate
StorageDays (Calculated Field)

When the 3 above Date fields have values I have the stoge days to be
calculated (DeliveredDate - ReceievedDate)

Below is my expression:
StorageDays: IIf(Not IsNull([ReceivedDate]) And ([DeliveredBackDate]) And
([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]))

The problem is that my result field is blank, why?

Thanks
 
G

Guest

Hi Levans -- Your statement is only checking if the 1st date is not null and
you don't have a statement to handle the false part of the IIf. Try:

IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),FALSE
STATEMENT HERE)
 
G

Guest

Hey thanks,
But I am kinda new here. If the statment is false I want the field to have
no value what must I enter to denote that?

xRoachx said:
Hi Levans -- Your statement is only checking if the 1st date is not null and
you don't have a statement to handle the false part of the IIf. Try:

IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),FALSE
STATEMENT HERE)

Levans digital said:
Logically I have 4 fields in a query.

ReceievedDate
DeliveredBackDate
ShippedDate
StorageDays (Calculated Field)

When the 3 above Date fields have values I have the stoge days to be
calculated (DeliveredDate - ReceievedDate)

Below is my expression:
StorageDays: IIf(Not IsNull([ReceivedDate]) And ([DeliveredBackDate]) And
([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]))

The problem is that my result field is blank, why?

Thanks
Click to expand...
Click to expand...
 
G

Guest

IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]), Null)

OR

IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),"")


Levans digital said:
Hey thanks,
But I am kinda new here. If the statment is false I want the field to have
no value what must I enter to denote that?

xRoachx said:
Hi Levans -- Your statement is only checking if the 1st date is not null and
you don't have a statement to handle the false part of the IIf. Try:

IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),FALSE
STATEMENT HERE)

Levans digital said:
Logically I have 4 fields in a query.

ReceievedDate
DeliveredBackDate
ShippedDate
StorageDays (Calculated Field)

When the 3 above Date fields have values I have the stoge days to be
calculated (DeliveredDate - ReceievedDate)

Below is my expression:
StorageDays: IIf(Not IsNull([ReceivedDate]) And ([DeliveredBackDate]) And
([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]))

The problem is that my result field is blank, why?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Karl Thanks,

That solves that problem. Refering back to my original post. I started out
with 3 date fields. Now I added another date field. However after that
expression become true and data is added into the fourth date field
[ReturnedDate) the "False" part of the expression does not occur, instead the
true part still occurs, why?

KARL DEWEY said:
IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]), Null)

OR

IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),"")


Levans digital said:
Hey thanks,
But I am kinda new here. If the statment is false I want the field to have
no value what must I enter to denote that?

xRoachx said:
Hi Levans -- Your statement is only checking if the 1st date is not null and
you don't have a statement to handle the false part of the IIf. Try:

IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),FALSE
STATEMENT HERE)

:

Logically I have 4 fields in a query.

ReceievedDate
DeliveredBackDate
ShippedDate
StorageDays (Calculated Field)

When the 3 above Date fields have values I have the stoge days to be
calculated (DeliveredDate - ReceievedDate)

Below is my expression:
StorageDays: IIf(Not IsNull([ReceivedDate]) And ([DeliveredBackDate]) And
([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]))

The problem is that my result field is blank, why?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Please re-state your problem giving example of input data, query SQL,
results, and results you want.

Levans digital said:
Karl Thanks,

That solves that problem. Refering back to my original post. I started out
with 3 date fields. Now I added another date field. However after that
expression become true and data is added into the fourth date field
[ReturnedDate) the "False" part of the expression does not occur, instead the
true part still occurs, why?

KARL DEWEY said:
IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]), Null)

OR

IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),"")


Levans digital said:
Hey thanks,
But I am kinda new here. If the statment is false I want the field to have
no value what must I enter to denote that?

:

Hi Levans -- Your statement is only checking if the 1st date is not null and
you don't have a statement to handle the false part of the IIf. Try:

IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),FALSE
STATEMENT HERE)

:

Logically I have 4 fields in a query.

ReceievedDate
DeliveredBackDate
ShippedDate
StorageDays (Calculated Field)

When the 3 above Date fields have values I have the stoge days to be
calculated (DeliveredDate - ReceievedDate)

Below is my expression:
StorageDays: IIf(Not IsNull([ReceivedDate]) And ([DeliveredBackDate]) And
([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]))

The problem is that my result field is blank, why?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

KARL I FIXED MY PROBLEM WITH THE FOLLOWING LENTHY EXPRESSION AND IT WORKS.
Storagedays: IIf(Not IsNull([ReceivedDate]) And IsNull([StuffedDate]) And
IsNull([DeliveredDate]) And IsNull([ReturnedDate]) And Not
IsNull([DeliveredBackDate]) And IsNull([ReturnedBackDate]) And Not
IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredBackDate]),"")

IS THERE ANYWAY TO SHORTEN THIS?

THE ABOVE EXPRESSION MEETS ONE VARIABLE OF A TOTAL OF 7 EACH OTHER VARIABLE
WILL BE ABOUT THAT LENTH. IS IT POSSIBLE TO COMBINE ALL OF THEM IN ONE
EXPRESSION

EXAMPLE: SOME "ISNULL" WILL BE "NOT ISNULL" AND THE OTHER WAY AROUND.

WHAT U THINK OR MUST IT BE DONE INCODE?

THANKS

KARL DEWEY said:
Please re-state your problem giving example of input data, query SQL,
results, and results you want.

Levans digital said:
Karl Thanks,

That solves that problem. Refering back to my original post. I started out
with 3 date fields. Now I added another date field. However after that
expression become true and data is added into the fourth date field
[ReturnedDate) the "False" part of the expression does not occur, instead the
true part still occurs, why?

KARL DEWEY said:
IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]), Null)

OR

IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),"")


:

Hey thanks,
But I am kinda new here. If the statment is false I want the field to have
no value what must I enter to denote that?

:

Hi Levans -- Your statement is only checking if the 1st date is not null and
you don't have a statement to handle the false part of the IIf. Try:

IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),FALSE
STATEMENT HERE)

:

Logically I have 4 fields in a query.

ReceievedDate
DeliveredBackDate
ShippedDate
StorageDays (Calculated Field)

When the 3 above Date fields have values I have the stoge days to be
calculated (DeliveredDate - ReceievedDate)

Below is my expression:
StorageDays: IIf(Not IsNull([ReceivedDate]) And ([DeliveredBackDate]) And
([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]))

The problem is that my result field is blank, why?

Thanks
Click to expand...
Click to expand...
Click to expand...
 

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 Statement 1
Access Building a IIF expression in Access 0
Sum IIf Statement Values on form 1
IIF statement...what is wrong w/ this expression? 5
Conditions with a nested IIF 2
Query Parameter 4
Same expression gets different results in Report and Query 1
Expression in query to VB in form 3

Top