Select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-07-26 : 08:35:30

Hi,

Please convert access to sql,

INSERT INTO tblProjectScheduleEv ( ProjectPracticeID, EvTypeID, EvDate, EvAccount, EvBFvalue, EvBFtypeID, EvBFDate, RemLetter, RemLetterGenDate, BatchID )
SELECT tblProjectPractices.ProjectPracticeID, [forms]![frmScheduler]![EvTypeID] AS EvTypeID, [forms]![frmScheduler]![EvDate] AS EvDate, [forms]![frmScheduler]![EvAccount] AS EvAccount, [forms]![frmScheduler]![EvBFValue] AS EvBFValue, [forms]![frmScheduler]![EvBFTypeID] AS EvBFTypeID, [forms]![frmScheduler]![EvBFDate] AS EvBFDate, [forms]![frmScheduler]![TogGenLetter] AS RemLetter, IIf([forms]![frmScheduler]![TogGenLetter]=-1,Date(),"") AS LetterGenDate, DMax("BatchID","tblBatchID") AS BatchID
FROM tblProjectPractices INNER JOIN qryPracsWithOvDueSchedules ON tblProjectPractices.ProjectPracticeID = qryPracsWithOvDueSchedules.ProjectPracticeID
WHERE (((tblProjectPractices.ProjectPracticeID) In (SELECT tblProjectPractices.ProjectPracticeID
FROM tblProjectPatients INNER JOIN tblProjectPractices ON (tblProjectPatients.ProjectID = tblProjectPractices.ProjectID) AND (tblProjectPatients.PracticeEID = tblProjectPractices.PracticeEID)
WHERE (((tblProjectPractices.ProjectID)=[forms]![frmScheduler]![txtProjects]) AND ((tblProjectPatients.[No])=-1)) OR (((tblProjectPractices.ProjectID)=[forms]![frmScheduler]![txtProjects]) AND ((tblProjectPatients.InD)=-1))
GROUP BY tblProjectPractices.ProjectPracticeID;) And (tblProjectPractices.ProjectPracticeID) In (SELECT tblProjectScheduleEv.ProjectPracticeID
FROM (tblProjectPractices INNER JOIN tblProjects ON tblProjectPractices.ProjectID = tblProjects.ProjectID) INNER JOIN tblProjectScheduleEv ON tblProjectPractices.ProjectPracticeID = tblProjectScheduleEv.ProjectPracticeID
WHERE (((tblProjectScheduleEv.EvTypeID)=1) AND ((tblProjects.ProjectStatus)=0));)) AND ((tblProjectPractices.ProjectID)=[forms]![frmScheduler]![txtProjects]));

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-26 : 08:39:23

That can't easily convert to SQL: [forms]![frmScheduler]![txtProjects] and [forms]![frmScheduler]![TogGenLetter]
That's a reference to an access form. in SQL queries can only reference variables or parameters for passed in values.

--
Gail Shaw
SQL Server MVP

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-07-26 : 08:46:16

ignore the references, for instance

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-26 : 08:54:02

Access has -1 as true and 0 as false (if i recall). SQL doesn't have a boolean type. If you use bit, the convention is that 0 is false, 1 is true. It won't accept a value of -1. Hence those hardcoded -1 need to change appropriately.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources