0

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

Nina
  • 11
  • 1
  • 1
  • 4

1 Answers1

1

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
Chris Rolliston
  • 4,788
  • 1
  • 16
  • 20
  • Thank you so much! I read your answer several times but I'm not good enough to understand the instructions and what to enter in the criteria query. I saw codes like in the grey box several times but I have no clue where and how to type it in. May I should just take it more simple when I can't implement it. But I thank you so much. – Nina Nov 26 '13 at 23:12
  • Did you try to implement Chris answer? Does it work? – Guilhem Hoffmann Nov 26 '13 at 23:57
  • Hi Guilhem, I tried to implement Chris answer and opened a query, selected complete Rental table and MinAge from the movie table. But then I didn't understand what to do next. – Nina Nov 27 '13 at 07:40
  • @user3035621 - is this the 'create a query called RentalsWithAge' part or something else? As I put in the brackets afterwards, to enter SQL code directly (that's the `SELECT` stuff), you need to create a new query but immediately cancel the table picker and go into SQL View. – Chris Rolliston Nov 27 '13 at 11:32
  • Hi Chris. The problem is I didn't learn SQL at all. But I will give it a try and youtube it later. Thanks Nina – Nina Nov 27 '13 at 13:27