automated validation correction

[Marcel2586]

Hello all,

Is there a way in vba to automatically correct incomplete data?
When we get new parts we scan the bar-code and within a query i chop it up with this in the field

CertNumber: Trim(Right([PartCertNr];[space]+2))

Example good scan:
20 MAY 2004H149-082-79 A4147011A05

But sometimes it happens that the certificate is missing, wrong, or shipped later. but now if i scan with no certification number i get an #Func! error.

Example of a bad scan:
20 MAY 2004H149-082-79

In my table i have a validation rule like this, that works. At least it gives me a message that the data is incomplete.

Len([BatchInput])>=35

Is there a way to fill in automatically "No certificate at the moment" when the data is smaller than 35 characters.

Thank you in advance.

 

[plog]

Is there a way to fill in automatically "No certificate at the moment" when the data is smaller than 35 characters.

In a query I would do this:

HasCertificate: Iif(Len([PartCertNr])>=35, 1,0)

Then all the ones that resolve to 1 have a cert and those with 0 do not.