Access date Query

[Brocco]

Hi, i'm creating and Access project to do with bookings, resorts and sites. Each resort has a particular day in which it's booked for.



What I want to do is create a query where it tells me what Resort is not booked at the present date. I have all the tables created and I think I need to enter something in the 'Criteria' section.

I've looked everywhere for this and can't seem to find the solution. Swift reply would really be appreciated. Thanks, Dave

[Robeen]

In the 'Criteria' row of the Booked Field of your query type in 'Is Null' [without ticks].
This will give you all rows of data where there is nothing in the Booked field [resort is not booked].

If you want a query that tells you what Resort is not booked for Today's Date:
Type in 'Is Null' for Criteria under Booked and 'Date()' under criteria for your Date field.

I hope this helps.

[Brocco]

Sorry about this but I prob didn't explain myself correctly.

In my fields I have Resort name, Site name, plot number, Start date and End date. (The plot number is number of the plot on the site and the site is on the Resort)

I need to run query which says which Resort isn't booked on todays date. (This in-turn would tell me which Plot number and Site is not booked?

Sorry again.

[Robeen]

Does each Resort have more than one 'Site'?

And does each Site have more than one 'Plot'?

Is all the data in one Table?

Can you give me a small example of what the data looks like for one Resort?

[Brocco]

I took a screenshot of it, is there a way I can send it to you ?

[Robeen]

Post it here - it will be more helpful to anyone coming in later.

[Brocco]

Hope you can read this ?

[Robeen]

What happens if you put 'Is Null' in the Criteria for Start Date in your query?

[Brocco]

Each column comes up blank

[Robeen]

That is why I asked you:

Can you give me a small example of what the data looks like for one Resort?

If I see some actual [or fake] data - I might be able to better help you.

Make up some data for resorts that HAVE bookings and some that do NOT have bookings so I can see what it looks like.

[Brocco]

This is my Booking Table. Maybe i've the wrong fields in the Query ?

Each of my Resorts has only 1 booking, i've 10 Resorts

[Robeen]

What you need is a query that returns:
1. Every PlotID from tblPlot
2. StartDate & EndDate [??] from tblBooking

Then you want to put Is Null in the criteria for StartDate.
This will show you all the Plots that have no bookings.

Create a Query.
Add tblPlot & tblBooking - in that order.
Draw a Join between the Tables in Query Design on the PlotID.
Dbl-Click the Join line & select Option 2 [Select all rows from tblPlot and only those . . .]
Add PlotId from tblPlot & StartDate & EndDate from tblBooking to the results set.
Under criteria for StartDate type Is Null.

See if that works for you.

[Brocco]

When you say to Draw a join between the tables, what do I join to what ?

[Robeen]

You will be joining the PlotID field in tblPlot to the PlotID field in tblBooking.

Click & hold down on PlotID in tblPlot -> drag to PlotID in tblBooking.

Hope this helps!

[Brocco]

When I do this I get an error: 'The SQL Statement could be executed because it contains ambiguous outer lines......'

Oh my God............................