Query Help

[mkov]

I am trying to pull information from a table using criteria entered into a form. The following is the SQL of they query that I am trying to get to work. This is only the portion of the query that I can not get to work, there is more, but it is way too much to post.

My problem is that if there are dates entered for "Brokerage Commissions" and non "Brokerage Commissions", the query seems to use the range entered for non "Brokerage Commissions" only. If I only input a date range for "Brokerage Commissions", I do get the correct period for them. If I only input a date for non "Brokerage Commissions", I get everything in that date range.

Hopefully this makes sence, if not let me know.

Thanks.

SELECT tblRepCommissions.RepNo, tblRepCommissions.ProductType, tblRepCommissions.Amount
FROM (tblRepCommissions INNER JOIN RepTable ON tblRepCommissions.RepNo = RepTable.RepID) INNER JOIN tblProductTypes ON tblRepCommissions.ProductType = tblProductTypes.ProductType
WHERE (((tblRepCommissions.ProductType)<>&quot;Brokerage Commissions&quot AND ((tblRepCommissions.Date) Between [Forms]![frmRepReportCriteria]![BegDate] And [Forms]![frmRepReportCriteria]![EndDate])) OR (((tblRepCommissions.ProductType)=&quot;Brokerage Commissions&quot AND ((tblRepCommissions.Date) Between [Forms]![frmRepReportCriteria]![BrokBegDate] And [Forms]![frmRepReportCriteria]![BrokEndDate]));

 

[PaulBricker]

Try changing the OR to AND

Paul

 

[PaulBricker]

Sorry, you original should produce the records you need. What happens if you take out the Product Type criteria. Do you get the records in the Date ranges you are looking for? I ran a similar query and got what I needed using just the logic from your original sql.

Paul

 

[mkov]

If I remove the ProductType, then I won't get the results I need. If the ProductType is &quot;Brokerage Commissions&quot;, I could and usually will have a completely different date range than if the ProductType is not &quot;Brokerage Commissions&quot;. The periods may overlap some, but will not be the same.

 

[PaulBricker]

What I'm wondering is where the issue might be. If you leave out the Product Type criteria, does your query return the correct date ranges based on the criteria in the Form? If not, then that may be where the problem is.


Paul

 

[mkov]

Ok, I tried that and played with some different date ranges.

If I leave the product type the way that it is, and put 2 completely different date ranges in, I get all the data from between the earliest date to the latest date. Ex: got data from 12/2/03 through 1/6/04.

If I remove the product type criteria, and put in the same date range as above, I get just the entries that fall within the 2 date ranges, nothing between the 2 ranges. Ex: got data from 12/2/03 to 12/10/03 and 12/25/03 to 1/6/04


Here is what I used
Other Commissions Brokerage Commissions
Beginning 12/02/2003 12/25/2003
Ending 12/10/2003 01/06/2004


The end result that I am looking for is:
Other Commissions between 12/2/03 and 12/10/03
Brokerage Commissions between 12/25/03 and 1/6/04

 

[PaulBricker]

OK so now try this. Try hardcoding your criteria. On the first criteria line for ProductType put
<>&quot;Brokerage Commissions&quot;
and on the first criteria line for the Date field put
Between #12/2/03# And #12/10/03#

then on the second criteria line for ProductType put
&quot;Brokerage Commissions&quot;
and on the second criteria line for Date put
Between #12/25/03# And #1/6/03#

See if it returns what you need.

Paul

 

[mkov]

Still the same problem.

I get everything from 12/2/03 to 12/10/03 and everything from 12/25/03 to 1/6/03

I have done this kind of thing before and had it work, but don't know why it won't this time.

I really appreciate the help Paul.

 

[PaulBricker]

Let me make sure I understand the problem. This query should return all
<> Brokerage Commission between 12/2/03 and 12/10/03

and all

Brokerage Commissions between 12/25/03 and 1/6/04

Is that what it's doing? Or is it not doing that.

Thanks

Paul

 

[mkov]

Yes, that is what it should be doing, but is not.

It is returning all commissions between the 2 dates no matter what product type they are.

 

[mkov]

I also tried an IIF statement in the criteria for the date range, but couldn't get that to produce the correct results either. The query did not return any records with it.

IIf(([ProductType]=&quot;Brokerage Commissions&quot,(Between #12/25/2003# And #01/06/2004#),(Between #12/02/2003# And #12/10/2003#))

 

[PaulBricker]

Is ProductType a lookup field in your table by some chance? What fields and values are in the ProductType Table.

Paul

 

[mkov]

Yes, it is a lookup to tblProductTypes.

There are 3 fields:

ProductType - Text 35, Primary Key
Company - Text 4
Revenue/CostType - Text 7

ProductTypes are: Annuities, Brokerage Commissions, DPP, Hedge Funds, MAFS Fees, MAPS Clearing, MAPS Fees, Misc Costs - MAFM, Misc Costs - MACC, Misc Revenue - MAFM, Misc Revenue - MACC, Mutual Fund (12b-1), Partnership Clearing, Partnership Fees or Partnership Commission

Company can be either MACC or MAFM

Revenue/CostType can be either Revenue or Cost

 

[PaulBricker]

I think that is the culprit, but I'm kind of at a loss as to exactly where to go from here. I don't use LookUp fields because they cause these types of problems. Here is what I'm going to suggest. On the Field line for a new field put this
MyCommissions:IIf(ProductType = &quot;Brokerage Commissions&quot;,1,2)

Then on the First criteria line put
=2

Then on the second criteria line put
=1

so that the 1's and 2's match up with the appropriate Date criteria.

See what that returns.

Paul

 

[mkov]

Paul, sorry I didn't get a chance to do this yesterday.

I added the field and when I run the query it returns 8 records all have a ProductType of &quot;Brokerage Commissions&quot;, and the new field shows a 1 for each record which is correct. But it is still pulling the information from both date ranges, so it has to be something with the dates.

If you can think of anything else to try I would be open to anything at this point.

Thanks for all of your help.

 

[PaulBricker]

I'm scratching my head on this one. It's got to be something we are missing. A couple of questions.
1. You say it returned 8 records. How many should it have returned? It seems that in this case, we returned &quot;Brokerage Commissions&quot; where before, you were returning <>&quot;Brokerage Commissions&quot;.
2. Did the IIF expression return 1's and 2's to the appropriate records
3. Are you running this against the whole query(you indicated it was too long to put in the thread) or are you running it against a small sample query represented by the SQL you posted in the first thread?

Paul

 

[mkov]

I agree, and think that it is something small. I have tripple checked the spelling in my criteria for references to fields on the form.

It should have returned 3 records. The other 5 are brokerage commissions between 12/2/03 and 12/10/03, which should not have been returned, it should have only returned non brokerage commissions. But there are none for that period and therefore it should have returned no records for that period. It is returning any record for each time period no matter what the ProductType is. So it returning only records from each time period, but it is retruning all records, basically ignoring the ProductType.

The IIF did return the appropriate values.

I am running this in a stripped down version of the full query, that way I can rule out any of the other criteria causing the problem.

Going back to something you said earlier about the lookups in the table, I am going to remove them. When the user inputs the data on the form, the combo box on the form is doing it's own lookup to the appropriate table for the choices that it will contain, so there is really no need to have the lookups in the tables themselves

Again, thanks for all of the help.

 

[PaulBricker]

Let me know how you make out. I'm very interested in a solution here.

Paul