Using IIF funcion

[italy47]

Hello everybody!

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]

I've been working at an MS Access query in order to get te 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 desired quater of the year.
At this point the result set should display the selected customer name, dates of debits, debits details.

I really hope someone can give me some idea for the code. Thanks for any help!

 

[pr2-eugin]

The above can be done, but with just form it is highly impossible !

 

[italy47]

The above can be done, but with just form it is highly impossible !

Hi everybody!
Sorry I forgot one field in the TblDebits, so the complete table is as follows:

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

I also wish to report the Sql code I’ve been trying which is:
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?]
This code 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.
Thanks a lot for your help.