Moving Invaid Records (1 Viewer)

[matt2006]

Hi again

I have a table that’s full of records, I want to split it into 2 separate tables (identical structure but they have input masks), The input mask is set to LLL0000 all I want to do is send valid records to one table and invalid ones to the other.

I think the best way would be an update query but it doesn’t check the input mask only the validation (and trying to break the string apart using an expression is impractical and lengthy)

Can anyone think of any way of splitting this table – I’m getting rather desperate now and have been trying for over a week with no success

Any help is greatly appreciated

Thanks
Matt

 

[gemma-the-husky]

how do you identify a valid record.


should it be in the format of a 7 char string with
3 alphas followed by 4 numbers

if so
1. what characters are permitted in the alphas? ie embedded spaces, puctuation characters.
2. is case significant in the alphas?
3. can the numbers be anything eg leading zero's?

 

[matt2006]

how do you identify a valid record.


should it be in the format of a 7 char string with
3 alphas followed by 4 numbers

if so
1. what characters are permitted in the alphas? ie embedded spaces, puctuation characters.
2. is case significant in the alphas?
3. can the numbers be anything eg leading zero's?

yeah 7 char - 3 letters only (upper or lowercase) then 4 numbers
It just skips the input mask when it copies the records, I just need to create a table of all the records that dont have this format (invalid)

Thanks for the quick reply
Matt

 

[gemma-the-husky]

here's a function with a few examples

in your append query put a column with

=validate(whatevercolumn)

and in the criteria put
true to find the passes
false to find the fails

Function validate(instrg As String) As Boolean
Dim ascval As Long
Dim index As Long

'test overall length
If Len(instrg) <> 7 Then
    validate = False
    Exit Function
End If

'test numeric in last 4 chars
If Not IsNumeric(Right(instrg, 4)) Then
    validate = False
    Exit Function
End If

'now test first 3 chars one at a time
For index = 1 To 3
    ascval = Asc(UCase(Mid(instrg, index, 1)))
    If ascval < 65 Or ascval > 90 Then
        validate = False
        Exit Function
    End If
Next index
    
    'if here its ok
validate = True
End Function


Sub tryme()
    MsgBox ("ABC45678  : " & validate("ABC45678")) 'false
    MsgBox ("A3C4567  : " & validate("A3C4567")) 'false
    MsgBox ("A?C4567  : " & validate("A?C4567")) 'false
    MsgBox ("ABC4567  : " & validate("ABC4567")) 'true
End Sub