Access report textbox format

Question

Good morning all you fine VBA Gurus,

I have a textbox on a report that is based upon a table that was imported from an Excel file.

I want to check the formatting of the textbox value.

it should be something like this "A12345"     ("?#####").

I was hoping to use something like this:

If ([Forms]![Form2A]!Text780) <> "?#####" Then

                [Forms]![Form2A]!Text780 = ""

end if

An IIF statement could work if someone can help me with the syntax.

Thanks

***Changed Office Sub-Topic from Windows other to Microsoft Office Programming***

Answer 1

I said "report" and meant "form".

A textbox on the form.

I did ask repeatedly whether you really meant "report".

What do you want to do on this form?  Do you want to prevent the user from entering a value that doesn't conform to your specified format, or do you want to actually change any non-conforming format they enter to Null (or an empty string)? 

If you just want to prevent them from entering a non-conforming value, then a control-level validation rule would probably be the best way to do that; e.g.,

    ValidationRule:  Like "?#####"

    ValidationText:  That's not a valid value, please correct it.

Alternatively, you could use the control's BeforeUpdate event to accomplish the same thing, but it's wordier:

    Private Sub YourTextBoxName_BeforeUpdate(Cancel As Integer)

        If Not (Me.YourTextBoxName Like "?#####") Then

            Cancel = True

            MsgBox "That's not a valid value, please correct it."

        End If

    End Sub

If you want to physically change an improperly entered value back to Null, then you would have to use the AfterUpdate event instead, because you can't update a control in its own BeforeUpdate event:

    Private Sub YourTextBoxName_AfterUpdate

        If Not (Me.YourTextBoxName Like "?#####") Then

            Me.YourTextBoxName = Null

        End If

    End Sub

If you want to change existing values that the user *didn't* enter or modify via the form, then there is no event of the control or the form that would naturally lend itself to that.  For that you would want to run an update query, as I suggested before.

Answer 2

Might there be a way to use this in an after update event?

Answer 3

You could try something like this in the Control Source of a text box:

    =IIf([YourFieldName] Like "?#####", [YourFieldName], Null)

(where you substitute the actual name of the field for "YourFieldName").

For that to work, the text box must not have the same name as the field itself, so if it does, you have to change the name of the text box.

Note that this won't actually change the stored value of the field.  It just changes what is displayed on the report.

Answer 4

On the report, I'm trying to change the textbox value to empty ("") if the existing textbox value does not meet the ("?#####") format.

The table populating the report was only meant to provide background information.

Answer 5

You said "report" in the thread title, but in the body of your post you only mention a table and a form.  I'm not sure whether the form *or* report is relevant, if what you really want to do is fix incorrect values in the table.  Is that what you want to do? 

From what you said, it sounds like some field in your table should always contain values that are like "?#####", but some don't, and you want to clear those incorrect values.  Is that correct?  If so, then you could run an update query to do that.  It would set all non-conforming values to Null or "", whichever you wanted, and it would do that for all records in the table.

If that's what you want, then you need to tell us the name of the table and the field in the table.  You also ought to be more specific about the required format of the values.  Does the first character have to be a capital letter, or can it be any alphabetic character, or any character at all?