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

  • Thread starter Thread starter atillman
  • Start date Start date
A

atillman

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]));
 
In the Click event of the button, put something like the following:

Me.txtResults = Nz(DLookUp("[Shelf Location]","
","'" &
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
 
Back
Top