I am working on creating a timeshare sales and inventory database.  I am
having difficulty with the inventory.  The problem is that one unit could
be sold either by odd years, even years or consecutive years.  If a unit is
sold for consecutive years then I need it to be removed from the available
inventory, if a unit is sold for either odd or even years I need the
opposite year to stay in available inventory and consecutive would not be
an option.

Does anyone have any ideas on how to do this the simpliest way.

Hi Connie,

How I would solve the issue:

Create an option group on the Sales form with 4 option buttons or a combobox
with a list of 4 items:  Unsold, Consecutive, Even, Odd.  You might include
a date field to indicate the day the unit actually sold.  What ever the
mechanism, call it SalesStatus.

Thereafter, I would refer to the value of SalesStatus in the main record and
calculate the availability for reports, etc. by using a Select --- Case
statement and put the code into a function procedure in a general module
that you call from your query.

Private Function Availability(SalesStatus as string) as string

    Select SalesStatus
        Case Unsold
           Availability = "Available"

        Case Consecutive
           Availability = "Unavailable"

       Case Even
            If  format(Date,"YYYY") mod 2 =0 then
               Availability = "Unavailable"
            Else
                Availability = "Available"
            End If

       Case Odd
            If  format(Date,"YYYY") mod 2 =0 then
               Availability = "Available"
            Else
                Availability = "Unavailable"
            End If

    End Select

Change the return value to be words that fit your application.  Always
calculate the results when you need them rather than save them in the
database.  The above is all "air" code so your mileage may vary.  If you
want to show only the available units then, I'd create a new field in my
query:  UnitsAvail: =Availability(SalesStatus)  Down in the criteria line
put "Available" and you will see only those units that are available this
year.

HTH
Show quoteHide quote

Ok, I understand the select case, but I don't is when you say the above is
"air" code.  I am fairly new at programming in VB code, I have written a
few basic databases to track information, but this one is a little more
difficult.  I know the basics, would you be so kind as to elaborate on it.
This is a great solution, thanks so much for your assistance.

Hi Connie,

"Air code" means I pulled it out of the air.  I've used the solution before
but haven't referred to old code just now.  I just wrote whit I believe to
be true:  It's a caveat that you can't just cut and paste and be sure it
will work without further reasoning and modification.

HTH
Show quoteHide quote

Jan 1, 2005  to Dec 31, 2005

Jan 1, 2007 to Dec 31, 2007





As you can see, you get maximum flexibility here. For requesting a
room/resource that is available, it is a simple matter to query the booking
table.



Further, the above approach means no fixed dates, and you can use the
software for properties that might be booked (split) in one year, or even 6
months for one person, and 6 months for another party. And, even more
interesting is that you can overlap years, since perhaps some dates might
always start in February, Or June, and go for one year.



So, building a simple date booking table will give you VERY high
flexibility, and actually reduce the amount of code you need to write also.

Views in SQL; whats in Access?
On Screen Keyboard
"Time and Billing" database question -- access
Copy expressions with one change in each of 36 boxes
Can't find Master Link and Child Link Attributes