SQL Question...

[CtrlAltDel]

I am appending data to a table. One of the columns in the database is a numeric field, and some data coming in is the letter "x" which messes things up, how can i convert the "x" to "999" on the way in?

[BruceG]

Can you supply more details? Where is the input data coming from? Can you show some of your code?

[*Vince]

Hi,

you should be more concerned about how to stop the x from the input than how to patch this case...

However (even if I'm not pro-patch) you could try a trigger on INSERT of SQL server that is, if your database is SQL SERVER driven ! This trigger could select from the "Inserted" table of the database and test for numeric values. If not numeric then update the "Inserted" table with the numeric value you want...

E-mail me for details at [email protected] if needed...

[CtrlAltDel]

ok the data is coming from a text delimited file that is exported off an AS400, and is a linked table in the database. Here is what i do to append all the items that dont have 'x' in the field

sSQL = "INSERT INTO Sales (Customer_No, Customer_Name, Sales_1, Sales_2, Sales_3, Sales_4, Sales_5, Sales_6, Sales_7) " & _
"SELECT Mmarptcsv.Field2, Mmarptcsv.Field3, Mmarptcsv.Field4, Mmarptcsv.Field5, Mmarptcsv.Field6, Mmarptcsv.Field7, Mmarptcsv.Field8, Mmarptcsv.Field9, Mmarptcsv.Field10 " & _
"From Mmarptcsv " & _
"WHERE FIELD1 <> 'x'"
dbSalesman.Execute sSQL

now how can i convert the x to 999 and then append those records.

[JHausmann]

You could select the data into a temporary table, then run SQL updates and finally append the records from the temporary table.

select * into temptable from Mmarptcsv
update temptable set field4='999' where field4='x'
[...]
update temptable set field10='999' where field10='x'
insert into Sales(...) select [...] from temptable

[CtrlAltDel]

thx, i will try that tonight, and see how it werks, looks good though.

thx

[JasonGS]

Im not sure if this is applicable, but if im reading from a field where the user should have entered a number, ill often force it to a Integer or Numeric Value with Int(X) or Val(X), example below:

' With ADO
adoRset.Update "id", Int(txtID.Text)

' SQL too
adoConn.Execute "UPDATE mytable SET id = " & Int(txtID.Text) & " WHERE id = 0"

[Chris]

sSQL = "INSERT INTO Sales (Customer_No, Customer_Name, Sales_1, Sales_2, Sales_3, Sales_4, Sales_5, Sales_6, Sales_7) " & _
"SELECT Mmarptcsv.Field2, Mmarptcsv.Field3, Mmarptcsv.Field4, Mmarptcsv.Field5, Mmarptcsv.Field6, Mmarptcsv.Field7, Mmarptcsv.Field8, Mmarptcsv.Field9, Mmarptcsv.Field10 " & _
"From Mmarptcsv " & _
"WHERE Mmarptcsv.FIELD1 <> 'x'"
dbSalesman.Execute sSQL

Hope this is working.