Problem with where condition between 2 dates

I have the below query that is excuted to run a report.  Need to display a note that applieds to every record that is printed in the report.  

    Set rs1 = conn.Execute("select fixtures.*, (select teamname from clublist where fixtures.hteamno = clublist.id) as hometeam, (select teamname from clublist where fixtures.ateamno = clublist.id) as awayteam, (Select gradename from competitiongrades where fixtures.gradecode = competitiongrades.id) as gradename, (select leaguename from competitions where fixtures.leagueno = competitions.leagueno) as leaguename, (select groundname from grounds where fixtures.venue = grounds.groundid) as groundname, (select groundstreet from grounds where fixtures.venue = grounds.groundid) as groundstreet, (select groundtown from grounds where fixtures.venue = grounds.groundid) as groundtown, (select groundmelways from grounds where fixtures.venue = grounds.groundid) as groundmelways," & _
    " (select fieldno from appointments where fixtures.gameno = appointments.gameno) as fieldno, (select fieldump1 from appointments where fixtures.gameno = appointments.gameno) as fieldump1val, (select displayname from umpire where umpire.umpireno = fieldump1val) as fieldump1, (select homeph from umpire where umpire.umpireno = fieldump1val) as fu1home, (select mobileph from umpire where umpire.umpireno = fieldump1val) as fu1mobile, (select fieldump2 from appointments where fixtures.gameno = appointments.gameno) as fieldump2val, (select displayname from umpire where umpire.umpireno = fieldump2val) as fieldump2, (select homeph from umpire where umpire.umpireno = fieldump2val) as fu2home," & _
    " (select mobileph from umpire where umpire.umpireno = fieldump2val) as fu2mobile, (select fieldump3 from appointments where fixtures.gameno = appointments.gameno) as fieldump3val, (select displayname from umpire where umpire.umpireno = fieldump3val) as fieldump3, (select homeph from umpire where umpire.umpireno = fieldump3val) as fu3home, (select mobileph from umpire where umpire.umpireno = fieldump3val) as fu3mobile, (select boundaryump1 from appointments where fixtures.gameno = appointments.gameno) as boundaryump1val, (select displayname from umpire where umpire.umpireno = boundaryump1val) as boundaryump1, (select homeph from umpire where umpire.umpireno = boundaryump1val) as bu1home," & _
    " (select mobileph from umpire where umpire.umpireno = boundaryump1val) as bu1mobile, (select boundaryump2 from appointments where fixtures.gameno = appointments.gameno) as boundaryump2val, (select displayname from umpire where umpire.umpireno = boundaryump2val) as boundaryump2, (select homeph from umpire where umpire.umpireno = boundaryump2val) as bu2home, (select mobileph from umpire where umpire.umpireno = boundaryump2val) as bu2mobile, (select boundaryump3 from appointments where fixtures.gameno = appointments.gameno) as boundaryump3val, (select displayname from umpire where umpire.umpireno = boundaryump3val) as boundaryump3, (select homeph from umpire where umpire.umpireno = boundaryump3val) as bu3home," & _
    " (select mobileph from umpire where umpire.umpireno = boundaryump3val) as bu3mobile, (select goalump1 from appointments where fixtures.gameno = appointments.gameno) as goalump1val, (select displayname from umpire where umpire.umpireno = goalump1val) as goalump1, (select homeph from umpire where umpire.umpireno = goalump1val) as gu1home, (select mobileph from umpire where umpire.umpireno = goalump1val) as gu1mobile, (select goalump2 from appointments where fixtures.gameno = appointments.gameno) as goalump2val, (select displayname from umpire where umpire.umpireno = goalump2val) as goalump2, (select homeph from umpire where umpire.umpireno = goalump2val) as gu2home, (select mobileph from umpire where umpire.umpireno = goalump2val) as gu2mobile" & _
    " (select note from globalnotes where $fixtures.gamedate between #" & globalnotes.start & "# and #" & globalnotes.end & "#) as globalnote from fixtures where fixtures.gamecompleted = 0 and fixtures.leagueno = " & (league.ItemData(league.ListIndex)) & " And fixtures.roundno = " & (roundno.ItemData(roundno.ListIndex)) & " group by fixtures.gameno", , adCmdText)

I am getting an runtime error 424 object required at the point of this part     " (select note from globalnotes where $fixtures.gamedate between #" & globalnotes.start & "# and #" & globalnotes.end & "#) as globalnote

What i want this to do is to return the note from the table globalnotes when the gamedate field from the fixtures table is between the start and end date nominated in the globalnotes table.

I have tried a couple of different things but can't get it to work.  Any ideas?
andrewl77Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnson00Commented:
Try this:

(select note from globalnotes where $fixtures.gamedate between "# & globalnotes.start & #" and "# & globalnotes.end & #") as globalnote
andrewl77Author Commented:
No luck.  This gives a syntax error when it runs.

This is quite urgent...need this resolved tonight if possible.

Bumbing this up to 500 points
johnson00Commented:
Have you tried running each of the individual select statements to ensure the error isn't being caused by another of the embedded select statements?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

andrewl77Author Commented:
yes i have.  The statements work perfectly when this section is deleted
johnson00Commented:
Could it be the $ in front of fixtures in your statement?  I don't see that in any of the other statements you have listed.
andrewl77Author Commented:
i tried with and without the $.....it makes no difference.
johnson00Commented:
Ok, then try this:

" (select note, start, end from globalnotes where $fixtures.gamedate between #" & globalnotes.start & "# and #" & globalnotes.end & "#) as globalnote

I don't see anywhere else in your statement where you're selecting these items, so it most likely can't find them.
abdulhameedsCommented:
try to dont use the between () statment

try to use the Fdate >= # " & date1  & " # and Fdate <= # " & date2  & " #
may be it will work

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.