Between Expression searching between alpha / #'s - Using Access Form

Hello,

My name is April and my attempt in Access is to create a form, also
with a command button which will search for information and put this
information onto a label or box on the form.

SEE EXAMPLE BELOW:

OBJECTIVE:
Access will need to search between column 1 / 2 below (after user
inputs drawing number in text box and hit command (search) button and
bring back results of column 3.

(column 1) (column 2)
(column 3)
*Begin Draw No.* *Ending Draw No.* *Shelf Location*
BC-1145.00 BC-1185.00 Shelf 1
BC-1186.01 BC-1258.02 Shelf 2
BC-1298.03 BC-1305.02 Shelf 3


EXAMPLE OF FORM LAYOUT:

DRAWING NUMBER: (TEXT BOX HERE) -- User enters BC-1150.01 (Since
this is between 'BC-1145.00 and BC-1185, would like for Access to
search between this parameter)

SEARCH: (COMMAND BUTTON HERE) -- User clicks on command button to
retrieve information

RESULTS: (LABEL OR TEXT BOX HERE) -- the information now shows
within a text or label box. (via example above, the results would be
"SHELF 1")

Please help. If more information is needed, please let me know.

Thanks!

Use this query -- Substitute you table name for April and the name of your
form and text box.

SELECT April.[Begin Draw No], April.[Ending Draw No], April.[Shelf
Location], [Forms]![YourFormName]![YourTextBox] AS [DRAWING NUMBER]
FROM April
WHERE ((([Forms]![YourFormName]![YourTextBox]) Between [Begin Draw No] And
[Ending Draw No]));

--
KARL DEWEY
Build a little - Test a little


" wrote:

Hello,

My name is April and my attempt in Access is to create a form, also
with a command button which will search for information and put this
information onto a label or box on the form.

SEE EXAMPLE BELOW:

OBJECTIVE:
Access will need to search between column 1 / 2 below (after user
inputs drawing number in text box and hit command (search) button and
bring back results of column 3.

(column 1) (column 2)
(column 3)
*Begin Draw No.* *Ending Draw No.* *Shelf Location*
BC-1145.00 BC-1185.00 Shelf 1
BC-1186.01 BC-1258.02 Shelf 2
BC-1298.03 BC-1305.02 Shelf 3


EXAMPLE OF FORM LAYOUT:

DRAWING NUMBER: (TEXT BOX HERE) -- User enters BC-1150.01 (Since
this is between 'BC-1145.00 and BC-1185, would like for Access to
search between this parameter)

SEARCH: (COMMAND BUTTON HERE) -- User clicks on command button to
retrieve information

RESULTS: (LABEL OR TEXT BOX HERE) -- the information now shows
within a text or label box. (via example above, the results would be
"SHELF 1")

Please help. If more information is needed, please let me know.

Thanks!

In the Click event of the button, put something like the following:

Me.txtResults = Nz(DLookUp("[Shelf Location]","[Table]","'" &
Me.txtDrawingNumber & "' BETWEEN [Begin Draw No] AND [End Draw No]"),"Not
Found")

Of course, you'll need to use your own table, field and control names. I've
just made these up. Note the single quotes around the value of the Drawing
Number.

Carl Rapson

wrote in message
ups.com...
Hello,

My name is April and my attempt in Access is to create a form, also
with a command button which will search for information and put this
information onto a label or box on the form.

SEE EXAMPLE BELOW:

OBJECTIVE:
Access will need to search between column 1 / 2 below (after user
inputs drawing number in text box and hit command (search) button and
bring back results of column 3.

(column 1) (column 2)
(column 3)
*Begin Draw No.* *Ending Draw No.* *Shelf Location*
BC-1145.00 BC-1185.00 Shelf 1
BC-1186.01 BC-1258.02 Shelf 2
BC-1298.03 BC-1305.02 Shelf 3


EXAMPLE OF FORM LAYOUT:

DRAWING NUMBER: (TEXT BOX HERE) -- User enters BC-1150.01 (Since
this is between 'BC-1145.00 and BC-1185, would like for Access to
search between this parameter)

SEARCH: (COMMAND BUTTON HERE) -- User clicks on command button to
retrieve information

RESULTS: (LABEL OR TEXT BOX HERE) -- the information now shows
within a text or label box. (via example above, the results would be
"SHELF 1")

Please help. If more information is needed, please let me know.

Thanks!