Access is inserting a space in front of vlaues.

[Jarryd]

Hi,

I have an append query that won't work because it is causing referential
integrity problems. I have since found out that the reason it is causing
those problems is because Access is adding a space in front of the values.
This is the code:

Dim strMobile As String
Dim strNew_Bill As String
Dim db As Database
Dim qryApnd_Bill As String

Set db = CurrentDb
strMobile = Form_frmMobile_Bills.txtMobile
strNew_Bill = Form_frmMobile_Bills.txtNew_Bill
qryApnd_Bill = "INSERT INTO Bills ( Bill, Mobile ) VALUES (' " & strNew_Bill
& " ', ' " & strMobile & " ');"
On Error Resume Next
db.Execute qryApnd_Bill, dbFailOnError
If Err <> 0 Then MsgBox "Error"
End Sub

If I remove the referential integrity from the join it adds the record but
it isn't linked because there is a space in front of ' " & strMobile & " ' -
the value of which is 07775589465, for e.g. There is also a space in front
of the bill number, but that doesn't stop it from being added, as far as I
can tell. Does anyone know why it is adding a space in front of the value?

Please help.

TIA,

Jarryd

 

[Guest]

There is a space because you have put one there. Take out the spaces between
your single and double quotes.

 

[John Vinson]

Hi,

I have an append query that won't work because it is causing referential
integrity problems. I have since found out that the reason it is causing
those problems is because Access is adding a space in front of the values.

Access is doing precisely what you're telling it to do:

qryApnd_Bill = "INSERT INTO Bills ( Bill, Mobile ) VALUES (' " & strNew_Bill
& " ', ' " & strMobile & " ');"

The first Value is delimited by the '; following that delimiter is a
blank, and following that is the concatenated string variable
strNew_Bill. When the string is concatenated, you'll get

INSERT INTO Bills ( Bill, Mobile ) VALUES (' 07775589465 ', '
012421231 ');

Change this to

qryApnd_Bill = "INSERT INTO Bills (Bill, Mobile) VALUES('" &
strNew_Bill & "', '" & strMobile & "');"


John W. Vinson[MVP]