VBScript & Access issue (Instr Function)

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

VBScript & Access issue (Instr Function)

Logged in as: Guest

arrSession:exec spGetSession 2,2,63870

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> VBScript & Access issue (Instr Function)
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

VBScript & Access issue (Instr Function) - 8/28/2008 1:36:18 AM

vasuki

Posts: 10
Score: 0
Joined: 8/27/2008
Status: offline

Hi All,

I have some 1000 records in a access database (#_GR1@cifgjcmelon.com). What i am trying to acheive is i want to remove the characters before the "@" symbol and copy the remaining characters after the "@" symbol to the next field in the database (cifgjcmelon.com). I have written a query to acheive this. When i run this query within Access database it works fine, but when i include this query in VBscript and run then i am getting the error "test.vbs(6, 112) Microsoft VBScript compilation error: Invalid character"

Can anyone help me in correcting where i have gone wrong. Below is the Access Query and My VBScript Code. Any help will be greatly appreciated

Access Query - Working fine

UPDATE Recipients SET Recipients.RecipDomain = Right([Recipient],Len([Recipient])-InStr([Recipient],"@"))
WHERE (((Recipients.RecipDomain) Is Null));

VBScript

Set conn = CreateObject("ADODB.Connection")
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=z:\Project\test.mdb"
conn.Open strConnect

StrSQL = "UPDATE Recipients SET Recipients.RecipDomain = Right([Recipient],Len([Recipient])-InStr([Recipient],"@"))" & _
"WHERE (((Recipients.RecipDomain) Is Null))"

Set rs = conn.Execute(StrSQL)
rs.MoveNext
Wscript.Echo "Records Updated"

Regards
T.R.Vasuki

Post #: 1

RE: VBScript & Access issue (Instr Function) - 8/28/2008 1:46:55 AM

dm_4ever

Posts: 2669
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline

What if you tried...

StrSQL = "UPDATE Recipients SET Recipients.RecipDomain = Right([Recipient],Len([Recipient])-InStr([Recipient],'@'))" & _
"WHERE (((Recipients.RecipDomain) Is Null))"

OR

StrSQL = "UPDATE Recipients SET Recipients.RecipDomain = Right([Recipient],Len([Recipient])-InStr([Recipient],""@""))" & _
"WHERE (((Recipients.RecipDomain) Is Null))"

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to vasuki)