How to convert this to an Access query?

[OK]

I want to convert a SQL-Server based query to work with an Access Database.
This is the SQL-Server query :

Set rstTrace = RST("SELECT SUM(CASE WHEN DEBET<> 0 THEN
abs(round(geboekt,2)) ELSE 0 END) AS subtotDebet, SUM(CASE WHEN DEBET = 0
THEN abs(round(geboekt,2)) ELSE 0 END) AS subtotCredit FROM tblBookings
.........

Is there a way to convert it such that it would work with both databases?

 

[OK]

Hi Jeff,

No I'm not using Access to create a query. I write my queries by hand.
So can you please answer my question?

regards,
Oscar

 

[carlos]

Access not have CASE, try with IIF

SELECT
SUM(IIF(DEBET<> 0,abs(round(geboekt,2)),0) AS subtotDebet,
SUM(IIF(DEBET = 0,abs(round(geboekt,2)),0) AS subtotCredit FROM tblBookings

 

[Jeff Boyce]

If you use Access to create a query, you are using Access' version of the
SQL statement, which it will translate to SQL-Server specific syntax if you
are linked to a SQL-Server table.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John Spencer]

Writing the query in Access

SELECT Sum(IIF([Debet]<>0,Round(Geboekt,2),0)) as subTotDebet
, Sum(IIF([Debet]=0,Round(Geboekt,2),0)) as subTotCredit
FROM tblBookings



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Jeff Boyce]

You posed your question in a MS Access.queries newsgroup. If you want a
SQL-only reply, perhaps the SQL-only groups would be a better spot...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[OK]

Hi Chris,

sorry for the delay.

In order to minimize my coding time, I want to achieve that the same code
including the database queries applies for both databases.
There are small customers who use Access as the back-end to my VB
application while there are also bigger customers who use SQL Server as
back-end and I want to prevent to code all queries twice. As up to now I've
written a query handler which initiates and creates recordsets according to
the configured database and processes almost the same query. Only in a few
specific cases the program deals with a SQL-server based query next to the
Access based query.

Can you inform me whether the IIF -statement also will apply to SQL Server?

regards,
Oscar

I think there's some confusion here. You may not be using a wizard to
create
the query, but you are using the Access application when you write your
SQL
statement manually.

Therefore you have to use Jet SQL, not Transact-SQL, in order for Jet to
understand your query. But if you use Jet SQL on SQL Server linked
tables,
the Jet SQL will be translated for you, so you only have to write one
version
of the query that will work on a Jet table and a linked SQL table.

This should be the Jet SQL equivalent:

SELECT IIF(Debet <> 0, abs(round(geboekt,2)), 0) AS subtotDebet,
IIF(Debet = 0, abs(round(geboekt,2)), 0) AS subtotCredit
FROM tblBookings;

Alternatively, you could use a SQL passthrough query and use Transact-SQL,
but that will only work on SQL Server tables.

Chris
Microsoft MVP

Hi Jeff,

No I'm not using Access to create a query. I write my queries by hand.
So can you please answer my question?

regards,
Oscar

If you use Access to create a query, you are using Access' version of
the
SQL statement, which it will translate to SQL-Server specific syntax if
you
are linked to a SQL-Server table.

I want to convert a SQL-Server based query to work with an Access
Database.

[quoted text clipped - 6 lines]

Is there a way to convert it such that it would work with both
databases?

 

[Rick Brandt]

Hi Chris,

sorry for the delay.

In order to minimize my coding time, I want to achieve that the same
code including the database queries applies for both databases.
There are small customers who use Access as the back-end to my VB
application while there are also bigger customers who use SQL Server
as back-end and I want to prevent to code all queries twice. As up to
now I've written a query handler which initiates and creates
recordsets according to the configured database and processes almost
the same query. Only in a few specific cases the program deals with a
SQL-server based query next to the Access based query.

Can you inform me whether the IIF -statement also will apply to SQL
Server?

No.

 

[Michel Walsh]

Note that the Jet-iff statement works outside Access (such as in a query
targeting Jet, but used from Excel, or from VB6) and is technically
different than the iff defined in VBA.

iif does not exist if you target MS SQL Server itself, as database engine,
which uses the CASE construction.


Vanderghast, Access MVP

The IIF statement and any other VBA function can be used in any query in
Access, even if the linked table isn't Jet. Keep in mind that using the
db's
native sql dialect is going to be more efficient than Jet sql. So you
have
to weigh which one is more valuable, your development time or the
customer's
time in using a less efficient query. If the customer's time is more
valuable, write a sql passthrough query for the SQL Server tables and a
Jet
sql query for the linked Access tables.

Chris
Microsoft MVP

Hi Chris,

sorry for the delay.

In order to minimize my coding time, I want to achieve that the same code
including the database queries applies for both databases.
There are small customers who use Access as the back-end to my VB
application while there are also bigger customers who use SQL Server as
back-end and I want to prevent to code all queries twice. As up to now
I've
written a query handler which initiates and creates recordsets according
to
the configured database and processes almost the same query. Only in a few
specific cases the program deals with a SQL-server based query next to the
Access based query.

Can you inform me whether the IIF -statement also will apply to SQL
Server?

regards,
Oscar

I think there's some confusion here. You may not be using a wizard to
create

[quoted text clipped - 41 lines]

Is there a way to convert it such that it would work with both
databases?