SQL join---language

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been working with you guys since last week on a project----or 1
crucial step in it.

My join query looks like the following in SQL:

SELECT Amount FROM Rent WHERE (((Rent.location)=1) AND ((Rent.date) Between
[enter beginning date] And [enter ending date]));

UNION SELECT Amount From Journal WHERE (((Journal.type)=4) AND
((Journal.LocationID)=1) AND ((Journal.Date) Between [enter beginning date]
And [Enter ending date]));

when i try to run it, it says it is too complex or typed incorrectly. could
somebody help me in correcting this language? thanks a bunch.
 
You'll need to remove the semi-colon before 'UNION'. I'm not sure whether
that is the only issue, but it's a start.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
I have been working with you guys since last week on a project----or 1
crucial step in it.

My join query looks like the following in SQL:

SELECT Amount FROM Rent WHERE (((Rent.location)=1) AND ((Rent.date) Between
[enter beginning date] And [enter ending date]));

UNION SELECT Amount From Journal WHERE (((Journal.type)=4) AND
((Journal.LocationID)=1) AND ((Journal.Date) Between [enter beginning date]
And [Enter ending date]));

when i try to run it, it says it is too complex or typed incorrectly. could
somebody help me in correcting this language? thanks a bunch.
Click to expand...

One very possible problem is your use of the reserved words Type and
Date as fieldnames. I'd strongly suggest changing these fieldnames
(say to EntryType and PaymentDate or whatever makes sense). If you
keep the fieldnames, you should consistantly use square brackets -
Rent.[Date] and Journal.[Type]. Otherwise Access will get confused,
thinking you're referring to the Date() function which returns the
system clock date, or that you're trying to report what Type of object
Journal might be.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top