Inserting a concatenated string into a text field

[blobb]

Hi. I am attempting to concatenate a uniqueID field from two other numeric
fields in my database. the VBA with SQL code appears to be working -- on the
insert statement the contact string is formated as 000-00 but when i look at
the data in the table something like 0 or -1 will be inserted into the
uniqueID field (instead of 001-01). Am I doing something wrong to insert the
formated & concatenated UniqueID as 000-00 into a text field in a table (with
no masks or formats assigned in the table)?

Thank you for your help in advance!


Dim db As Database
Dim LSQL As String
Dim LCntr As Integer
Dim PregNum As Integer
Dim Contact As String


'Establish connection to current database
Set db = CurrentDb()

LCntr = 1
PregNum = Forms![Contacts Display]![Index Form Subform]!PregNum


'Create SQL to insert item numbers 1 to PregNum into table Pregnancy

Do Until LCntr > PregNum

Contact = Format(Forms![Contacts Display]!ContactID, "000") &
Format(LCntr, "-00")

LSQL = "INSERT INTO Pregnancy (ContactID, PregID, UniqueID)"
LSQL = LSQL & " VALUES ("
LSQL = LSQL & ContactID & ", " & LCntr & ", " & Contact & ")"

'Perform SQL
db.Execute LSQL, dbFailOnError

'Increment counter variable
LCntr = (LCntr + 1)
Loop

 

[Marshall Barton]

Hi. I am attempting to concatenate a uniqueID field from two other numeric
fields in my database. the VBA with SQL code appears to be working -- on the
insert statement the contact string is formated as 000-00 but when i look at
the data in the table something like 0 or -1 will be inserted into the
uniqueID field (instead of 001-01). Am I doing something wrong to insert the
formated & concatenated UniqueID as 000-00 into a text field in a table (with
no masks or formats assigned in the table)?

Thank you for your help in advance!


Dim db As Database
Dim LSQL As String
Dim LCntr As Integer
Dim PregNum As Integer
Dim Contact As String


'Establish connection to current database
Set db = CurrentDb()

LCntr = 1
PregNum = Forms![Contacts Display]![Index Form Subform]!PregNum


'Create SQL to insert item numbers 1 to PregNum into table Pregnancy

Do Until LCntr > PregNum

Contact = Format(Forms![Contacts Display]!ContactID, "000") &
Format(LCntr, "-00")

LSQL = "INSERT INTO Pregnancy (ContactID, PregID, UniqueID)"
LSQL = LSQL & " VALUES ("
LSQL = LSQL & ContactID & ", " & LCntr & ", " & Contact & ")"

'Perform SQL
db.Execute LSQL, dbFailOnError

'Increment counter variable
LCntr = (LCntr + 1)
Loop

Because Contact is a Text field, the value must be enclosed
in quotes:

LSQL = LSQL & ContactID & ", " & LCntr & ", """ & Contact &
""")"

 

[blobb]

THANK YOU!