If function in a textbox control source

[c2rich]

I have a text baox that calulates the last order date but if there have not been any orders it shows #error. I tried to use different if functions but access does not seem to like it. Here is the command i am currently using.

="Date of last order:" & Space(2) & [Orders by Customer Subform].Form!OrderDate

If there is another thread for this you can just point me there i couldn't find it.

-Kerry

Those that know, do. Those that understand, teach. -Aristotle

 

[c2rich]

Here is the command i am having issues with

 =if([Orders by Customer Subform].Form!OrderDate,"Date of last order:" & Space(2) & [Orders by Customer Subform].Form!OrderDate,"")

-Kerry

Those that know, do. Those that understand, teach. -Aristotle

 

[straybullet]

You may have tried this already, but in the OnCurrent event of the form, how about something along the lines of the following which of course may need adjusting...

Me.textboxname = Iif(IsNull([Orders by Customer Subform].Form!OrderDate),"","Date of last order:" & Space(2) & [Orders by Customer Subform].Form!OrderDate)

I left the Space(2) in place even though it seems to do the same as Date of last order: " & [Orders by Customer Subform].Form!OrderDate]

Let them hate - so long as they fear... Lucius Accius

 

[c2rich]

I just tried that and it is returning the same info as before, i am wondering if i need to do a lookup function before the iif function


-Kerry

Those that know, do. Those that understand, teach. -Aristotle

 

[straybullet]

How about something along the lines of:

Me.textboxname = Iif(IsNull(Forms!NameOfParentForm!Form![Orders by Customer Subform].OrderDate),"","Date of last order:" & Space(2) & Forms!NameOfParentForm!Form![Orders by Customer Subform].OrderDate)

Let them hate - so long as they fear... Lucius Accius

 

[TheAceMan1]

how are ya c2rich . . .

Your initial code . . .

[blue]   ="Date of last order:" & Space(2) & [Orders by Customer Subform].Form!OrderDate[/blue]

. . . indicates it resides in a control on a parent form.
Be aware: that which is returned by [blue][Orders by Customer Subform].Form!OrderDate[/blue] is dependent on the record in the subform which has the focus! . . .

Also: if you have other calculated controls on your parent form, anytime one needs to update it causes a [blue]ReCalc[/blue] of all! The point being, [blue]you may be trigering and update when the focus in the subform is not where it needs to be . . .[/blue]

[blue]Your thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[c2rich]

Thanks everyone for your help, I think at this point i am going to re-evaluate this feature and remove it, the user can still see the date in the sub form and it is sorted by the last oder date anyways again thanks everyone for your help.

-Kerry

Those that know, do. Those that understand, teach. -Aristotle