Having problems getting a multiple date query to work correctly, any suggestions? (1 Viewer)

[Bill Harrison]

I am using Access 2000, and am trying to query our table to get what we call a "Prior inventory Report"

We are a used car dealer. On this report we will need to pull data based on 2 criteria: Every vehicle that was in stock up to the date entered in the report. To do this I am using a query and the criteria for the field "DATE_IN" is "Between (1/1/1900) And [Type the end date]". That gives me a list of all vehicles stocked in before the "End date". Then I need to remove any vehicles that were sold PRIOR to the end date. We have a "DATESOLD" field, and by typing "Is Null" that removes all sold vehicles from the query. HOWEVER, the problem with that, is it removes vehicles sold AFTER the "End Date" which were still in inventory PRIOR to the end date, because they now have a sold date in the "DATESOLD". Does anyone have any suggestions on how to deal with this query? I am lost

Thanks!

 

[The_Doc_Man]

It would seem that the Access schools in your area are focusing on used car dealership models now. This is either the third or fourth one we've seen in the last month, I think.

There are literally a bazillion ways to solve this problem. One way is to choose default dates (in the table, not any form) so that the date bought and date sold are set to something non-zero (non-null) but improbable. So like set the date bought to default to 1-Jan-1900, date sold to 31-Dec-2099, and then your dates become well-behaved. 'tain't the only way.

 

[Pat Hartman]

You need a compound condition.

(DATESOLD Is Null OR DATESOLD > [Type the end date])

I disagree with Doc about defaults. I prefer null. It is easier to recognize as an unknown value.

 

[Bill Harrison]

Thanks for the replys! I actually just got back to read this, but the solution was what pat recommended, I was just having a hard time wrapping my mind around the solution. I am new to access programming, so while there is a somewhat steep learning curve, it seems to offer alot of information.

PS, I am not in school, I am setting this up for my dealership. They are currently running "PCFile", DOS version, and it has alot of problems, so I am doing this to help them bring things to a more modern level!