About Access Project Views

[Guest]

Hi,

1. How can I create a view in Access Project (.adp), which require user to
enter parameter. Question is - can I create view like Parameter Query in .mdb?

2. What's synthax is for conditional expressions in SQL. For example, in
..mdb I have expression for months:


IIf(Month([Dat])=1,"Jan",IIf(Month([Dat])=2,"Feb",IIf(Month([Dat])=3,"Mar",IIf(Month([Dat])=4,"Apr",IIf(Month([Dat])=5,"May",IIf(Month([Dat])=6,"Jun",IIf(Month([Dat])=7,"Jul",IIf(Month([Dat])=8,"Aug",IIf(Month([Dat])=9,"Sep",IIf(Month([Dat])=10,"Okt",IIf(Month([Dat])=11,"Nov",IIf(Month([Dat])=12,"Dec"))))))))))))

How can I do the same in adp?

Thanks

 

[Norman Yuan]

See comment inline.

Hi,

1. How can I create a view in Access Project (.adp), which require user to
enter parameter. Question is - can I create view like Parameter Query in

..mdb?

View is not an object in ADP, it is in SQL Server database. ADP is only a
front-end UI and IDE. View in SQL Server DB does not allow parameter, nor
sorting ("ORDER BY"). What you need is Stored Procedure, which is a lot more
powerful than the *.mbd counterpart Query.

2. What's synthax is for conditional expressions in SQL. For example, in
.mdb I have expression for months:

IIf(Month([Dat])=1,"Jan",IIf(Month([Dat])=2,"Feb",IIf(Month([Dat])=3,"Mar",I
If(Month([Dat])=4,"Apr",IIf(Month([Dat])=5,"May",IIf(Month([Dat])=6,"Jun",II
f(Month([Dat])=7,"Jul",IIf(Month([Dat])=8,"Aug",IIf(Month([Dat])=9,"Sep",IIf
(Month([Dat])=10,"Okt",IIf(Month([Dat])=11,"Nov",IIf(Month([Dat])=12,"Dec"))
))))))))))

IN SQL Server's T-SQL, you use CASE...END as you use IIF in Access *.mdb,
such as

SELECT
Filed1,
CASE
WHEN field2=0 THEN 'N/A'
ELSE field2
END AS FieldName,
Field3
FROM Table1
WHERE...

But to your particular question, you can get the day of the month for given
date value easily by using T-SQL built-in function DatePart(). You can also
write your own function (UDF) in SQL Server for such tedious calculation
once forever (if you use SQL Server2K).

How can I do the same in adp?

Of course you can also do this purely in ADP the front-end with VBA code.
You, as developer, decide what is getting run on the server, or on the
front-end. But the above sample would be better running on the server during
quering (in SP or View)