Deleting the first character of a string when importing (1 Viewer)

[troncarver]

Hello ALL,

I need some VB code that will delete the first character of an 11 digit number string when I am importing this field into an access table??? THanks alot my peoples...

 

[Matt Greatorex]

Dim li_String as Integer

li_String = value

li_String = Right(li_String, 10)

 

[ghudson]

After you import the data "as-is", you could use an update query to update the values by stripping of the first value of the 11 character string.

Post your db and I will make the query for you.

 

[troncarver]

thank you

the input mask is for writing data to a table i am assuming? For my db, the user is entering the number on a .asp form and the vb script is simply finding that value in the ndc field and returning it to a display on the asp page.

So with this update query i can essentially import this field as is (12345-67-8901) and update it to populate the field values with (2345678901). that way when the user types in 2345678901 the query will find actaully find that entry.

Right now I have a batch file that that calls the macro mcrRun to open the db up, delete the old data, import the new text file and close the db. So im assuming we would have to execute that query right after the transfertext in that macro?

I really really appreciate you taking the time to help me out. That is good karma my friend

Terron

 

[ghudson]

I have attached my fix. The qDisplayExample query will show you what the stripped value will look like using the Right() function Right([ndc],12) You have an 11 digit number but the hyphens add two more for a total of 13 characters.

The qStrip1stCharacter query will actually replace the ndc values in the drug table with the new values [minus] the first character in the original string. You should run it right after the data is imported.

I also added my custom VerifyImportErrorTables() function. You should run it after your import function for it will search and delete any import error tables for which you have a bunch.

Good luck!

 

[troncarver]

Thank you so much

ghudson,

That is awesome, thank you so much. If i could just have your quick help i one more thing that would be superb. Now what would the syntax be for an update query, that after this query runs would then delete the 5th and 10th characters which would be the hyphens?????

Hey thanks alot ghudson you saved me alot of valuable time!!

 

[checoturco]

That is awesome, thank you so much. If i could just have your quick help i one more thing that would be superb. Now what would the syntax be for an update query, that after this query runs would then delete the 5th and 10th characters which would be the hyphens?????


hi troncarver
i buid a form with a button with this code and it works

Private Sub Command0_Click()
Dim rs As Recordset
Dim db As Database
Dim strnewstring As String
Dim strfind As String
Dim strreplace As String
Dim teste As String

Set db = CurrentDb

strfind = "-"
strreplace = ""

DoCmd.SetWarnings False
Set rs = New ADODB.Recordset
rs.Open "select ndc from drug;", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then
Do Until rs.EOF
strnewstring = Replace(rs("ndc"), strfind, strreplace)
' Debug.Print strnewstring
DoCmd.RunSQL " update drug set ndc='" & strnewstring & "' where ndc like '" & rs("ndc") & "';"
rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing

be carefull because you have a blank field on ndc column,line 984
ndc Name QOH Actual
BRUSH TEETH 815 ,

if you could have this, put a where clause in the openrecordset query

checoturco

 

[ghudson]

Since I think that you will be more comfortable with SQL I have created a new query "qRemoveHyphens" that uses the Replace() function to remove the hyphens. The criteria Like *-* will ensure only the records with a hyphen will be affected. Enjoy!

Searching the forum will help you find a lot of good stuff on how to help you with your Access programming questions.

 

[troncarver]

Replace function undefined

hey ghudson, when i run that query it is displaying that the Replace() function is undefined? I am running access 2000, has that function net bet defined yet in 2000?

 

[ghudson]

The Replace() Function is new to Acess 2003 [maybe 2002]. There is a custom Replace function floating around here @ Find and Replace Programatically

So the Update criteria of your query would then be fReplace([FieldName],"-","")

Here is another from MSDN...
ACC: How to Replace a String with Another String

HTH

 

[troncarver]

foiled

ghudson,

I entered the function freplace into a module and then called it in a query but it will not open the query saying there is an ambigous name. What am I doing wrong?????

 

[troncarver]

foiled

ghudson,

I entered the function freplace into a module and then called it in a query but it will not open the query saying there is an ambigous name. What am I doing wrong?????

 

[troncarver]

alll GOOD in the HOOOD

program is done....thank you so much ghudson for your help....IT is awesome that there is a comprehensive programming community on the web that is willing to lend resources and time to complete strangers. Your services have not gone unnoticed and i again thank you very much !!!

 

[ghudson]

Your welcome!