If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Range
Hi,
I have a database that has a list of files that are stored away in numbered boxes. There is only one table which lists the box number, the contents of the box, and the date range (for example BoxNumber: 12345; Contents: Payroll records; From: 1/1/98; To: 12/31/98. The user needs to locate a box by either entering in the items wanted, for which I created a query using Like "*" & [Enter Item Searching For] & "*". The problem is, how can they search for a stored box based on a date range from 1/1/98 to 12/31/98? Right now, the Start Date (1/1/98) and End Date (12/31/98) are in separate fields. Do I need to have the whole date range in the same field such as 1/1/98-12/31/98? If so, how can you add 2 dates in the same field? I would appreciate any help I can get. Thanks. Marie |
#2
|
|||
|
|||
I don't think this can be done easily. The best I could come up with is to
use your existing criteria and also include criteria to pull all the records where the year matches an entry. For example... Add a new column to your query with your contents as... YearFrom: Format([Start Date],"yy") and your criteria of: =[Enter Year as XX] Then add another column as... YearTo: Format([End Date],"yy") and your criteria of: =[Enter Year as XX] You would need to put the criteria above on two separate lines to create an "OR" condition. Your contents criteria would need to be on both of these lines. In short, you'd look for all boxes where the contents matched AND the start date contained the year entered OR the contents matched AND the end ate contained the year entered. The user would then have a short list to view. Hopefully someone will have some other ideas. I know how to use "between" in the criteria, but don't know how to go the other way around. Good Luck, Rck B "Marie" wrote in message ... Hi, I have a database that has a list of files that are stored away in numbered boxes. There is only one table which lists the box number, the contents of the box, and the date range (for example BoxNumber: 12345; Contents: Payroll records; From: 1/1/98; To: 12/31/98. The user needs to locate a box by either entering in the items wanted, for which I created a query using Like "*" & [Enter Item Searching For] & "*". The problem is, how can they search for a stored box based on a date range from 1/1/98 to 12/31/98? Right now, the Start Date (1/1/98) and End Date (12/31/98) are in separate fields. Do I need to have the whole date range in the same field such as 1/1/98-12/31/98? If so, how can you add 2 dates in the same field? I would appreciate any help I can get. Thanks. Marie |
#3
|
|||
|
|||
On Mon, 27 Dec 2004 16:50:09 -0500, "Marie"
wrote: Hi, I have a database that has a list of files that are stored away in numbered boxes. There is only one table which lists the box number, the contents of the box, and the date range (for example BoxNumber: 12345; Contents: Payroll records; From: 1/1/98; To: 12/31/98. The user needs to locate a box by either entering in the items wanted, for which I created a query using Like "*" & [Enter Item Searching For] & "*". The problem is, how can they search for a stored box based on a date range from 1/1/98 to 12/31/98? Right now, the Start Date (1/1/98) and End Date (12/31/98) are in separate fields. Do I need to have the whole date range in the same field such as 1/1/98-12/31/98? If so, how can you add 2 dates in the same field? I would appreciate any help I can get. Thanks. Marie Basically you want to find those records where the date range supplied by the user overlaps the date range stored in the table, right? They must be separate fields; storing two dates in a Date/Time field is impossible, and storing two dates in a text field makes them useless. Try a criterion on both From and To of BETWEEN [Enter start date:] AND [Enter end date:] Put these criteria on *SEPARATE* lines of the query grid, and put the [Enter item searching for] criterion on both lines. John W. Vinson[MVP] |
#4
|
|||
|
|||
Thank you very much for your help. These suggestions worked for me.
Marie "Marie" wrote in message ... Hi, I have a database that has a list of files that are stored away in numbered boxes. There is only one table which lists the box number, the contents of the box, and the date range (for example BoxNumber: 12345; Contents: Payroll records; From: 1/1/98; To: 12/31/98. The user needs to locate a box by either entering in the items wanted, for which I created a query using Like "*" & [Enter Item Searching For] & "*". The problem is, how can they search for a stored box based on a date range from 1/1/98 to 12/31/98? Right now, the Start Date (1/1/98) and End Date (12/31/98) are in separate fields. Do I need to have the whole date range in the same field such as 1/1/98-12/31/98? If so, how can you add 2 dates in the same field? I would appreciate any help I can get. Thanks. Marie |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using a date range in an IF function | Springtimetigger | Worksheet Functions | 1 | September 23rd, 2004 04:39 PM |
QDE (Quick Date Entry) | Norman Harker | Worksheet Functions | 37 | September 5th, 2004 01:24 AM |
QDE (Quick Date Entry) | Norman Harker | General Discussion | 3 | September 3rd, 2004 08:00 AM |
Does date fall between two ranges? | MR | Worksheet Functions | 4 | January 14th, 2004 04:08 PM |
Offset formula to add range depending on date | Todd | Worksheet Functions | 3 | November 22nd, 2003 06:12 PM |