?
Solved

Problem with where condition between 2 dates

Posted on 2006-04-03
8
Medium Priority
?
210 Views
Last Modified: 2010-04-17
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
Comment
Question by:andrewl77
  • 4
  • 3
8 Comments
 
LVL 2

Expert Comment

by:johnson00
ID: 16360306
Try this:

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

Author Comment

by:andrewl77
ID: 16369094
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
 
LVL 2

Expert Comment

by:johnson00
ID: 16370127
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:andrewl77
ID: 16370134
yes i have.  The statements work perfectly when this section is deleted
0
 
LVL 2

Expert Comment

by:johnson00
ID: 16370457
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
 

Author Comment

by:andrewl77
ID: 16370688
i tried with and without the $.....it makes no difference.
0
 
LVL 2

Expert Comment

by:johnson00
ID: 16370764
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
 
LVL 4

Accepted Solution

by:
abdulhameeds earned 2000 total points
ID: 16384974
try to dont use the between () statment

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

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Introduction to Processes

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question