DAO recordset Update validation problem

  • Thread starter Thread starter Marcel K.
  • Start date Start date
M

Marcel K.

To Whom It May Concern:
I get runtime error 13 'Type Mismatch' when trying to
validate a text box control inside recorset update below:

!General_Status = IIf(Len(Trim(Me.txtGeneral_Status) <
1), "N/A", Me.txtGeneral_Status)

Is this a viable way to validate?

Thanks,

Marcel K.

The below works as expected:

!General_Status = Me.txtGeneral_Status
 
I think you have one of the right parentheses in the wrong position - it
should be:

!General_Status = IIf(Len(Trim(Me.txtGeneral_Status) )<> 1, "N/A",
Me.txtGeneral_Status)

This is not validation - you are just replacing any value that is of length
not equal to 1 with the string "N/A". If this is what you want to do then
it's fine.

I am guessing that you are checking for controls where NO value was entered.
In this case, I would personally recommend leaving the Null along and then
when needed in a query, replace nulls with the string "N/A".
 
Also, if Me.TxtGeneral_Status is Null, then the comparison returns Null, and the
IIF will return the second choice. At least, it does when I check it in the
immediate window.

I would either check the length of Me.TxtGeneral_Status & ""
OR use the NZ function
OR use the formatting functions to display/return "N/A"

Sandra said:
I think you have one of the right parentheses in the wrong position - it
should be:

!General_Status = IIf(Len(Trim(Me.txtGeneral_Status) )<> 1, "N/A",
Me.txtGeneral_Status)

This is not validation - you are just replacing any value that is of length
not equal to 1 with the string "N/A". If this is what you want to do then
it's fine.

I am guessing that you are checking for controls where NO value was entered.
In this case, I would personally recommend leaving the Null along and then
when needed in a query, replace nulls with the string "N/A".

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
To Whom It May Concern:
I get runtime error 13 'Type Mismatch' when trying to
validate a text box control inside recorset update below:

!General_Status = IIf(Len(Trim(Me.txtGeneral_Status) <
1), "N/A", Me.txtGeneral_Status)

Is this a viable way to validate?

Thanks,

Marcel K.

The below works as expected:

!General_Status = Me.txtGeneral_Status
Click to expand...
Click to expand...
 
Thanks for you input, Sandra.
-----Original Message-----
I think you have one of the right parentheses in the wrong position - it
should be:

!General_Status = IIf(Len(Trim(Me.txtGeneral_Status) )<> 1, "N/A",
Me.txtGeneral_Status)

This is not validation - you are just replacing any value that is of length
not equal to 1 with the string "N/A". If this is what you want to do then
it's fine.

I am guessing that you are checking for controls where NO value was entered.
In this case, I would personally recommend leaving the Null along and then
when needed in a query, replace nulls with the string "N/A".

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

To Whom It May Concern:
I get runtime error 13 'Type Mismatch' when trying to
validate a text box control inside recorset update below:

!General_Status = IIf(Len(Trim(Me.txtGeneral_Status) <
1), "N/A", Me.txtGeneral_Status)

Is this a viable way to validate?

Thanks,

Marcel K.

The below works as expected:

!General_Status = Me.txtGeneral_Status
Click to expand...

.
Click to expand...
 
Back
Top