Question about duplicates and Input Masks

[rbinder]

Greetings

I was wondering how I go about this?

I have a table that holds vessls and barrels in a winery. When I enter data via a form I have a lookup to showme the location of a vessel or barrel. The vessels are static and have a two letter location ie WT or RT etc. The barrels, once filled are put in different locations in a warehouse. Each location has a code ie WW011121 etc.

I have a range of codes in the form as follows;

Private Sub Location_Enter()
If IsNothing(Me.Location) Then 'means it is probably a barrel
'and needs a warehouse location
Me.Location.InputMask = ">LL00000"
End If
End Sub

Private Sub Location_Exit(Cancel As Integer)
If IsNothing(Me.Location) Then 'tried to leave without a location
MsgBox "Location Required", vbCritical, gstrAppTitle
Cancel = True
End If
End Sub

What I wish to write is some code to find a ducplicate location in the warehouse not in the static locations.

What I have written thus far is;

Private Sub Location_BeforeUpdate(Cancel As Integer)
If Me.Location.InputMask = ">LL00000" Then

then the air code would be to find duplicates from there.

Would it be best to compare string length (clues needed) as the static locatons on have two letter where as the barrel locations will have two letters and five numbers. Or is there a trick in dcount to look at bits of the contents of a field? ie ignore fields with on two letters and do a dcount in fields with say two letters and five numbers?

I do not have any experience writing code involvine string lengths.

Thanks in advance

~rbinder

 

[reclusivemonkey]

Firstly, I think all you need is

Left([Location],2)

To get the first two characters of the Location field. In answer to your other questions,

Len([Location])

Will give you the length of a field value. You might also want to look at Left and Mid in case you need them, all in the Access help. That should get you started as I don't completely understand your system.