AutoIncrement Text Field in an Insert Statement

[clk]

Hi. I had the following code working for a database:
Dim strSQL As String
Dim intCount As Integer
Dim db As DAO.Database
Set db = CurrentDb
strSQL = "INSERT INTO qryPartSerialNumber " _
& "(PartID, RevNumber, SerialNumber) " _
& "SELECT " & Me.PartID & ", " & "('" & Me.RevNumber & "')" _
& ", N + " & Me.StartingSerial & " FROM Num " _
& "WHERE N < " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError

The problem is now I need to make the "Serial Number" number field a
text field because we need to keep the leading zeros entered. Any
help would be greatly appreciated. I tried changing the table fields
to text but then I get a "data type mismatch" error.

Not sure how to make this work. Thanks.

 

[clk]

Hi.  I had the following code working for a database:
Dim strSQL As String
Dim intCount As Integer
Dim db As DAO.Database
Set db = CurrentDb
strSQL = "INSERT INTO qryPartSerialNumber " _
   & "(PartID, RevNumber, SerialNumber) " _
   & "SELECT " & Me.PartID & ", " & "('" & Me.RevNumber & "')" _
   & ", N + " & Me.StartingSerial & " FROM Num " _
   & "WHERE N < " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError

The problem is now I need to make the "Serial Number" number field a
text field because we need to keep the leading zeros entered.  Any
help would be greatly appreciated.  I tried changing the table fields
to text but then I get a "data type mismatch" error.

Not sure how to make this work.  Thanks.

More information. It seems to be in the passing of the startingserial
number. I have even tried making it a string thinking that would work
and it still drops the leading zero.

Dim strStarting As String
strStarting = Me.StartingSerial



Any help is greatly appreciated.

 

[Bob Barrows]

Hi. I had the following code working for a database:
Dim strSQL As String
Dim intCount As Integer
Dim db As DAO.Database
Set db = CurrentDb
strSQL = "INSERT INTO qryPartSerialNumber " _
& "(PartID, RevNumber, SerialNumber) " _
& "SELECT " & Me.PartID & ", " & "('" & Me.RevNumber & "')" _
& ", N + " & Me.StartingSerial & " FROM Num " _
& "WHERE N < " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError

The problem is now I need to make the "Serial Number" number field a
text field because we need to keep the leading zeros entered. Any
help would be greatly appreciated. I tried changing the table fields
to text but then I get a "data type mismatch" error.

At what point, when you changed the field's datatype or when you ran this
code?
If you need leading zeroes, then you absolutely need to chane the field's
datatype. Then, in your sql, you need to delimit the value being inserted
into the field. You should also get into the habit of using & for string
concatenation instead of +. Yes, there are sometimes advantages to using +
where nulls are concerned, but maintenance is simpler if you can tell at a
glance if addition or concatenation is intended.

strSQL = "INSERT INTO qryPartSerialNumber " _
& "(PartID, RevNumber, SerialNumber) " _
& "SELECT " & Me.PartID & ", " & "('" & Me.RevNumber & "')" _
& ", N + " & Me.StartingSerial & " FROM Num " _
& "WHERE N < " & Me.[Quantity] & ";"

You cannot debug a sql statement without knowing what it is. Add a statement
to print the statement to the immediate window, preferably before the line
that executes it:
debug.print strSQL

I would like to provide more assitance but you have failed to tell us how
many leading zeroes are required - what is the length of that text field?

 

[clk]

Hi.  I had the following code working for a database:
Dim strSQL As String
Dim intCount As Integer
Dim db As DAO.Database
Set db = CurrentDb
strSQL = "INSERT INTO qryPartSerialNumber " _
   & "(PartID, RevNumber, SerialNumber) " _
   & "SELECT " & Me.PartID & ", " & "('" & Me.RevNumber & "')" _
   & ", N + " & Me.StartingSerial & " FROM Num " _
   & "WHERE N < " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError

The problem is now I need to make the "Serial Number" number field a
text field because we need to keep the leading zeros entered.  Any
help would be greatly appreciated.  I tried changing the table fields
to text but then I get a "data type mismatch" error.

At what point, when you changed the field's datatype or when you ran this
code?
If you need leading zeroes, then you absolutely need to chane the field's
datatype. Then, in your sql, you need to delimit the value being inserted
into the field. You should also get into the habit of using & for string
concatenation instead of +. Yes, there are sometimes advantages to using +
where nulls are concerned, but maintenance is simpler if you can tell at a
glance if addition or concatenation is intended.

