Question Database that searches the values between two columns

[Tiger6115]

Hello, I'm new to Access programming. I am wondering if anyone can point me in the right direction to go. I want to setup an Archiving/Storage Database which contains data of boxes with folders in it. The data would be the box number, begining folder, ending folder and box description (See Below).

[BoxNum] [FromRange] [ToRange] [BoxInfo]
123456 10000 10100 Case File
123457 10101 10954 Batch File
123458 11234 11545 Case File

I would like to setup a database that once the data is entered a user can open a form and search for a folder like 10054 by entering it in to a field like [SearchBox] and search the values between [FromRange] and [ToRange] and retrieve that boxes information 123456.

Any information would be appreciated. Thanks in advance.

 

[jdraw]

What have you tried so far?
Do you have a data model? Can you show us tables and relationships in a jpg format?

Would you ever want to search by Subject or Keyword or Date????

 

[RainLover]

The number you search must be Greater than or Equal to FROMRANGE and Less than or Equal to TORANGE.

This can be done in a Query that references the Box on the form. The wizard will help you build this, if you don't know the correct Syntax.

 

[Tiger6115]

I haven't tried much, just setup a table with the following fields: [BoxNum], [FromRange], [ToRange], [BoxInfo] and setup a form to do the data entry.

The form to do the search is my problem. I just can't figure out how to search the value between two fields. The documents i'm being given only has the box number, it's description, begining folder in a box and the end folder in a box. I have researched and found the between function for dates in a query but have read it only works with dates.

I would like for the user to be able to search by entering a folder number and retrieve that boxes information. I haven't setup any relationships.

 

[Tiger6115]

@Rainlover

OK, i think i now have an understanding of which direction i should go in. Thanks RainLover,

 

[Tiger6115]

@Rainlover

Hello, I have been trying since yesterday but can not get a code to work. I tried building it using the wizard.

SELECT [Archive Record].BarcodeNum, [Archive Record].FromRange, [Archive Record].ToRange, [Archive Record].CustomerID, [Archive Record].DivisionID, [Archive Record].DepartmentID, [Archive Record].BoxDescription
FROM [Archive Record]
WHERE ((([Forms]![RMSearch].[SearchTxt])>=[Archive Record].[FromRange] And ([Forms]![RMSearch].[SearchTxt])<=[Archive Record].[ToRange]));

It returns with no data.

 

[Tiger6115]

Nevermind, I got it to work.

 

[RainLover]

Glad you got it.