Update of record only occurs if textfield contains text

I have a repeating region that displays records. A person reviewing the records has an option to either ACCEPT or REJECT an order.  They also have an option to enter a comment in the cmNotes field.

The REJECT portion is working correctly but I am having issues with the ACCEPT portion.  The below code works if records are ACCEPTED and information is entered into the cmNotes field.  But if an order is ACCEPTED, but no comments are entered into the cmNotes textfield, I get an error. The error is below.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ID ='.


I want this code to allow a person to ACCEPT without the need to enter text in the cmNotes field. If the field is either blank or contains text, the record is updated.

If mrec<>"" Then

	
	Set MM_editCmd = Server.CreateObject("ADODB.Command")
        MM_editCmd.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tampaorders100.mdb"

	
	arrMrec=split(mrec,",")	
	for i = 0 to ubound(arrMrec)
		
	        mquery= "update tampaorders set processed='Yes', Status='Accepted', cmNotes='" & _
				Request.Form("cmNotes" & arrMrec(i)) & "', Who='"&Request.Form("Who")& _
				"', ViewDate=now(), ViewDate1=date() where ID = "& arrMrec(i) &";" 
		
    	        MM_editCmd.CommandText = mquery
		
		MM_editCmd.Execute
	next
	
    
	MM_editCmd.ActiveConnection.Close

End If
	

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If

Open in new window

arendt73Asked:
Who is Participating?
 
Daniel WilsonConnect With a Mentor Commented:
I think this code is ASP (VBScript) code, not Access (VBA) code, right?  If so, I think Nz is mistaken.

My mistake was the omission of an ampersand (&)
mquery= "update tampaorders set processed='Yes', Status='Accepted', "
if len(trim(Request.Form("cmNotes" & arrMrec(i)))) > 0 then
    mquery = mquery & " cmNotes='" & Request.Form("cmNotes" & arrMrec(i)) & "', "
end if
mquery = mquery & " Who='"&Request.Form("Who")& _
				"', ViewDate=now(), ViewDate1=date() where ID = "& arrMrec(i) &";"

Open in new window

0
 
Daniel WilsonCommented:
There may be a simpler way ... but I would write an IF statement that would determine whether to include cmNotes in the UPDATE.
mquery= "update tampaorders set processed='Yes', Status='Accepted', "
if len(trim(Request.Form("cmNotes" & arrMrec(i)))) > 0 then
    mquery = mquery & " cmNotes='" Request.Form("cmNotes" & arrMrec(i)) & "', "
end if
mquery = mquery & " Who='"&Request.Form("Who")& _
				"', ViewDate=now(), ViewDate1=date() where ID = "& arrMrec(i) &";"

Open in new window

0
 
GRayLCommented:
Replace:

cmNotes='" & Request.Form("cmNotes" & arrMrec(i)) & "'

with:

cmNotes='" & Nz(Request.Form("cmNotes" & arrMrec(i)),"") & "'
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
arendt73Author Commented:
In attempting to use DanielWilson's suggestion, I get the following error:

Microsoft VBScript compilation  error '800a0401'

Expected end of statement

mquery = mquery & " cmNotes='" Request.Form("cmNotes" & arrMrec(i)) & "', "
----------------------------------------------------^
0
 
arendt73Author Commented:
When using GRayL's suggestion, I get the following error:

Microsoft VBScript runtime  error '800a000d'

Type mismatch: 'Nz'
0
 
Andrew_WebsterCommented:
NZ is a VBA function that replaces a Null with a specified value.  i.e. Nz(myVariable, "") would replace a Null value for myVariable with an empty string.

Which brings me to a workaround: concatenate an empty string to cmNotes so that there is a value to enter come what may.

e.g.:
mquery= "update tampaorders set processed='Yes', Status='Accepted', cmNotes='" & _
                                Request.Form("cmNotes" & arrMrec(i)) & "' & "", Who='"&Request.Form("Who")& _
                                "', ViewDate=now(), ViewDate1=date() where ID = "& arrMrec(i) &";" 

Open in new window

Note the & "".
0
 
arendt73Author Commented:
Attempted Andrew Wbster's suggestion and received the follwoing error message after attempting the update of several records:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query expression ''' & ", Who='RFA', ViewDate=now(), ViewDate1=date() where ID = 369365;'.

/orders/cm_review.asp, line 106

Line 106 in the code is between

MM_editCmd.CommandText = mquery
            
MM_editCmd.Execute
0
 
GRayLCommented:
I suggest you develop the query in the Query Designer and when you get what you want, save it as a named query and execute the query from code.  
0
 
GRayLCommented:
That is a heck of a lot easier than trying to do what you are doing.  Regardless,  you should always develop a query in the Designer, paste the SQL in your code, add the quotes and line continuations and go from there. With that approach, you always have a baseline which you know works.  
0
 
arendt73Author Commented:
Thanks for the suggestion GRayL.  I wish I knew how to use the Query Designer in Access.  Everytime I attempt to use it, it never works out.  If you have any directions or a site that has specific directions on using the query designer, I'd greatly appreciate the information.

Thank you.
0
 
GRayLCommented:
Then what do you use to design a query in Access?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.