Update text to table that has Inch mark ie 3/4"

[mcaliebe]

SO I have a working update statement to write to my table. some of my text fields are measurements and have the Inch " identifier. Access doesn't like this.... how do I prevent the error and have access update the data to the table?

[ssanfu]

I have a couple of functions to deal with single and double quotes:

Function ConvertQuotesSingle(InputVal)
   ConvertQuotesSingle = Replace(InputVal, "'", "''")
End Function


Function ConvertQuotesDouble(InputVal)
   ConvertQuotesDouble = Replace(InputVal, """", """""")
End Function

So lets say I'm building a SQL string to insert a new record with a street name of "O'Malley Road", where vaddr1 is the variable for the address.
The partial SQL string looks like

       sSQLVal = sSQLVal & "','" & ConvertQuotesSingle(vaddr1)

The variable "vaddr1" value in the SQL string would be "O''Malley" (two single quotes)

You would use the "ConvertQuotesDouble" function.....

Also see
https://bytes.com/topic/access/answe...-inside-string

[mcaliebe]

Below is my update statement. Can you give me an example of how I would insert the double quote function into my string?
My ConSize fields are the trouble as I will use 3/4" as an input.

stSQL = "UPDATE tblItem " & _
        "SET tblItem.ItemNbr=""" & Me.txtItemNbr & """, tblItem.BrennanItemNbr=""" & Me.txtBrennanItemNbr & """, tblItem.Customer=""" & Me.txtCustomer & """, " & _
        "tblItem.Description=""" & Me.txtDesc & """, tblItem.Configuration=""" & Me.txtConfiguration & """, tblItem.Print=""" & Me.txtPrint & """, " & _
        "tblItem.ConType1=""" & Me.cmbConType1 & """, tblItem.ConType2=""" & Me.cmbConType2 & """, tblItem.ConType3=""" & Me.cmbConType3 & """, " & _
        "tblItem.ConType4=""" & Me.cmbConType4 & """, tblItem.ConType5=""" & Me.cmbConType5 & """, tblItem.ConType6=""" & Me.cmbConType6 & """, " & _
        "tblItem.ConSize1=""" & Me.txtConSize1 & """, tblItem.ConSize2=""" & Me.txtConSize2 & """, tblItem.ConSize3=""" & Me.txtConSize3 & """, " & _
        "tblItem.ConSize4=""" & Me.txtConSize4 & """, tblItem.ConSize5=""" & Me.txtConSize5 & """, tblItem.ConSize6=""" & Me.txtConSize6 & """ " & _
        "WHERE tblItem.ID =" & Me.txtID

[June7]

Multiple similar name fields with the same kind of data indicates a non-normalized data structure.

You can call the custom function:

ConvertQuotesDouble(Me.txtConSize1)

Or directly use the Replace() function:

Replace(Me.txtConSize1, """", """""")

[mcaliebe]

Multiple similar name fields with the same kind of data indicates a non-normalized data structure.

I agree. I would rather populate a separate table with simply AutoID, ItemNbr, PortNbr, ConType, and ConSize.

Speaking from my experience, I lose data normalization due to my inexperience in writing the code that will do what needs to be done.

In this instance, I think I would be better to check if data exists in each field, and if so write the ConType and ConSize to the table along with the associated port number. I just have to figure out the code. And, I have to figure out how to check if an entry already exists in the table...updating data vs inserting new data from the same form.

I think this is how many of the at-home novices' get bogged down in their progress....and ultimately revert back to what they can do as opposed to what should be done.

[ssanfu]

Looking at your SQL, if your tables were normalized, you could use a main form/ sub form arraignment.


And after seeing your SQL, you might try the following to see if it works:

stSQL = "UPDATE tblItem" 
stSQL = stSQL &  "SET tblItem.ItemNbr = '" & Me.txtItemNbr & "', tblItem.BrennanItemNbr = '" & Me.txtBrennanItemNbr & "'"
stSQL = stSQL &  ", tblItem.Customer = '" & Me.txtCustomer & "', tblItem.Description = '" & Me.txtDesc & "'"
stSQL = stSQL &  ", tblItem.Configuration = '" & Me.txtConfiguration & "', tblItem.Print = '" & Me.txtPrint & "'"
stSQL = stSQL &  ", tblItem.ConType1 = '" & Me.cmbConType1 & "', tblItem.ConType2 = '" & Me.cmbConType2 & "'"
stSQL = stSQL &  ", tblItem.ConType3 = '" & Me.cmbConType3 & "', tblItem.ConType4 = '" & Me.cmbConType4 & "'"
stSQL = stSQL &  ", tblItem.ConType5 = '" & Me.cmbConType5 & "', tblItem.ConType6 = '" & Me.cmbConType6 & "'
stSQL = stSQL &  ", tblItem.ConSize1 = '" & Me.txtConSize1 & "', tblItem.ConSize2 = '" & Me.txtConSize2 & "'"
stSQL = stSQL &  ", tblItem.ConSize3 = '" & Me.txtConSize3 & "',  tblItem.ConSize4 = '" & Me.txtConSize4 & "'"
stSQL = stSQL &  ", tblItem.ConSize5 = '" & Me.txtConSize5 & "', tblItem.ConSize6 = '" & Me.txtConSize6 & "'"
stSQL = stSQL &  " WHERE tblItem.ID = " & Me.txtID
Debug.Print stSQL

Expanded, it looks like

"SET tblItem.ItemNbr = ' " & Me.txtItemNbr & " ', tblItem.BrennanItemNbr = ' "

[mcaliebe]

Thank you for the suggestion. I believe that changing over my structure in this case to single apostrophes as opposed to double quotes would eliminate the issue with my use of a Quote " in my string data.

[June7]

It should. Could there be any apostrophes in your data, as foot measure symbol or in names or contractions (O'Malley, doesn't)?