#ERROR in textbox

[ktrasler]

Hi all

How do I stop #ERROR from appearing in a text box when there is no data in a required field.

my function is =Age([DOB]). This works fine as long as there is a date in the DOB field.

I need something along the lines of

=IF(IsError(Age([DOB])),"""",Age([DOB])).

Or is this the wrong approach. Should I modify the function to trap the error.

Cheers

Kev

 

[boblarson]

=IF(IsNull([DOB]),"",Age([DOB]))

 

[ktrasler]

Hi Bob

I am still getting #Name? appear in the textbox.

the function I am using is:

Public Function Age(DOB As Date) As Integer

Age = DateDiff("yyyy", DOB, Date) + (Date < DateSerial(Year(Date), Month(DOB), Day(DOB)))
  
End Function

 

[ktrasler]

Hi Bob

I am still getting #Name? appear in the textbox.

the function I am using is:

Public Function Age(DOB As Date) As Integer

Age = DateDiff("yyyy", DOB, Date) + (Date < DateSerial(Year(Date), Month(DOB), Day(DOB)))
  
End Function

I entered =IF(IsNull([DOB]),"",Age([DOB])) in the control source box of the text box.

Sorry if I'm missing something very simple.

Cheers

Kev

 

[boblarson]

If you use a formula in a control source, your field name CAN NOT be named the same as your control I'm guessing you have a control named DOB as well and should change it to txtDOB. When you make the change and save it, the autocorrect will likely change the formula to txtDOB too, but change it back to just DOB.

 

[boblarson]

Oh, and also don't use DOB As Date for the variable use dteDOB or datDOB so that it doesn't get confused with your field DOB.

 

[ktrasler]

Hi bob

Thanks for your replies, but I still can get this to work. Here's what I have now.

Textbox named txtDOB with its control source as DOB field

Formula in control source of age textbox (txtAge)
=IF(IsNull([DOB]),"",Age([DOB]))

Function

Public Function Age(dtDOB As Date) As Integer
  
  Age = DateDiff("yyyy", dtDOB, Date) + (Date < DateSerial(Year(Date), Month(dtDOB), Day(dtDOB)))
    
End Function

The function still works but I am getting #Name? still in the text box.

Any ideas?

 

[boblarson]

Maybe if you go with two (2) I's:

=IIF(IsNull([DOB]),"",Age([DOB]))

 

[ktrasler]

Hi Bob

OK, now that works fine.

What is IIF then, never seen it used before?!?!?

Cheers

KEv

 

[boblarson]

IIf is what is called an IMMEDIATE IF and it is the only IF you can use for queries, control sources, etc. You can use IF in VBA but pretty much elsewhere you have to use IIF.

 

[ktrasler]

Thanks

I see, useful to know.

Thanks for you help.

appreciate it.

Kev