clk wrote:
> On Jan 6, 1:04 pm, "Bob Barrows" <reb01...@NOSPAMyahoo.com> wrote:
>>> 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 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' ...

On Jan 9, 10:19*am, clk <c.kur...@comcast.net> wrote:
> 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 supposedto
> > 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.- 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????

On Jan 9, 11:21*am, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
> clk wrote:
> > On Jan 6, 1:04 pm, "Bob Barrows" <reb01...@NOSPAMyahoo.com> wrote:
> >>> 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 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.