Memo Field Issue? Please help.

[hotflash]

Hi All,

There is a field in my MS Access database called WorkDescription and it is set to MEMO.

Required: No
Allow Zero Length: Yes
Indexed: No
Unicode Compression: Yes

Sometimes if you can and paste a large updates to the Work Description update box, it gives me an error

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression "and whatever the update is ....."

Can someone please help? Thanks.

[shweta123]

Hi,

I think you are getting this error as your sql string is containing quotes or other special chracters. You can use Replace function for this.

e.g. Replace(fieldna me,"'","")

Hi All,

There is a field in my MS Access database called WorkDescription and it is set to MEMO.

Required: No
Allow Zero Length: Yes
Indexed: No
Unicode Compression: Yes

Sometimes if you can and paste a large updates to the Work Description update box, it gives me an error

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression "and whatever the update is ....."

Can someone please help? Thanks.

[hotflash]

Hi shweta123,

Thanks for your help. I have never used the replace command before. Below is the statement that I used to update. What would it look like with the REPLACE statement?

strSQL = "UPDATE TableProjects SET "
strSQL = strSQL & " WorkDescription = '"& strWorkDescript ion &"', "

Conn.Execute(st rSQL)

There are other fields that need to update as well. If you change to REPLACE, is it going to affect the other fields? Thanks for your help again.

[CroCrew]

Like this:

strSQL = "UPDATE TableProjects SET "
strSQL = strSQL & " WorkDescription = '"& Replace(strWork Description,"'" ,"") &"', "

[hotflash]

Hi CroCrew,

Thanks for your outstanding support. I put your command in but got the following error:

Microsoft JET Database Engine error '80040e14'
Syntax error in UPDATE statement.

Please help. Thanks.

[CroCrew]

Sorry try this:

strSQL = "UPDATE TableProjects SET WorkDescription = '" & replace(strWork Description,"'" ,"''") & "'"

[hotflash]

Hi CrowCrew,

You are TRULY AN ASP GURU. Thanks for your OUTSTANDING SUPPORT.

[CroCrew]

All the thanks should go to shweta123 he/she pointed out using the replace function.

Good work shweta123.

[hotflash]

Hi CroCrew and All,

Wow ... Other issue pops up after I used your statement. There are other fields such as strSQL = strSQL & " DeviceName = '"& strDeviceName &"', "
strSQL = strSQL & " Vendor = '"& strVendor &"', " that need to update as well. They STOPPED working when I used the new statements you provided.

Is there a way to define the Replace statement outside of the Query? Please advise. Thanks.

[CroCrew]

Post your entire code and we will have a look at it and fix it all for you. Don’t forget to use the code tags before and after your code snip so we can see line numbers.

Waiting…

[hotflash]

Hi CroCrew,

I have never used the code tag on this forum before and hope below is what you want to see. Sorry if it does not show up correctly. Thanks.

html

strSQL = "UPDATE PTSProjects SET "

strSQL = strSQL & " DeviceName = '"& strDeviceName &"', "
strSQL = strSQL & " Vendor = '"& strVendor &"', "
strSQL = strSQL & " SiteCode = '"& strSiteCode &"', "
strSQL = strSQL & " Region = '"& strRegion &"', "
strSQL = strSQL & " HardwareIssue = '"& strHardwareIssu e &"', "
strSQL = strSQL & " WorkDescription = '"& strWorkDescript ion &"', "

strSQL = strSQL & " WHERE ProjectID = " & ProjectID

Conn.Execute(st rSQL)

Conn.Close
Set Conn = Nothing

[CroCrew]

here ya go...

[code=asp]
strSQL = "UPDATE PTSProjects SET "
strSQL = strSQL & "DeviceName = '"& replace(strDevi ceName,"'","''" ) &"', "
strSQL = strSQL & "Vendor = '"& replace(strVend or,"'","''") &"', "
strSQL = strSQL & "SiteCode = '"& replace(strSite Code,"'","''") &"', "
strSQL = strSQL & "Region = '"& replace(strRegi on,"'","''") &"', "
strSQL = strSQL & "HardwareIs sue = '"& replace(strHard wareIssue,"'"," ''") &"', "
strSQL = strSQL & "WorkDescriptio n = '"& replace(strWork Description,"'" ,"''") &"' "
strSQL = strSQL & "WHERE ProjectID = " & ProjectID

Conn.Execute(st rSQL)

Conn.Close
Set Conn = Nothing
[/code]

[hotflash]

Hi CroCrew,

Don't know what WORDS that I can use for you but to make it short, you are REALLY AN ASP GURU.

By the way, I am in the middle of playing with a Case statement. Got stuck and wonder if you can help me out.

I can send you the entire HTML file and the ASP code for you to look at if you don't mind. Should be easy to fix for an EXPERT like you. Thanks.