strSQL = "INSERT INTO qryPartSerialNumber " _
   & "(PartID, RevNumber, SerialNumber) " _
   & "SELECT " & Me.PartID & ", " & "('" & Me.RevNumber & "')" _
   & ", N + " & Me.StartingSerial & " FROM Num " _
   & "WHERE N < " & Me.[Quantity] & ";"

You cannot debug a sql statement without knowing what it is. Add a statement
to print the statement to the immediate window, preferably before the line
that executes it:
debug.print strSQL

I would like to provide more assitance but you have failed to tell us how
many leading zeroes are required - what is the length of that text field?- Hide quoted text -

- Show quoted text -

Hi...thank you for your reply. I changed my code to match what you
sent. & instead of +. I also added the debug statement. It
processed through without error. The problem is it dropped my leading
zero. I have a message box statement now to see if it is grabbing the
zero. The zero is there in the message box but once inserted into the
table, the zero is dropped. The table field where that item is
inserted is set to a text field.

Here is code:

Dim strSQL As String
Dim intCount As Integer
Dim db As DAO.Database
Dim strStarting As String
strStarting = Me.StartingSerial
MsgBox (strStarting)
Set db = CurrentDb
strSQL = "INSERT INTO qryPartSerialNumber " _
& "(PartID, RevNumber, SerialNumber) " _
& "SELECT " & Me.PartID & ", " & "('" & Me.RevNumber & "')" _
& ", N + " & strStarting & " FROM Num " _
& "WHERE N < " & Me.[Quantity] & ";"
Debug.Print strSQL

 

[clk]

Hi.  I had the following code working for a database:
Dim strSQL As String
Dim intCount As Integer
Dim db As DAO.Database
Set db = CurrentDb
strSQL = "INSERT INTO qryPartSerialNumber " _
   & "(PartID, RevNumber, SerialNumber) " _
   & "SELECT " & Me.PartID & ", " & "('" & Me.RevNumber & "')" _
   & ", N + " & Me.StartingSerial & " FROM Num " _
   & "WHERE N < " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError

The problem is now I need to make the "Serial Number" number field a
text field because we need to keep the leading zeros entered.  Any
help would be greatly appreciated.  I tried changing the table fields
to text but then I get a "data type mismatch" error.

At what point, when you changed the field's datatype or when you ran this
code?
If you need leading zeroes, then you absolutely need to chane the field's
datatype. Then, in your sql, you need to delimit the value being inserted
into the field. You should also get into the habit of using & for string
concatenation instead of +. Yes, there are sometimes advantages to using +
where nulls are concerned, but maintenance is simpler if you can tell at a
glance if addition or concatenation is intended.

strSQL = "INSERT INTO qryPartSerialNumber " _
   & "(PartID, RevNumber, SerialNumber) " _
   & "SELECT " & Me.PartID & ", " & "('" & Me.RevNumber & "')" _
   & ", N + " & Me.StartingSerial & " FROM Num " _
   & "WHERE N < " & Me.[Quantity] & ";"

You cannot debug a sql statement without knowing what it is. Add a statement
to print the statement to the immediate window, preferably before the line
that executes it:
debug.print strSQL

I would like to provide more assitance but you have failed to tell us how
many leading zeroes are required - what is the length of that text field?- Hide quoted text -

- Show quoted text -

Also, the length of text field was not changed. It is set to 50. The
number of leading zeros varies. If they type "001212" in the unbound
field, it should use two. If they type "010104" it will only be one.
etc.

 

[clk]

Hi.  I had the following code working for a database:
Dim strSQL As String
Dim intCount As Integer
Dim db As DAO.Database
Set db = CurrentDb
strSQL = "INSERT INTO qryPartSerialNumber " _
   & "(PartID, RevNumber, SerialNumber) " _
   & "SELECT " & Me.PartID & ", " & "('" & Me.RevNumber & "')" _
   & ", N + " & Me.StartingSerial & " FROM Num " _
   & "WHERE N < " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError
The problem is now I need to make the "Serial Number" number field a
text field because we need to keep the leading zeros entered.  Any
help would be greatly appreciated.  I tried changing the table fields
to text but then I get a "data type mismatch" error.

At what point, when you changed the field's datatype or when you ran this
code?
If you need leading zeroes, then you absolutely need to chane the field's
datatype. Then, in your sql, you need to delimit the value being inserted
into the field. You should also get into the habit of using & for string
concatenation instead of +. Yes, there are sometimes advantages to using +
where nulls are concerned, but maintenance is simpler if you can tell at a
glance if addition or concatenation is intended.

