Inserting a concatenated string into a text field

  • Thread starter Thread starter blobb
  • Start date Start date
B

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
 
blobb said:
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
Click to expand...


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

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