Again on the IIF function

[italy47]

Hello everybody!

I try to reformulate my previous post more accurately in order to make clear the problem.

I have these 2 tables:

TblCustomers

CstId
CstName
PaymentPeriod [monthly payments = m; quaterly payments = q]

TblDebits

DbtId
DbtDate
DbtDetalis [specifications of what is being charged]
CstId [foreign key]

I've been working at an MS Access query in order to get the following results, though unsuccessfully so far.
(Pay attention: I'm not using VBA code or SQL Server but simple Access 2010 sql code).
When I run the query:
- Access should ask first for the customer name;
- When the name is entered, Access should check if the selected customer has a quaterly or a
monthly method of payment (that is, if the relevant field in the TblCustomers table is marked
"m" or "q");
- If the selected customer is labelled, say, "q", Access should ask for the desired year within
the DbtDate field;
- once the year is entered, Access should finally ask for the desired quater of the year.
- a similar procedure if the selected customer is marked “m”.
At this point the result set should display the selected customer name, dates of debits, debits details.

The code I’ve been trying is this:

SELECT
h.DbtDate, a.CstName, h.DbtDetails
FROM TblDebits AS h INNER JOIN TblCustomers AS a ON h.CstId = aCstId
WHERE ([quater?] = DatePart("q",[h.DbtDate])) AND YEAR(h.DbtDate) = [year?] AND a.CstName = [name?]

It works correctly. The problem is that I want Access to ask automatically for the desired month or quarter according to how a certain customer is labelled in the TblCustomers.PaymentPeriod field, while this code presumes that the selected customer is a “q” one.
Maybe an IIF function is necessary but all attempts I’ve made so far didn’t solve the problem.


I really hope someone can offer some tips. Thanks for reading this post!

 

[JHB]

Sorry, you'll not get the desire result without use some code, because you want the query to take some decision in the Where clause before the query is running.
Only for info:
You have a typo in the select statement you show.

 

[Cronk]

You have described a process where the operations are dependent on the particular instance. In this case whether it is "q" or "m".

You cannot I believe handle this with one query and you will have to resort to using VBA with different queries within an If/else or select case construct.

 

[Simon_MT]

Have you thought of putting this Client qualifying onto a Form using cascading Combiboxes rather that a query. Then go through your requirements step by step.

Simon