strSQL = "INSERT INTO qryPartSerialNumber " _
   & "(PartID, RevNumber, SerialNumber) " _
   & "SELECT " & Me.PartID & ", " & "('" & Me.RevNumber & "')" _
   & ", N + " & Me.StartingSerial & " FROM Num " _
   & "WHERE N < " & Me.[Quantity] & ";"

You cannot debug a sql statement without knowing what it is. Add a statement
to print the statement to the immediate window, preferably before the line
that executes it:
debug.print strSQL

I would like to provide more assitance but you have failed to tell us how
many leading zeroes are required - what is the length of that text field?- Hide quoted text -

- Show quoted text -

Hi...thank you for your reply.  I changed my code to match what you
sent.  & instead of +.  I also added the debug statement.  It
processed through without error.  The problem is it dropped my leading
zero.  I have a message box statement now to see if it is grabbing the
zero.  The zero is there in the message box but once inserted into the
table, the zero is dropped.  The table field where that item is
inserted is set to a text field.

Here is code:

Dim strSQL As String
Dim intCount As Integer
Dim db As DAO.Database
Dim strStarting As String
strStarting = Me.StartingSerial
MsgBox (strStarting)
Set db = CurrentDb
strSQL = "INSERT INTO qryPartSerialNumber " _
   & "(PartID, RevNumber, SerialNumber) " _
   & "SELECT " & Me.PartID & ", " & "('" & Me.RevNumber & "')" _
   & ", N + " & strStarting & " FROM Num " _
   & "WHERE N < " & Me.[Quantity] & ";"
Debug.Print strSQL- Hide quoted text -

- Show quoted text -

Also, the field is set to a text field. 50 characters. The leading
zeros will vary. It could be "001212" or "010104". Whatever is typed
in the unbound field should be inserted into the field in the table.

 

[Bob Barrows]

Also, the field is set to a text field. 50 characters. The leading
zeros will vary. It could be "001212" or "010104". Whatever is typed
in the unbound field should be inserted into the field in the table.

Could you please show me the sql statement that gets printed into the
Immediate window as a result of the debug.print statement when the code
runs? You need to press ctrl-g to open the window and see it.

 

[clk]

Could you please show me the sql statement that gets printed into the
Immediate window as a result of the debug.print statement when the code
runs? You need to press ctrl-g to open the window and see it.

Here it is:

INSERT INTO qryPartSerialNumber (PartID, RevNumber, SerialNumber)
SELECT 638, ('2'), N + 010101 FROM Num WHERE N < 2;

 

[Bob Barrows]

Here it is:

INSERT INTO qryPartSerialNumber (PartID, RevNumber, SerialNumber)
SELECT 638, ('2'), N + 010101 FROM Num WHERE N < 2;

So, when N is 3, the result of that "N + 010101" expression is supposed to
be ... what?
3010101? 010104? Which one do you intend it to be?


The idea is that you need to be able to paste the sql statement resulting
from your code (the result of debug.print) into the sql view of a query
window and run it without error or modification. If it generates an error,
you have not built it correctly.
In this case, copy only the SELECT potyion of your statement into the window
and test it. What results wre returned? If not correct, what is the correct
result?

 

[clk]

So, when N is 3, the result of that "N + 010101" expression is supposed to
be ... what?
3010101? 010104? Which one do you intend it to be?

The idea is that you need to be able to paste the sql statement resulting
from your code (the result of debug.print) into the sql view of a query
window and run it without error or modification. If it generates an error,
you have not built it correctly.
In this case, copy only the SELECT potyion of your statement into the window
and test it. What results wre returned? If not correct, what is the correct
result?- Hide quoted text -

- Show quoted text -

The problem is the "010101" is being inserted into the table as
"10101". The leading zero got dropped on insert. The field it is
being inserted into is a text field. I hope I answered your question.

Thanks for your help.

 

[Bob Barrows]

The problem is the "010101" is being inserted into the table as
"10101". The leading zero got dropped on insert. The field it is
being inserted into is a text field. I hope I answered your question.

Well ... no, you completely ignored my question.

Again, when that specific select statement runs, what do you want the result
to be? It can't be "010101" because you are either adding or concatenating
the value from the Num table to "010101". Which operation are you intending
to take place? Addition or concatenation?

As it is written, addition is taking place, which means the "010101" is
being converted to a number so that addition can take place. Numbers do not
have leading zeroes and you have not done anything to convert the result
back to a string and reconcatenate the leading zero.

