467,905 Members | 1,849 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,905 developers. It's quick & easy.

SQL-Report Query Contains an Error, but where??

Hi folks,

I need once again your help. I have a long SQL-Report Query, which has an error build in, however, I do not see it at all. I have typed it with the SQL-Builder, not with the Wizard (don't like that Click and Go).

Does anybody see the mistake, error message: Syntax Error in FROM clause (great Error message) :-)

Expand|Select|Wrap|Line Numbers
  1. SELECT Contacts.FirstName, Contacts.LastName, CostCenter.CostCenter, CostCenter.OrgUnit, CostCenter.Region, 
  2. Workstream.Workstream, Workstream.[Cap/Non-Cap], Work.StartDate, Work.EndDate, Work.[Int/Ext], Vendor.Vendor, 
  3. Contacts.ContactID FROM CostCenter INNER JOIN (Contacts INNER JOIN (Workstream INNER JOIN [Work] (Vendor INNER JOIN [Work] 
  4. ON Vendor.VendorID=Work.Vendor) ON Workstream.WorkstreamID=Work.WorkstreamID)  ON Contacts.ContactID=Work.ContactID) 
  5. ON CostCenter.CostCenterID=Work.CostCenter WHERE (((Work.StartDate)>=[forms]![ReportDateForm]![txtDateFrom]) 
  6. AND ((Work.EndDate)<=[forms]![ReportDateForm]![txtDateTo]));
  7.  
Thanks a lot for the help.

Alive
Aug 15 '07 #1
  • viewed: 1402
Share:
5 Replies
FishVal
Expert 2GB
Hi folks,

I need once again your help. I have a long SQL-Report Query, which has an error build in, however, I do not see it at all. I have typed it with the SQL-Builder, not with the Wizard (don't like that Click and Go).

Does anybody see the mistake, error message: Syntax Error in FROM clause (great Error message) :-)

Expand|Select|Wrap|Line Numbers
  1. SELECT Contacts.FirstName, Contacts.LastName, CostCenter.CostCenter, CostCenter.OrgUnit, CostCenter.Region, 
  2. Workstream.Workstream, Workstream.[Cap/Non-Cap], Work.StartDate, Work.EndDate, Work.[Int/Ext], Vendor.Vendor, 
  3. Contacts.ContactID FROM CostCenter INNER JOIN (Contacts INNER JOIN (Workstream INNER JOIN [Work] (Vendor INNER JOIN [Work] 
  4. ON Vendor.VendorID=Work.Vendor) ON Workstream.WorkstreamID=Work.WorkstreamID)  ON Contacts.ContactID=Work.ContactID) 
  5. ON CostCenter.CostCenterID=Work.CostCenter WHERE (((Work.StartDate)>=[forms]![ReportDateForm]![txtDateFrom]) 
  6. AND ((Work.EndDate)<=[forms]![ReportDateForm]![txtDateTo]));
  7.  
Thanks a lot for the help.

Alive
Hi, Alive.

I'm not sure. Just a guess. I think this join will not work

(Workstream INNER JOIN [Work] (Vendor INNER JOIN [Work]
ON Vendor.VendorID=Work.Vendor) ON Workstream.WorkstreamID=Work.WorkstreamID)

Anyway, when you build complicated table join do it stepwise running the query each time new table is added to the join to see whether it works and returns records you are expecting for.
Aug 15 '07 #2
Hi, Alive.

I'm not sure. Just a guess. I think this join will not work

(Workstream INNER JOIN [Work] (Vendor INNER JOIN [Work]
ON Vendor.VendorID=Work.Vendor) ON Workstream.WorkstreamID=Work.WorkstreamID)

Anyway, when you build complicated table join do it stepwise running the query each time new table is added to the join to see whether it works and returns records you are expecting for.
Hi Fish,

Thanks, good hint, I will try to solve it step-by-step.

May I ask you another question:? I have a tbl_Contact and tbl_Work which are 1--N related. Now what I would like to implement is a button that adds to the Contact a 2nd Work-Information. Is that possible with SQL? Do you know the command? So that I could do some research.

Thanks a lot
Aug 15 '07 #3
FishVal
Expert 2GB
Hi Fish,

Thanks, good hint, I will try to solve it step-by-step.

May I ask you another question:? I have a tbl_Contact and tbl_Work which are 1--N related. Now what I would like to implement is a button that adds to the Contact a 2nd Work-Information. Is that possible with SQL? Do you know the command? So that I could do some research.

Thanks a lot
You are welcome.

There are at least two approaches:
  • Normaly this is implemented with Subform
  • If you have some very special reason not to use subform you can simulate subform automation by setting DefaultValues of the controls linked to 1-side table to there current values. Actually this is the same that subform does.
In any case I would suggest you to use subform as more simple, natural and reliable solution.


BTW it looks like continuation of you previous thread,
VB and Form!Field populating (automatically)
have you got it working ?
Aug 15 '07 #4
You are welcome.

There are at least two approaches:
  • Normaly this is implemented with Subform
  • If you have some very special reason not to use subform you can simulate subform automation by setting DefaultValues of the controls linked to 1-side table to there current values. Actually this is the same that subform does.
In any case I would suggest you to use subform as more simple, natural and reliable solution.


BTW it looks like continuation of you previous thread,
VB and Form!Field populating (automatically)
have you got it working ?
No, its already a new issue. Thanks to your help I could solve the first one. :-) I have closed it now.

Back to the ContactID has two WorkID Problem: Do you think I should open a new discussion?
I understand why I should implement a subform, the problem I have is how to create that "create 2ndWork ID button" (the code behind it). Somehow I have to tell this button to leave the information typed in Contacts and simply add a 2nd WorkID.

Thanks
Aug 15 '07 #5
FishVal
Expert 2GB
No, its already a new issue. Thanks to your help I could solve the first one. :-) I have closed it now.

Back to the ContactID has two WorkID Problem: Do you think I should open a new discussion?
I understand why I should implement a subform, the problem I have is how to create that "create 2ndWork ID button" (the code behind it). Somehow I have to tell this button to leave the information typed in Contacts and simply add a 2nd WorkID.

Thanks
Ok. Let's move to another thread. Plz provide comprehensive information on table structure and relationships.
Aug 15 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by george lewycky | last post: by
2 posts views Thread by Peter | last post: by
10 posts views Thread by Dagwood | last post: by
9 posts views Thread by Grim Reaper | last post: by
11 posts views Thread by Neil | last post: by
3 posts views Thread by datapro01 | last post: by
8 posts views Thread by M K | last post: by
14 posts views Thread by Developer | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.