Inserting a concatenated string into a text field


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
 
Ad

Advertisements

M

Marshall Barton

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


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

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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top