Update Checkbox with Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to update a yes/no check box to yes/true if a field contains certain
text.
eg: if a partdescription contains "NLA" or "N.L.A" etc, update the
"discontinued" field to yes/true.
How would i go about this?
 
I need to update a yes/no check box to yes/true if a field contains certain
text.
eg: if a partdescription contains "NLA" or "N.L.A" etc, update the
"discontinued" field to yes/true.
How would i go about this?
Click to expand...

Code the [PartDescription] AfterUpdate event:

Me![Discontinued] = (Me![PartDescription] = "NLA" or
Me![PartDescription] = "N.L.A")
 
Fred, I got ahead of myself with this question.
What I need to do first is find "N.L.A." in the tblParts_Notes field.
Easy to do if I'm only looking for "N.L.A." but the Notes field may contain
text such as: "N.L.A. PCN A88/62" etc as well.
Any ideas on how to find both instances of text?
--
DonK


fredg said:
I need to update a yes/no check box to yes/true if a field contains certain
text.
eg: if a partdescription contains "NLA" or "N.L.A" etc, update the
"discontinued" field to yes/true.
How would i go about this?
Click to expand...

Code the [PartDescription] AfterUpdate event:

Me![Discontinued] = (Me![PartDescription] = "NLA" or
Me![PartDescription] = "N.L.A")
Click to expand...
 
Hi Don,

One way is to use the rgxValidate() function at
http://www.mvps.org/access/modules/mdl0063.htm

An expression like this should do it:

Me!Discontinued = rgxValidate(Me.PartDescription,
".*\bN\.?L\.?A\.?\b.*", True)

That will find "NLA" or "N.L.A." or "N.LA." and so on anywhere in the
field. If the letters have to be at the start of the field, replace the
initial ".*\b" with "^".
 
You can use LIKE as a comparison operator in VBA or in SQL. There are some
differences in the two.

tblParts_Notes Like "*NLA*" Or tblParts_Notes Like "*N?L?A*" Or
tblParts_Notes Like "*NL?A*" Or tblParts_Notes LIKE "*N?LA*"

That would catch most cases where tblParts_Notes contains NLA or the Letters
N, L, and A separated by one character. If you need the string only at the
beginning of notes then remove the asterisk {*) at the beginning of each
comparison string.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

DonK said:
Fred, I got ahead of myself with this question.
What I need to do first is find "N.L.A." in the tblParts_Notes field.
Easy to do if I'm only looking for "N.L.A." but the Notes field may
contain
text such as: "N.L.A. PCN A88/62" etc as well.
Any ideas on how to find both instances of text?
--
DonK


fredg said:
I need to update a yes/no check box to yes/true if a field contains
certain
text.
eg: if a partdescription contains "NLA" or "N.L.A" etc, update the
"discontinued" field to yes/true.
How would i go about this?
Click to expand...

Code the [PartDescription] AfterUpdate event:

Me![Discontinued] = (Me![PartDescription] = "NLA" or
Me![PartDescription] = "N.L.A")
Click to expand...
Click to expand...
 
while I'm here and reading this thread - something similar to ask.

Let's say I've found the text and yes/no field is checked. Is there any way
to lock this field once is checked, so even if the original text is changed
and the condition is no longer met, the yes/no field would still be checked???


Viktor
 
You can use VBA code on the form, but if the user can get to the data in the
table directly there is no way to lock the field.

In the before update event of acontrol on the form you could use something
like

If Me.CheckBox.OldValue = True then
Cancel = True
End If


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top