parameter query - multiple criteria

[juicybeasty]

I'm having a problem with a query, as follows:

I need to be able to enter the name of an account handler when prompted. So in the criteria section of the AccountHandler field I've got [name]. This works OK when none of the other fields have criteria.

However I also need to have data for specific months only.
I have CreatedMonth and CreatedYear fields, and the criteria I have for them are:
CreatedMonth - "nov" or "dec" CreatedYear - "02"
CreatedMonth - "jan" or "feb" or "mar" Created Year - "03"

Once I add the above criteria (on 2 separate lines as shown above), it still asks for AccountHandler name, but regardless of what you put in it still brings up details for ALL account handlers.

No doubt there's a simple explanation for this.

I hope someone can help me. Thanks.

 

[Travis]

I think there is a simple explanation for this, but to confirm what I think the issue is, could you please post the SQL view of your query.

 

[juicybeasty]

No problem, here it is:

 

[Mile-O]

Out of interest, do you have a field that stores a date which these CreatedMonth and CreatedYear fields apply to?

 

[juicybeasty]

Yeah, they're stored in a table.

 

[Mile-O]

No, I mean is there another field that stores a date which is how these two fields' values are derived from?
i.e

Date Created: 22-April-2003
MonthCreated: Apr
YearCreated: 03

 

[juicybeasty]

Oh, I get you!

No, they were all entered like that - (there's also CreatedDay but that's not relevant here).

Is that likely to cause a problem?

 

[Mile-O]

I was just thinking that if you kept one field that held the date created, even if it's just a default value of =Date() that would put the current date into your record, you could save the need for those extra fields.

 

[Mile-O]

That would make your query easier as you could just say, on the date field:-

Between DateSerial(2002, 11, 1) And (DateSerial(2003, 4, 1)-1)

 

[juicybeasty]

Would that prevent it ignoring the [name] parameter in the criteria of AccountHandler?

 

[Mile-O]

Would that prevent it ignoring the [name] parameter in the criteria of AccountHandler?

What is [Name]? Is it a field? If it's not, that's why it's prompting you?

Before you run this query do you let the user select a person's name? If so, then you'll have to reference the control using:

[Forms]![frmYourForm]![txtYourControl]

 

[juicybeasty]

Unfortunately adding those two parentheses doesn't make a difference. That would have been a great answer!

 

[Travis]

Acctually the answer is quite clear once I look at the SQL of the Query:

What you did was you asked for all records for

1. Criteria:
a. Account Exec
b. Create Month = 'nov' or 'dec'
c. Create Year = '02'

OR

2. Criteria:
a. Create Month= 'jan' or 'feb' or 'mar'
b. Create Year = '03'

Notice that you did not add the Account Exec to the Second Criteria. This will give you every record for Jan, Feb and Mar for 03.

In the QBE Grid you will notice that the criteria are on seperate lines. Just add the [Name] to the second line also.


Try this:

SELECT tblProspects.AccountExec, tblProspects.ClientName, tblProspects.CurrentStatus, tblProspects.LatestPosition, tblProspects.CurrPrem, tblProspects.RenewalDate, tblProspects.Prem, tblProspects.Comm, tblProspects.Fee, tblProspects.Insurer, tblProspects.EffectiveDay, tblProspects.EffectiveMonth, tblProspects.EffectiveYear, tblProspects.CreatedDay, tblProspects.CreatedMonth, tblProspects.CreatedYear
FROM tblProspects
WHERE (((tblProspects.AccountExec)=[name]) AND ((tblProspects.CreatedMonth)="nov" Or (tblProspects.CreatedMonth)="dec") AND ((tblProspects.CreatedYear)="02")) OR (((tblProspects.AccountExec)=[name]) AND ((tblProspects.CreatedMonth)="jan" Or (tblProspects.CreatedMonth)="feb" Or (tblProspects.CreatedMonth)="mar") AND ((tblProspects.CreatedYear)="03"))
ORDER BY tblProspects.AccountExec, tblProspects.CurrentStatus;

 

[juicybeasty]

That's great, it works.

Thank you very much.