SQL Statement with Inches

[scotthutchings]

I have a Text that often contains the length of an item which can include feet and inches written like 3'6". I need to use select statements and copy/insert these fields using code but I don't know how to handle the Quote symbol. A couple of data samples are:

171A 36"

3070 5-3/4" 18ga. HMF, Face Welded

303AS 1/36", 2/84"

How do I manage this data?

 

[MarkK]

Say a user enters this data in a text box called MyText ...

1'2"3'4"5

You can assign this to a string variable using...

dim sTmp as string
sTmp = Me.MyText

You can assign it to a field in a recordset...

dim rst as dao.recordset
set rst = currentdb.openrecordset("YourTable")
with rst
  .addnew
  !TextField = Me.MyText
  .update
  .close
end with

...or you can assign it to a parameter in a query...

dim qdf as dao.querydef
[COLOR="Green"]'p0 is implicitly interpreted as a parameter...[/COLOR]
set qdf = currentdb.createquerydef("", _
  "INSERT INTO tYourTable ( TextField ) " & _
  "VALUES ( p0 );")
with qdf
  .parameters("p0") = Me.MyText
[COLOR="Green"]  ' or alternatively...[/COLOR]
  .parameters(0) = sTmp
  .execute
  .close
end with

I increasingly like the last method, though it is a little more verbose, it is the fastest, the most reliable, and the most explicit.
Mark

 

[CHKGreen]

I have had the same problem and used the Replace function to deal with it.

Dim strUse As String
strUse = Replace(me.Text1, Chr(34), " inches")

Whenever me.Text1 contains something like: 13' 11"
The value is changed to: 13' 11 inches

You can then use strUse as you wish with worrying about an imbedded " value.

Hope this helps.