Query Tools

[vaniKanabathy]

No body can help me????????????? ??plzzzzzzzzzzz z

[debasisdas]

try this

[code=vb]conn.BeginTrans
conn.Execute "UPDATE Alternator SET EversparkNo1 = " & REPLACE(Everspa rkNo1, " - ", "")
conn.CommitTran s
[/code]

[vaniKanabathy]

conn.BeginTrans
conn.Execute "UPDATE Alternator SET EversparkNo1 = " & Replace(EversparkNo1, " - ", "")
conn.CommitTran s

EversparkNo1 that i bold was not define eror....is it a variable? EversparkNo1 is a field in table. How to solve it. Plzzzzzzzz.....

[debasisdas]

if that is a field then what is the meaning of your update statment ?

[vaniKanabathy]

i have one field which is Eversparkno1 where all its data got ' - ' now i want to remove the ' - ' to ' ' nothing, that means if 101-001 i want to change to 101001. so i want to do it by query, this replace successful if run directly in access but..now im doing in vb as a tools for query.

[debasisdas]

try to use this

[code=vb]
conn.BeginTrans
conn.Execute "UPDATE Alternator SET EversparkNo1 = select Replace(Everspa rkNo1, " - ", "") from Alternator
conn.CommitTran s
[/code]

NOTE :-- Check for the quotes ' and "

[vaniKanabathy]

Type dismatch error occur....plssss ssss help me...next week i must present to my boss....

db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
db_file = db_file & "Everspark2.mdb "

' Open a connection.
Set conn = New ADODB.Connectio n
conn.Connection String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
conn.Open

conn.BeginTrans
conn.Execute "UPDATE Alternator SET EversparkNo1 = SELECT REPLACE(Everspa rkNo1, " - ", "") FROM Alternator"
conn.CommitTran s

[vaniKanabathy]

Anyone can help me?????? Plssssssss

[debasisdas]

Type dismatch error occur....

which line causes the error ?

[vaniKanabathy]

conn.Execute "UPDATE Alternator SET EversparkNo1 = SELECT REPLACE(Everspa rkNo1, " - ", "") FROM Alternator"

this line got error

[debasisdas]

run the query directly in database and check if that works .

[vaniKanabathy]

No, the query shows error. This query down here is success.....

UPDATE Alternator SET EversparkNo1 = REPLACE(Everspa rkNo1, " - ", " ")

[vaniKanabathy]

why no one response to my question? plz help me ......i try a lot of way..but all error....

[QVeen72]

Hi,

Try This :

[code=vb]
conn.Execute "UPDATE Alternator SET EversparkNo1 = REPLACE(Everspa rkNo1, ' -', '') "
[/code]


Regards
Veena

[vaniKanabathy]

I had try..but Undefine function "REPLACE" in expression error occur. Please Help me.