CODEWITHSUNDEEP
ms-accessvalidationrule
Nina
Nina

Reputation: 11

Access / validationrule - calculation

I need to set up a small database as an assignment after 3 hours of an access introduction and I'm failing completely. However I youtubed several hours but still no change in the outcome. Do I try to implment too many excel functions into access?

I saw in one resolved question that it if calculation is possible in access?

I have three tables for a dvd rental business. Customers, Movies, Rentals.

My questions / problems:

I wanted to do an age check in regards to the customer's age and the age restriction of the movie. I have customer's date of birth and the age restriction for a movie. Provided fields DoB 15/03/1996, age restriction 18+. For example user is born 15/03/1996 and wants to rent a movie 18+. Access should show me the customer's current age like: Field name: Age; "17". This didn't work out at all. I failed in the first step to set up the field/calculation.

In excel it's usually =INT((TODAY()-DoB)/365.25) which tells you the age up to date.

How do I need to set it up?

Many thanks in advance Nina

Upvotes: 0

Views: 409

Answers (1)

Chris Rolliston
Chris Rolliston

Reputation: 4808

You need to convert the Excel formula to use VBA functions. Since Int exists in VBA, and the Excel UI, VBA and and Access database engine (DAO/ADE) all use the OLE date/time format, the conversion is pretty trivial - just use the Date function in place of TODAY.

So, say the tables are set up like this:

  1. Customers - CustomerID (AutoNumber, primary key), Surname (Text, required), Forename (Text, required), DoB (Date/Time, required)

  2. Movies - MovieID (AutoNumber, primary key), MovieTitle (Text, required), MinAge (Number/Byte)

  3. Rentals - RentalID (AutoNumber, primary key), CustomerID (Number/Long Integer, required, foreign key to Customers.CustomerID), MovieID (Number/LongInteger, required, foreign key to Movies.MovieID)

You could then create a query called RentalsWithAge as so:

SELECT Rentals.*, Movies.MinAge, Int((Date() - Customers.DoB) / 365.25) AS CustomerAge
FROM (Rentals INNER JOIN Customers ON Rentals.CustomerID = Customers.CustomerID)
INNER JOIN Movies ON Rentals.MovieID = Movies.MovieID;

(Do so by going to create a new query in the UI, before cancelling the table prompt and heading straight to the SQL view.) Next, create a form to log a new rental, and base it on RentalsWithAge (not Rentals); ensure all the fields discussed have been added to the form, then handle the form's BeforeUpdate event as so:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Me.CustomerAge < Nz(Me.MinAge, 0) Then
    Cancel = True
    MsgBox "Customer is under the minumum age for the chosen movie!", _
      vbCritical, "Customer Under Age"
  End If
End Sub

Upvotes: 1

Related Questions