• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

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?
0
andrewl77
Asked:
andrewl77
  • 4
  • 3
1 Solution
 
johnson00Commented:
Try this:

(select note from globalnotes where $fixtures.gamedate between "# & globalnotes.start & #" and "# & globalnotes.end & #") as globalnote
0
 
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
0
 
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?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
andrewl77Author Commented:
yes i have.  The statements work perfectly when this section is deleted
0
 
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.
0
 
andrewl77Author Commented:
i tried with and without the $.....it makes no difference.
0
 
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.
0
 
abdulhameedsCommented:
try to dont use the between () statment

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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now