Can you help with IIf() function?

[Mayo123]

I have a field called dtDate in the table, when there's no dates in the field at all, it shows a blank on the form. I'd rather to assign a value to it, as below, but it doesn't work. Can you help me with this?
IIf([dtDate] is null,Now(),[dtDate])

Thanks!

 

[rjoubert]

Try...

IIf(IsNull([dtDate]),Now(),[dtDate])

And if that doesn't work, try this...

IIf(Not IsDate([dtDate]),Now(),[dtDate])

 

[Mayo123]

Both are not working,
Thanks anyway!

 

[JerryKlmns]

??
NZ([dtDate], Now())

 

[jebry]

Hi!

Is this going in the control source of a text box? If so don't forget that you need an = in front of the expression.

One other observation. If you want the user to be able to put a value in this field, you will need to save it programmatically since the control source will not be bound to anything. Even if you want to just store the current date you will need to program it.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Mayo123]

I did it like this---iif(NZ([dtDate]), Now(),[dtDate])
but it doesn't work?

Thanks!

 

[lespaul]

you say you are trying to display something from a table in a form, are you trying to put this formula on the control on the form? you'll need to do it in a query and use that as the record source of the form in order to display the correct information.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[GingerR]

Change the name of your text box. If you put a bound text box on a form, say the field name is

AddDate

Access makes the name of the text box "AddDate"

If you change the controlsource to something other than the name of the associated field, it doesn't work. I usually just stick a "1" after it.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Mayo123]

I tried all the tricks, it just refused to work.
Thanks to you all!

 

[Mayo123]

I found a way out. The textbox is still bounded to the field. Instead of using iif() function, I did the following

On the form_Load event
If IsNull(Me.dtDate) Then
Me.dtDate = Date
End If