andrewl77
asked on
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.Li
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?
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.Li
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?
ASKER
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
This is quite urgent...need this resolved tonight if possible.
Bumbing this up to 500 points
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?
ASKER
yes i have. The statements work perfectly when this section is deleted
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.
ASKER
i tried with and without the $.....it makes no difference.
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.
" (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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
(select note from globalnotes where $fixtures.gamedate between "# & globalnotes.start & #" and "# & globalnotes.end & #") as globalnote