Access autoinserts spaces

[Wiscow]

My code seems to run fine updateing my tables. But when it inserts
the string value for the [Status] field it adds a space before and
after the string, in this case "IN". Not a big deal except that later
when I have a query list all those that are "IN" it doesn't work with
those that are " IN " What is happening?

'building SQL Insert
strSQL = "INSERT INTO ItemHistory ([IDNumber],[Status],[Date]"
If Not IsNull(txtNote) Then
strSQL = strSQL & ",[Discription]"
End If
strSQL = strSQL & ",[Quantity],[Person])"

strSQL = strSQL & "VALUES (" & Me.[Items.IDNumber] & ",'IN',#" &
txtDate & "#"
MsgBox strSQL
If Not IsNull(txtNote) Then
strSQL = strSQL & ",'" & txtNote & "'"
End If
strSQL = strSQL & "," & txtQuanity & ",'" & txtName & "')"

'run SQL
MsgBox strSQL
DoCmd.RunSQL strSQL

 

[storrboy]

Two thoughts...

1) Do the extra spaces exist in the form control? If so, can you
determine if something is putting them there, such as AutoCorrect?
2) IN is part of the sql language, I wonder if something is thinking
this is part of an IN clause and is inserting the spaces. Try a
different word to see if the spaces are added. If not, I might suggest
using a diferent word for the IN status, or try surronding it with [ ].

 

[AccessVandal via AccessMonster.com]

Hi Wiscow,

The “IN†is a reserve word in access. You’ll need to set the concatenate the
string correctly. Try this one. (watch for the quotes)

Dim strIN as String
strIN = “’IN’â€

strSQL = strSQL & "VALUES (" & Me.[Items.IDNumber]

strSQL = strSQL & strIN & “,#" & txtDate & "#"

or try this

strSQL = strSQL & "VALUES (" & Me.[Items.IDNumber]

strSQL = strSQL & “,’IN’†& “,#" & txtDate & "#"

Do a Debug.Print on Me.[Items.IDNumber].See if it is the correct item.
“Debug.Print Me.[Items.IDNumer]â€
If you get null or blank, try

“Me![Items.IDNumber]â€

Wiscow wrote:
My code seems to run fine updateing my tables. But when it inserts
the string value for the [Status] field it adds a space before and
after the string, in this case "IN". Not a big deal except that later
when I have a query list all those that are "IN" it doesn't work with
those that are " IN " What is happening?

 

[Wiscow]

Thank you, My problem was I was totally looking at the wrong SQL
statment.