Access Functions in SQL

[Guest]

Hello,

We recently moved our Access Database to an SQL Server. All the
calculations I did in my previous reports, queries and forms are now not
working.

I am a beginner with SQL. Although I am presently taking a class for SQL, I
need to try and fix a lot of our queries as quickly as possible. I have some
assistance through the developer, but it is limited. Is there a website or
some resource I can go to learn how to revise or relocate these calculations
to accommodate the SQL requirements?

For example, in a form I have the following calulation to count how many of
our requests are categorized as Activa Therapy:

=Count(IIf([Therapy]='Activa',"1"))

How or where would I put this to pull that information in SQL?

Any assistance would be greatly appreciated.

Cathy

 

[Paul Shapiro]

Look in SQL Server's Books Online for the Case statement. BOL has great
documentation, and searching usually turns up what you need.
From the documentation:
Simple CASE function:
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Searched CASE function:
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

So your expression in a SQL Server query would be
Count(
Case [Therapy]
When 'Activa' Then 1
Else Null
End
)

Another common one is Access's NZ() is SQL Server's Coalesce.

If your expression is for a calculated control in Access, it stays the same.
Access forms use Access VBA, not SQL.

Paul Shapiro

 

[fredg]

Hello,

We recently moved our Access Database to an SQL Server. All the
calculations I did in my previous reports, queries and forms are now not
working.

I am a beginner with SQL. Although I am presently taking a class for SQL, I
need to try and fix a lot of our queries as quickly as possible. I have some
assistance through the developer, but it is limited. Is there a website or
some resource I can go to learn how to revise or relocate these calculations
to accommodate the SQL requirements?

For example, in a form I have the following calulation to count how many of
our requests are categorized as Activa Therapy:

=Count(IIf([Therapy]='Activa',"1"))

How or where would I put this to pull that information in SQL?

Any assistance would be greatly appreciated.

Cathy

When you place quotes around a number ("1"), it's considered a string
value, not a number value.

Try:
=Sum(IIf([Therapy]="Activa",1,0))

 

[Guest]

I'm not sure how SQL represents boolean expression. In Access you could do
something like this:

=Count(Abs([Therapy]='Activa'))

Cool, isn't it? ;-)

You can try to adapt the expression to SQL.

HTH

Vlado

 

[Dirk Goldgar]

I'm not sure how SQL represents boolean expression. In Access you
could do something like this:

=Count(Abs([Therapy]='Activa'))

I think maybe you meant

=Sum(Abs([Therapy]='Activa'))