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.
| Author |
Topic |
|
stephenbaer
Yak Posting Veteran
71 Posts |
 Posted - 2006-09-22 : 21:11:52
|
Moved from Transact-SQL forumLin100 writes,quote:
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 areresided in the SQL server.I have a query named customer_query. In the query, onthe "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, butsince 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 ?
First, an ADP file connects to either SQL Server or MSDE, so it is different from an MDB is several ways. I take it you figured that out on your own, but aren't quite sure how it affects things.The two major points are these:1. SQL Server/MSDE are not aware of the front end; they operate completely independent of it, so no, the database 'objects' (functions, queries & tables) can't 'read' information from forms like those in an MDB can.This is actually a good thing, because it means you can query the same database from any number of different types of applications, a web page, a visual basic exe, or Access, among others.2. Queries in SQL aren't quite like queries in Access. They come in several types, which I'm sure you've noticed, because of the three choices for "new" in the ADP. I'll try to explain the difference, but I'm new at this too, so bear with me.Views: Much like a regular Access query as setup goes, but nice because if you set a form to use a view as its source instead of a table, you can build some row-level filters in.Functions: These are sort of similar to a function in a module, but hard to work with in Access. I'd avoid trying at first, and put the functions in the modules if you can, at least until you are better with the SQL side.Stored Procedures: These are the real power in SQL. They work a bit like a query, but can contain some logic operations, too, hence the name.It only gets hard when you are used to Access, which knows all about your forms, etc. Also, Access has trouble with things it can't display graphically, like a scalar function (different conversation).In any case, your problem...Ok, well, first I'll have to make a couple of assumptions...Assumption 1: These are controls on a form, which I think is called "Selector," from your post.Assumption 2: Once these controls are populated, as in the user types in the dates for from and to, then either selects a dept or not. The user then clicks a command button to either view the the query results in table form, or to view/print a report that uses the query as its source. I'll address both the query and the report, if I can.Again, I'm new at this, too. You'll have to wait, though because I'm late for a meeting. Stay tuned, I'll post more later tonight or tomorrow morning. -----------------Stephen |
|
|
Lin100
Yak Posting Veteran
70 Posts |
Posted - 2006-09-24 : 16:26:31
|
| Hi stephenbaer. Let me restate briefly the question from the SQL forum here.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)You said "Access *does* know, but the back end, where the data and queries reside, doesn't. "What if FROMDATE is a variable created by using Dim FROMDATE As Date1) If I do not specified [Forms]![Selector]! , then Access would not be able to distinguish between [Forms]![Selector]!FROMDATE (a combo box) and FROMDATE (a variable) ////////////////////////////////////////////////I am new to stored procedure. So please specify which code suppose to be in a stored procedure (SQL Server), and which code suppose to be in Access front-end. |
 |
|
|
|
|
|
|
|
How to run a query that uses "iiF" and "Between"