DLookup without the initial characters

[DBL]

Hiya. I run duplicate check on a form to make sure the file name doesn't match one that's previously been entered. Currently I have:

If Me.NewRecord Then
If Not IsNull(DLookup("File", "tblFileProcessing", "[File]=""" _
& Me![File] & """" & " and [FileClientID] = " & Me![FileClientID])) Then
DoCmd.OpenForm "frmDuplicateFiles", acNormal, , , , acDialog
End If
End If


However we've recently changed the way we work and have now added a unique ID number prefixed to the file name: 567_File_Name_1 How do I run the DLook up but parse off the digits prior to the first underscore and only check on the remainder of the file name?

Any help gratefully received.

 

[RainLover]

Why are you not using Two separate Fields.

Also you could Index that field so that it can never be duplicated.

 

[DBL]

The job number is part of the name of a file so can't be split into two different fields.

 

[RainLover]

There is a simple way to join them back again.

MyJoin =(Field1 and Field2)

You can displayed them joined and no one will know. This allows you to sort on either Field.

 

[CJ_London]

Since you are looking at the table, not the actual file I'm not sure which 'file' you want to strip off the initial numbers, but the basis would be

mid(file,instr(file,"_")+1)

 

[DBL]

That's great, I'll give it a try. The file is an externally created document that is prenamed at source that we log in our database and we need to remove the ID number from the document name purely so we can do a dlookup to check that the document hasn't already been send to us, with a different job Id and already logged in the system, which can happen and results in the document being processed twice which is what we're trying to avoid.