Query

[ankit]

HAVE 670000 RECORD SOME RECORD ARE WRONG I HAVE TO SCRUB THESE NUMBER BEFORE I POST QUESTION BUT I DOESN'T WORK MY TABLE FIELD
NAME IS
INPECTION NUMBER
DATE
REMARK
FACILITY
INPECTION NUMBER EXAMPLE IS
W14-0124-1
S28-0124-1R01
S30-1045-2R01
THESE INSPECTION ARE CORRECT
BUT SOME OF WRONG NUMBER LIKE THESE
W14-0125-01
S24-1024-02
ND-0345-03
I WANT TO REMOVE 0 (LAST SECOND NUMBER).
PLEASE HELP THESE RECTANGULAR BOX IS A PICTURE IF POSSIBLE TO OPEN THERE DETAIL OF RECORD.

 

[Galaxiom]

You will need a custom funciton.

In your Update query use:
KillZero([fieldname])

Create a Module and paste this function into it.

Function KillZero(WholeCode As String) As String
 
Dim SubCode() As String
 
     SubCode = Split(WholeCode, "-") 
     If IsNumeric(SubCode(2)) Then: SubCode(2) = Val(SubCode(2)) 
     KillZero = SubCode(0) & "-" & SubCode(1) & "-" & SubCode(2)
 
End Function

This will remove leading zeros from numeric values in the third subcode.
Hope it is what you need.

 

[ankit]

Thank you very much

 

[ankit]

Lets hope it will work. Thank you very much.

 

[ankit]

I try this code but it doesn't work
my table name is tfs steam data proccess and fild name is inspection. If u have other way please reply
thx

 

[Galaxiom]

If you need more guidance just ask. I don't know how much experience you have. It is a very simple custom function. Once you see SubCode is an array the rest is quite obvious

I promise we can make it work for you so long as the specification of the InspectionNumber field is as we have understood. The third subcode will change only if it is a number with no letters.

If you are needing to use it in future you might like to call the function Inspection(). KillZero() was somewhat whimsical.

 

[Galaxiom]

I tried the function I wrote and it fine worked for me.
Must be some problem with how you have set it up.

Are you getting an error message when you run the query?

 

[ankit]

I am just new in access i apply this code in my module but it doesn't work how to apply this code. Please advise

 

[Galaxiom]

Make sure code is allowed to be run in your database.
Sometimes it is set to not even warn that code has been disabled but usaully a security warning will appear. The setting depends on the version of Access you are using.

In the VBA Editor open Project Explorer. (View menu or Ctrl + R)
Right click on Modules and Select Insert > Module

Paste the code after the line that is already there.
Save. Enter any name for the module. The default Module1 is fine.

In the Update To: line of your Update query type:
KillZero([Inspection]