Expression in query to VB in form

[Guest]

I have the following Calculated field in a query.

STATUS: IIf(Not IsNull([DateReceivedComp]) And IsNull([DateStuffed]) And
IsNull([DateDelivered]) And IsNull([DateReturnedComp]) And
IsNull([DateDeliveredAgtEmp]) And IsNull([DateReturnedCompX]) And
IsNull([DateShipped]),"COMPOUND","DELIVERED")

I would like to transfer this to a form but rewrite this expression in VB.
What would the above be in VB and on which event should this code be placed?

Thanks

 

[pietlinden]

why? just create an unbound control and put this in the control's
rowsource.

 

[Guest]

You asked why?

If you noticed I have several dates some null and some is not null. That
expression accounted for only one date sequence. I have 7 more to write and
all of the end result will be either "Compound" or Delvered.

If I continue to use expression I will need to do 7 more and join them.

What do you think?

 

[pietlinden]

You asked why?

If you noticed I have several dates some null and some is not null. That
expression accounted for only one date sequence. I have 7 more to write and
all of the end result will be either "Compound" or Delvered.

If I continue to use expression I will need to do 7 more and join them.

What do you think?

could you create a form based on the recordsource for this data and
then create a series of unbound controls where you can put the
expressions that will return "Compound" or "Delivered"?

If you calculate them all the same way, you could create a function
that did one complete calculation, then you could just set the control
source of the other calculated fields to that function, and just pass
different date values. Of course, you'd have to declare any of the
date parameters as variants so you could detect nulls...

something along the lines of

fResult(byval strSomeVariable as string, byval varDate1 as variant,
byval varDate2 as variant....) as String

then you would do your complex rules in here...

and then return the result.
fResult= (some variable or expression)
End Function