database results filter via 2 dropdown boxes

  • Thread starter Thread starter BarryS
  • Start date Start date
B

BarryS

Hi,
I have a problem with an answer I am sure is similar to
http://www.spiderwebwoman.com/tutorials/doubledropdown.htm

What I want to do is take a Venue from the first dropdown and produce in the
second dropdown a list of dates which are NOT already booked as an Event at
that Venue.
Terminology - an "Event" is a "Venue" with a "Date"
Tables are "Event", "Dates" and "Venue". The Events table contains DatesID
from Dates table and VenueID from Venue table

The sql for the Access query is below and does produce the required data in
Access
______________________________________________________________
SELECT Dates.DATESID, Dates.WC_date
FROM Dates LEFT JOIN [Event and Date Xref] ON Dates.DATESID = [Event and
Date Xref].WC_Date
WHERE (((Dates.WC_date)>Now()) AND (([Event and Date Xref].WC_Date) Is
Null));
______________________________________________________________


The sql for the Access qry "Event and Date Xref" used in the above is
______________________________________________________________
SELECT Event.Venue, Event.WC_Date
FROM Event
WHERE (((Event.Venue)=[]));
______________________________________________________________
Both quereies when run in Access require the VenueID (last line) entering
which on the website will come from the 1st dropdown.


I need to make the same data available to the 2nd dropdown, issue seems to
be caused by the need to send a "VenueID" from the 1st dropdown to "Event
and Date Xref"qry.


I will be grateful for any advice and am willing to venture outside of
Frontpage2003 if necessary

Thanks Barry
 
Don't attempt a Join on a query (Event and Date Xref)
(and even in Access never create a query w/ spaces in the query name if you are going to call it thru another query)

Do your join on tables
For some tips on Join in the DBRW See
http://home.att.net/~codelibrary/FrontPage/detail.htm
and on building a custom query see
http://home.att.net/~codelibrary/FrontPage/buildquery.htm


PS
Checking for Null is not the same as checking for an empty field
--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________


| Hi,
| I have a problem with an answer I am sure is similar to
| http://www.spiderwebwoman.com/tutorials/doubledropdown.htm
|
| What I want to do is take a Venue from the first dropdown and produce in the
| second dropdown a list of dates which are NOT already booked as an Event at
| that Venue.
| Terminology - an "Event" is a "Venue" with a "Date"
| Tables are "Event", "Dates" and "Venue". The Events table contains DatesID
| from Dates table and VenueID from Venue table
|
| The sql for the Access query is below and does produce the required data in
| Access
| ______________________________________________________________
| SELECT Dates.DATESID, Dates.WC_date
| FROM Dates LEFT JOIN [Event and Date Xref] ON Dates.DATESID = [Event and
| Date Xref].WC_Date
| WHERE (((Dates.WC_date)>Now()) AND (([Event and Date Xref].WC_Date) Is
| Null));
| ______________________________________________________________
|
|
| The sql for the Access qry "Event and Date Xref" used in the above is
| ______________________________________________________________
| SELECT Event.Venue, Event.WC_Date
| FROM Event
| WHERE (((Event.Venue)=[]));
| ______________________________________________________________
| Both quereies when run in Access require the VenueID (last line) entering
| which on the website will come from the 1st dropdown.
|
|
| I need to make the same data available to the 2nd dropdown, issue seems to
| be caused by the need to send a "VenueID" from the 1st dropdown to "Event
| and Date Xref"qry.
|
|
| I will be grateful for any advice and am willing to venture outside of
| Frontpage2003 if necessary
|
| Thanks Barry
|
|
 
Back
Top