Create query in ms access with three tables

Hi, I have this query which is working fine under the query design of MS Access, but I'm in the process to migrate the system to a web application, now my question is how can I do the same for the IIF statement in a sql query?


SELECT tblNews.IDNews, tblNews.Title, tblNews.Description, IIf([tblNewsDoctos]![IDNews]>0,"Y","") AS A, tblNews.PublicationDate AS [Date]
FROM (tblNews LEFT JOIN tblNewsDoctos ON tblNews.IDNews = tblNewsDoctos.IDNews) INNER JOIN tblNewsSource ON tblNews.IDNews = tblNewsSource.IDNews
GROUP BY tblNews.IDNews, tblNews.Title, tblNews.Description, IIf([tblNewsDoctos]![IDNews]>0,"Y",""), tblNews.PublicationDate, tblNewsSource.IDSource
HAVING (((tblNewsSource.IDSource)=[?]))
ORDER BY tblNews.Title;

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

in a sql query

What flavor of SQL?  SQL Server?
IIF can be replaced by CASE, but the usage and syntax are a little different

IIf([tblNewsDoctos]![IDNews]>0,"Y","") AS A

Case [IDNews]
      when 0 then ''
      else 'Y'
end AS A

will work

     When [IDNews] > 0 Then 'Y'
     When[IDNews] < 1 Then ''
end AS A

may also work as desired

C. Using CASE to replace the IIf function that is used in Microsoft Access
CASE provides functionality that is similar to the IIf function in Microsoft Access. The following example shows a simple query that uses IIf to provide an output value for the TelephoneInstructions column in an Access table that is named db1.ContactInfo.

Copy Code
SELECT FirstName, LastName, TelephoneNumber,
     IIf(IsNull(TelephoneInstructions),"Any time",
     TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo;
The following example uses CASE to provide an output value for the TelephoneSpecialInstructions column in the AdventureWorks2008R2 view Person.vAdditionalContactInfo.

Transact-SQL Copy Code
USE AdventureWorks2008R2;
SELECT FirstName, LastName, TelephoneNumber, "When to Contact" =
          WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'
          ELSE TelephoneSpecialInstructions
FROM Person.vAdditionalContactInfo;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave BaldwinFixer of ProblemsCommented:
The SQL query like the one you posted is just a text string to most programming languages.  You just fill in any varaible info and pass it to the query function.  Are you really using PHP on your web server?
If the database will remain "Access" which is actually Jet or ACE and not Access at all once you remove the application component, then the syntax remains the same.  If the database is something else, you would need to tell us what it is since the syntax can vary.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.