I am going to make the assumption that you do intend addition to take place.
Here is one way to solve the problem (not sure why you put parentheses
around the '2' value (RevNumber). I am going to remove them):

INSERT INTO qryPartSerialNumber (PartID, RevNumber, SerialNumber)
SELECT 638, '2', Right('000000' & Cstr(N + 010101), 6) FROM Num WHERE N < 2;

When you run this select statement only to test this, you should see the
leading zeroes. Since we are now being explicit about casting it to a
string, and the destination field is Text, the leading zero will be retained
when the insert is done.

Now, to make this happen in your code, you have to use the len function to
get the number of characters in StartingSerial and use that number to
generate the string of zeroes and the sql string:

Dim l as integer
dim sZeroes as string
l = len(Me.StartingSerial)
sZeroes = String(l, "0")
strSQL = "INSERT INTO qryPartSerialNumber " _
& "(PartID, RevNumber, SerialNumber) " _
& "SELECT " & Me.PartID & ", " & "'" & Me.RevNumber & "'" _
& ", Right('" & sZeroes & "' & " _
& " CStr(N + " & Me.StartingSerial & "), " & l & ")" _
& " FROM Num " _
& "WHERE N < " & Me.[Quantity] & ";"
debug.print strSQL

If you did intend concatenation, then this would need to be the sql
statement:
INSERT ... SELECT ... , CStr(N) & '010101' ...

 

[clk]

The problem is the "010101" is being inserted into the table as
"10101".  The leading zero got dropped on insert.  The field it is
being inserted into is a text field.  I hope I answered your question.

Thanks for your help.- Hide quoted text -

- Show quoted text -

This is the code now. Added single quotes thinking I needed that to
keep it with leading zeros:

Dim strStart As String
strStart = Me.StartingSerial
Set db = CurrentDb
strSQL = "INSERT INTO qryPartSerialNumber " _
& "(PartID, RevNumber, SerialNumber) " _
& "SELECT " & Me.PartID & ", " & "('" & Me.RevNumber & "')" _
& ", N + '" & strStart & "' FROM Num " _
& "WHERE N < " & Me.[Quantity] & ";"
db.Execute strSQL, dbFailOnError
Debug.Print strSQL


Ctrl +G = SELECT 708, ('6'), N + '060606' FROM Num WHERE N < 3;

Still drops leading zeros????

 

[clk]

The problem is the "010101" is being inserted into the table as
"10101".  The leading zero got dropped on insert.  The field it is
being inserted into is a text field.  I hope I answered your question..

Well ... no, you completely ignored my question.

Again, when that specific select statement runs, what do you want the result
to be? It can't be "010101" because you are either adding or concatenating
the value from the Num table to "010101". Which operation are you intending
to take place? Addition or concatenation?

As it is written, addition is taking place, which means the "010101" is
being converted to a number so that addition can take place. Numbers do not
have leading zeroes and you have not done anything to convert the result
back to a string and reconcatenate the leading zero.

I am going to make the assumption that you do intend addition to take place.
Here is one way to solve the problem (not sure why you put parentheses
around the '2' value (RevNumber). I am going to remove them):

INSERT INTO qryPartSerialNumber (PartID, RevNumber, SerialNumber)
SELECT 638, '2', Right('000000' & Cstr(N + 010101), 6) FROM Num WHERE N <2;

When you run this select statement only to test this, you should see the
leading zeroes. Since we are now being explicit about casting it to a
string, and the destination field is Text, the leading zero will be retained
when the insert is done.

Now, to make this happen in your code, you have to use the len function to
get the number of characters in StartingSerial and use that number to
generate the string of zeroes and the sql string:

Dim l as integer
dim sZeroes as string
l = len(Me.StartingSerial)
sZeroes = String(l, "0")
 strSQL = "INSERT INTO qryPartSerialNumber " _
    & "(PartID, RevNumber, SerialNumber) " _
    & "SELECT " & Me.PartID & ", " & "'" & Me.RevNumber & "'" _
    & ", Right('" & sZeroes & "' & " _
    & " CStr(N + " & Me.StartingSerial & "), " & l & ")" _
    & " FROM Num " _
    & "WHERE N < " & Me.[Quantity] & ";"
debug.print strSQL

If you did intend concatenation, then this would need to be the sql
statement:
INSERT ... SELECT ... , CStr(N) & '010101' ...- Hide quoted text -

- Show quoted text -

You are a life saver!!! Thank you so much for your help! It is
working now.