Iif Date Formatting Problem

[amclfc]

ACCESS 97

Hi, I have a text box in a report and I am trying the following within expression builder:

=IIf([Offer/Subscription End Date] Is Null,"To be advised","From " & Format([Offer/Subscription Start Date] , "long date") & " to " & Format([Offer/Subscription End Date] , "long date"))

I have also tried "dd/mmmm/yy"

I get the following error message "The function you entered can not be used in this expression."

Can I fix this expression, or is there another way I can combine the text elements of above with the date fields whilst having a long date format?

Any help very much appreciated

 

[Uncle Gizmo]

first of all try this:-

=IIf(IsNull([Offer]/[Subscription End Date]),"Null","Not Null")


What answer(s) do you get?

 

[Rich]

=IIf(IsNull([Offer/Subscription End Date]),"To be advised","From " & Format([Offer/Subscription Start Date] , "dd/mmmm/yy") & " to " & etc.

 

[amclfc]

Rich, I got the same error message with yours

Uncle Gizmo, your =IIf(IsNull([Offer/Subscription End Date]),"Null","Not Null")came back with Not Null

perplexed very am I

This works fine (but wrong date formats): =IIf([Offer/Subscription End Date] Is Null,"To be advised","From " & [Offer/Subscription Start Date] & " to " & [Offer/Subscription End Date])

 

[Rich]

Uncle Gizmo, your =IIf(IsNull([Offer/Subscription End Date]),"Null","Not Null")came back with Not Null

replace that with
=IIf(IsDate([Offer/Subscription End Date]),"Null","Valid")

 

[amclfc]

This is the first time I have tried to use the format function,

I have just tried below and I still get the error message "The function you entered can not be used in this expression."?????

=Format([Offer/Subscription Start Date],"dd/mmmm/yy")

 

[ecniv]

=IIf(isnull([Offer/Subscription End Date]) OR isnull([Offer/Subscription Start Date]),"To be advised","From " & Format([Offer/Subscription Start Date] , "dd mmmm yyyy") & " to " & Format([Offer/Subscription End Date] , "dd mmmm yyyy"))

otherwise it gets tricky, and you'd need to use the NZ function to check whether the date you are trying to convert but haven't checked is null... if that makes sense.


Vince

 

[amclfc]

Hi Guys, you are being very helpful thank you, I still get the same error message with all our variants.

When I try =Format([Offer/Subscription Start Date],"dd/mmmm/yy") all on its own I still get the error message. So I now think my problem is to do with format if you know what I mean. Why can't I can't use format on its lonesome? (asked in a pulling my hair out styly)

 

[Rich]

Like I said, try the IsDate function first

 

[ecniv]

try:

=iif(isnull([datefield],'',format([datefield],"dd mmmm yyyy"))

Post up your results


Vince

 

[amclfc]

Hi Rich

=IIf(IsDate([Offer/Subscription End Date]),"Null","Valid") comes back with null when I have a date in the field [Offer/Subscription End Date] and comes back with valid when there is nothing in the field [Offer/Subscription End Date].

How does this help?

 

[ecniv]

What Rich is trying to say is that the format function cannot work on nothing, there has to be a date there. So the isdate (you did read the help on isdate function right??) tells the comp if the data is a date.


Vince

 

[amclfc]

Hi Vince

=iif(isnull([datefield],'',format([datefield],"dd mmmm yyyy"))

I get the error message "The function you entered can not be used in this expression."

Same as just having =Format([Offer/Subscription Start Date],"dd/mmmm/yy") all on its own. I defo have an isolated problem with using format on date fields

 

[Rich]

because it tells me that your date field is not a valid date. Are you sure it's not a text value?

 

[amclfc]

I think I get it now, will be back in a mo.......

 

[amclfc]

Thank you all once again.....

I have just tried the following which should only try to format if there is a date if I have finally caught up. The fields being queried are date fields by the way with only null or dates by way of values

=IIf(IsDate([Offer/Subscription End Date]),"From " & Format([Offer/Subscription Start Date] , "dd/mmm/yy") & " to " & Format([Offer/Subscription End Date] , "dd/mmm/yy",),"To be advised")

I am still getting the same error message

I'll be back......

 

[amclfc]

The problem seems to be null values as when I emulate the scenario with a table containing no null values in the date field being interigated then I have no problem, this is my latest attempt....

=Nz([Offer/Subscription End Date],"To be advised","From " & Format([Offer/Subscription Start Date] , "long date") & " to " & Format([Offer/Subscription End Date] , "long date")))

I got the usual error message :-(

Please help, I don't want to have to put some bogus date in.

 

[Rich]

You need to validate the StartDate too

 

[amclfc]

FIXED

Turned out to be a reference problem to do with a missing active X control

Thank you all very much

 

[Lister]

I am now getting the same error on a 97db that I am "fixing".
What was the Active X control that was turned off or missing?