Make only 4 characters in a Text field unique

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

Guest

I have a 7 character text field in which I would like to disallow duplicates
of the last four characters only.

ie: 98.1000
96.1001
82.1002
98.1003
98.1004

Can this be done with a validation rule in a form? if so, how?; if not,
anyone have any ideas?

This would have been simple if I had split this info into two fields in the
first place but didn't know it would be required at the time.
 
You can't do this at the table level with your current column setup, but you could enfoce this on the form using the
appropriate events. For example, the BeforeUpdate or BeforeInsert event of the form would allow you to examine the data
and query the relevant table to determine if the data is a dupe. If it is, you can cancel the event and alert the user.

Better, IMO, to fix the data. I'm not sure what the data represents, however it appears from your post that this belongs
in two columns. You can build those columns, use a SQL UPDATE query to massage and move the data into those columns,
then rework your app as needed to reflect the new structure. Not a walk in the park, but in my experience sometimes you
gotta bite the bullet.
I have a 7 character text field in which I would like to disallow duplicates
of the last four characters only.

ie: 98.1000
96.1001
82.1002
98.1003
98.1004

Can this be done with a validation rule in a form? if so, how?; if not,
anyone have any ideas?

This would have been simple if I had split this info into two fields in the
first place but didn't know it would be required at the time.
Click to expand...

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com
 
You can use the before Update event of the text box to check if there are
duplicates, if there are give a message and stop the process

Something like
If DCount("*","TableName","Right([FieldName],4) =""" &
Right(Me.[TextBoxName,4) & """") > 0 Then
Msgbox "Record already exist"
Cancel = True 'stop the propcess
End If


Note: need to change the FieldName , TableNAme , TextBoxName to yours
 
You don't need to do anything if this is bound to a table. Just change the
table field Indexed to Yes(no duplicates). The entire string is what is
considered, so if you have 98.1000 and 96.1000, those represent two
different strings.
On the other hand, if you don't want to use 1000 more than once (even though
it is unique in both cases) then you can
put in the after update event of the textbox (which I call txtMyID, and the
field in the table is MyID):
Private Sub txtMyId_AfterUpdate()
If IsNull(txtMyId) = False Then

If DCount("[myID]", "table1", "right([myid],4) = '" &
Right(txtMyId, 4) & "'") > 0 Then
MsgBox "The number suffix you have entered, " &
right([txtMyId],4) & ", has already been used. Enter a different name." & _
vbCrLf , vbCritical, "Error!"
Me![txtMyId].SelStart = 0
Me![txtMyId].ForeColor = vbRed
Else
Me![txtMyId].ForeColor = vbBlack

End If
End If

End Sub

Damon
 
Back
Top