problem creating a query that searches for field created by control source

[Buddy]

I'm having some problems with a database I've made to generate serial
numbers for a factory floor. The forms are pretty much done. The
Serial Number follows this format:

FYYMMDDSLL-UUUU

where

F = Facility
Y = Year
M = Month
D = Day
S = Shift #
L = Line Number (or packing area)
U = Unit Number (auto number that increments)

The user simply selects the current shift and line number and it
generates a serial number through the control source of a a text box
on that same form. There is a print button that prints the serial
number out onto labels. The control source looks like this:

=Format([FacilityID],"0") & Format([Date],"yymmdd") &
Format([ShiftID],"0") & Format([PackingAreaID],"00") & "-" &
Format([UnitNumber],"0000")

It works great for printing the serial number and for displaying in
reports, but I cannot figure out how I can have a user type in a
serial number and pull up all the information on that particular
product (line number, shift number, date, time, facility, etc.). The
serial number itself is not a field in the table. I don't know if I
should or can make it a field in a table. I have a tblSerialNumber
that holds FacilityID, Date, ShiftID, PackingAreaID, UnitNumber, Time,
ItemNumber (not all these fields are needed in the serial number
itself. However, I use this table to generate a serial number via a
text box control source.

Ideally, I would like to be able to type in serial number, and if it
matches one of the rows in tblSerialNumber, generate a table or report
with all the information in this table. Can anyone out there help me?
I would great, greatly appreciate any and all help as I have to have
this finished in 1 week!!

Thanks in advance!!

 

[Steve Schapel]

Buddy,

I can think of two possible approaches to this.
One is to have the place where you want to enter the serial number
actually comprised of 5 textboxes, so that you type in the 5 components
of the serial number separately. Then your query for your report will
have a simple reference to the 5 controls on the form in the criteria of
the 5 fields in the table related to the serial number. This would seem
to be the simplest approach.

The other is to enter the required serial number in one textbox, and
then break it down into its components. So the criteria in your query,
for example of the FacilityID field, would be something like...
Left([Forms]![YourForm]![SerialNumberSought],1)
And the criteria in the PackingAreaID field in your query would be
something like...
Val(Mid([Forms]![YourForm]![SerialNumberSought],9,2))
I think for the date you would need the DateSerial function, e.g.
DateSerial(Mid([Forms]![YourForm]![SerialNumberSought],2,2),Mid([Forms]![YourForm]![SerialNumberSought],4,2),Mid([Forms]![YourForm]![SerialNumberSought],6,2))

--
Steve Schapel, Microsoft Access MVP

I'm having some problems with a database I've made to generate serial
numbers for a factory floor. The forms are pretty much done. The
Serial Number follows this format:

FYYMMDDSLL-UUUU

where

F = Facility
Y = Year
M = Month
D = Day
S = Shift #
L = Line Number (or packing area)
U = Unit Number (auto number that increments)

The user simply selects the current shift and line number and it
generates a serial number through the control source of a a text box
on that same form. There is a print button that prints the serial
number out onto labels. The control source looks like this:

=Format([FacilityID],"0") & Format([Date],"yymmdd") &
Format([ShiftID],"0") & Format([PackingAreaID],"00") & "-" &
Format([UnitNumber],"0000")

It works great for printing the serial number and for displaying in
reports, but I cannot figure out how I can have a user type in a
serial number and pull up all the information on that particular
product (line number, shift number, date, time, facility, etc.). The
serial number itself is not a field in the table. I don't know if I
should or can make it a field in a table. I have a tblSerialNumber
that holds FacilityID, Date, ShiftID, PackingAreaID, UnitNumber, Time,
ItemNumber (not all these fields are needed in the serial number
itself. However, I use this table to generate a serial number via a
text box control source.

Ideally, I would like to be able to type in serial number, and if it
matches one of the rows in tblSerialNumber, generate a table or report
with all the information in this table. Can anyone out there help me?
I would great, greatly appreciate any and all help as I have to have
this finished in 1 week!!

Thanks in advance!!