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.

On Jan 3, 9:22*pm, clk <c.kur...@comcast.net> wrote:
> 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.

clk wrote:
> 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?

On Jan 4, 9:53*am, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
> clk wrote:
> > 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

On Jan 4, 9:53*am, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
> clk wrote:
> > 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.

On Jan 5, 2:16*pm, clk <c.kur...@comcast.net> wrote:
> On Jan 4, 9:53*am, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
>
>
>
>
>
> > clk wrote:
> > > 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.

clk wrote:
>>
>> 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.
>>
>
> 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.

On Jan 5, 3:28*pm, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
> clk wrote:
>
> >> 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.
>
> > 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.

Here it is:

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

clk wrote:
> On Jan 5, 3:28 pm, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
>> clk wrote:
>>
>>>> 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.
>>
>>> 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.
>
> 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?

On Jan 6, 1:04*pm, "Bob Barrows" <reb01...@NOSPAMyahoo.com> wrote:
> clk wrote:
> > On Jan 5, 3:28 pm, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
> >> clk wrote:
>
> >>>> 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.
>
> >>> 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.
>
> > 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?- 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.