How to run a query that uses "iiF" and "Between"

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

SQL Server 2000 Forums

Transact-SQL (2000)

How to run a query that uses "iiF" and "Between"

Author

Topic

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-09-22 : 10:40:25

Access 2002 and SQL 2000 Server.
How to run a query that uses "IIF" and "Between" in an Access project file (ADP) ?

I have an Access project file (ADP), and the tables are
resided in the SQL server.

I have a query named customer_query. In the query, on
the "criteria" section it has the following:

Product-Name Criteria:
IIf([Forms]![Selector]![Dept] Is Null,[DepartmentName],[Forms]![Selector]![Dept])

Record-Date Criteria:
Between [Forms]![Selector]![From_Date] And [Forms]![Selector]![To_Date]

If this is an Access MDB file then the query would run, but
since this is an Access ADP file, it does not how to process the IIf statement,
and the Between statement.

How do I fix this so that it will work ?

sa
Yak Posting Veteran

77 Posts

Posted - 2006-09-22 : 11:41:15

If your UI was built from vb, I suggest you create a stored procedure which accepts parameters. From vb you can pass values of textboxes as parameter to a stored procedure.

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-22 : 17:16:39

Iif is really just a brief CASE. For instance, in VB, you would use Iif like this:
Procedure NewMath
Dim stNewMath As String
strNewMath = IIf(1 + 1 = 2, "No, its still the old math", "You exist in a different universe!")
Debug.Print strNewMath

In SQL, you could use CASE to do something similar, while also selecting a bunch of other stuff at the same time.
SELECT Somecolumn1,Somecolumn2,
CASE WHEN (1+1=2) THEN No, its still the old math
ELSE
CASE WHEN (1+1<>2) THEN(<---THIS PART ISN'T REALLY NECESSARY, SINCE THE 'ELSE' COVERS IT) You exist in a different universe! AS NewMath, Somecolumn4, Somecolumn5

This would return data like
col1data1 col2data1 No, its still the old math col4data1 col5data1
col1data2 col2data2 No, its still the old math col4data2 col5data2

Unless you actually are in a different universe, then the third column would return the other text.

Between is a simple <> comparison, or a <= >= comparison, if you wanted it inclusive.

To apply it more like you need to in your example, you would need to pass ([Forms]![Selector]![Dept], [Forms]![Selector]![From_Date], and [Forms]![Selector]![From_Date] as parameters, Like this

CREATE PROCEDURE CUSTOMER_QUERY
(
@DEPT AS VARCHAR
@FROMDATE AS DATETIME
@TODATE AS DATETIME
)
BEGIN
SELECT COLUMN1,COLUMN2,COLUMN3,etc.
FROM SOMETABLE
WHERE
(CASE WHEN @DEPT IS NULL THEN NULL ELSE [SOMETABLE].[DEPT]=@DEPT) AND @TODATE<[SOMETABLE].[DATE]>@FROMDATE

I think I got that right, but I'm sure the others will correct me if I'm wrong. You might have to separate the <> with another AND.

----------------
-Stephen

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-09-22 : 18:58:49

Hi stephenbaer.

You said "pass these as parameters"
[Forms]![Selector]![Dept]
[Forms]![Selector]![From_Date]
[Forms]![Selector]![From_Date]

Please look below
How will Acces know that DEPT is a combo box on a form when [Forms]![Selector]! is not included as in [Forms]![Selector]!DEPT ?

How will Acces know that FROMDATE is a combo box on a form when [Forms]![Selector]! is not included as in [Forms]![Selector]!FROMDATE ?

How will Acces know that TODATE is a combo box on a form when [Forms]![Selector]! is not included as in [Forms]![Selector]!TODATE ?

CREATE PROCEDURE CUSTOMER_QUERY
(
@DEPT AS VARCHAR
@FROMDATE AS DATETIME
@TODATE AS DATETIME
)

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-22 : 20:55:45

Access *does* know, but the back end, where the data and queries reside, doesn't. That's why you have to pass it. We really should move this conversation to the Access forum, or we're gonna get scolded. I'll answer over there. Click on all forums, above, then go to the Access one, way down at the bottom.

----------------
-Stephen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources