problem looking up 6 digit serial when coding set up for 5.

  • Thread starter Thread starter kateoc
  • Start date Start date
K

kateoc

I have a serial number which I enter on a form and it tells me 'whose
number it is'. The problem is it was set up for five digit serial
numbers and now I want to have six digits, and it won't look at the
first figure in the numbers but only five digits from the right.
What do I change in the code to get it to look at the whole six
digits.
Any ideas, thanks
Kate


Sub Issues(t)
Dim db As Database, r As Recordset, tag As String
PI = Null: RI = Null: QI = Null: PACI = Null
Set db = CurrentDb()
tag = "O" & Right(Format(100000 + t), 5)
Set r = db.OpenRecordset("SELECT [Sname] & ', ' & [Gname] AS Grower
FROM Tags INNER JOIN Grower ON Tags.GrowerID = Grower.GrowerID WHERE
Tags.TagNumber = '" & tag & "'")
If r.RecordCount > 0 Then QI = r!Grower
 
***Warning: Wild Guess Ahead.***
tag = "O" & Right(Format(100000 + t), 6) 'Change from 5 to 6

I have absolutely no idea what your code does, so try this. I don't expect
it to work completely, but maybe it will get you started. I would assume
that there is a table that has the serial number as a text of 5, that may
need changed.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Steve,

I have one slightly different guess
tag = "O" & Right(Format(1000000 + t), 6)

Or Perhaps
tag = "O" & Right("000000" & t, 6)

And WHY in the WORLD someone would have the letter O followed immediately by one
or more zeroes is beyond me. As a human I would very probably miss the fact
that the first character was the letter O and not a number character of zero.

[MVP] S. Clark said:
***Warning: Wild Guess Ahead.***
tag = "O" & Right(Format(100000 + t), 6) 'Change from 5 to 6

I have absolutely no idea what your code does, so try this. I don't expect
it to work completely, but maybe it will get you started. I would assume
that there is a table that has the serial number as a text of 5, that may
need changed.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

kateoc said:
I have a serial number which I enter on a form and it tells me 'whose
number it is'. The problem is it was set up for five digit serial
numbers and now I want to have six digits, and it won't look at the
first figure in the numbers but only five digits from the right.
What do I change in the code to get it to look at the whole six
digits.
Any ideas, thanks
Kate


Sub Issues(t)
Dim db As Database, r As Recordset, tag As String
PI = Null: RI = Null: QI = Null: PACI = Null
Set db = CurrentDb()
tag = "O" & Right(Format(100000 + t), 5)
Set r = db.OpenRecordset("SELECT [Sname] & ', ' & [Gname] AS Grower
FROM Tags INNER JOIN Grower ON Tags.GrowerID = Grower.GrowerID WHERE
Tags.TagNumber = '" & tag & "'")
If r.RecordCount > 0 Then QI = r!Grower
Click to expand...
Click to expand...
 
John,

THANK YOU!!!!!!!!!
I owe you a beer.
the format Right(Format"000000" & t,6)
works beautifully.
Regards,

Kate
 
Back
Top