how to resolve a parameter conflict with unmatched records query?

  • Thread starter Thread starter Pitlochry1
  • Start date Start date
P

Pitlochry1

I have created a query for every calendar month that finds unmatched records
for expected receipts, and another query for every calendar month that finds
unmatched records for unexpected receipts. To open up the report to non
Access programmers I placed a date parameter, however this only works when it
is in the primary query when finding the unmatched records. Is there any way
of resolving the other way? it comes up asking for parameters that are not
there!
Or is there a simpler way of getting the same results? It does work with
query searching for 'last months' receipts, but not when changed to a date
parameter
 
Are you saying that you have created 12 different queries i.e. one for each
month? If so, there must be a better way.

Show us the SQL for one of the queries. Open the query in design view. Next
go to View, SQL View and copy and past it here.
 
Hi Jerry
SELECT [Development Payments].DonorNo, [Development Payments].Type,
[Development Payments].Occurring, [Development Payments].Amount, [Development
Payments].Duration, [Development Payments].GA, [Development Payments].Notes
FROM [Development Payments]
WHERE ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="1") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="1") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="1") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="1") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="1") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="12") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received"));
 
Hi Jerry,
Hopefully you will have seen the large amount of criteria. Basically the
table records donations that are one off and recurring. These queries are
focusing on expected recurring donations that are expected based on frequency
throughout the year. For example annually, bi-annually, quarterly and
monthly. They are also signed up for a number of years; 1-10. so I have
attempted to give my successors (without Access experienc) queries that will
last four years. I am sure that there is probably an easier query, however
this mountain grew out of a molehill with very little experience.
Glenn
 
That query is very strange as it seems to just repeat the exact same criteria
many times. I’ve simplified it below. Also it doesn’t seem to have much to do
with the orginal problem or I’m missing something.

Another possible problem is the use of a reserved word in a table or field
name. In this case “Type†is a reserved word. I put [] around it which should
stop any problems. In the future try to name fields something slightly
different than a reserved word. For example “Note†is a reserved word;
however, your “Notes†isn’t.
http://support.microsoft.com/kb/286335/

SELECT [Development Payments].DonorNo,
[Development Payments].[Type],
[Development Payments].Occurring,
[Development Payments].Amount,
[Development Payments].Duration,
[Development Payments].GA,
[Development Payments].Notes
FROM [Development Payments]
WHERE [Development Payments].[Type] = "S/O"
AND [Development Payments].Occurring IN ("1", "2", "4", "12")
AND [Development Payments].Duration Is Not Null
AND [Development Payments].Notes Not Like "ANOMALY*"
AND [Development Payments].Notes Not Like "not yet received" ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Pitlochry1 said:
Hi Jerry
SELECT [Development Payments].DonorNo, [Development Payments].Type,
[Development Payments].Occurring, [Development Payments].Amount, [Development
Payments].Duration, [Development Payments].GA, [Development Payments].Notes
FROM [Development Payments]
WHERE ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="1") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="1") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="1") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="1") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="1") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="12") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received"));

Jerry Whittle said:
Are you saying that you have created 12 different queries i.e. one for each
month? If so, there must be a better way.

Show us the SQL for one of the queries. Open the query in design view. Next
go to View, SQL View and copy and past it here.
Click to expand...
Click to expand...
 
Hi Jerry,

Thanks for that. The query that I pasted is only one half of the Find
Unmatched Query. The other query lists all payments received last
month(which works) but when I put in date parameters things go pearshaped:
SELECT [Development Payments].DonorNo, [Development Payments].Type,
[Development Payments].Occurring, [Development Payments].Amount, [Development
Payments].DateRecd, [Development Payments].Duration, [Development
Payments].GA, [Development Payments].Notes
FROM [Dev Fund Donor Details] RIGHT JOIN [Development Payments] ON [Dev Fund
Donor Details].KeyDonorNo = [Development Payments].DonorNo
GROUP BY [Development Payments].DonorNo, [Development Payments].Type,
[Development Payments].Occurring, [Development Payments].Amount, [Development
Payments].DateRecd, [Development Payments].Duration, [Development
Payments].GA, [Development Payments].Notes
HAVING ((([Development Payments].Type)="S/O") AND (([Development
Payments].DateRecd)>=DateSerial(Year(Date()),Month(Date())-1,1) And
([Development Payments].DateRecd)<Date()-Day(Date())+1) AND (([Development
Payments].Notes) Not Like "ANOMALY*")) OR ((([Development
Payments].Type)="S/O") AND (([Development
Payments].DateRecd)>=DateSerial(Year(Date()),Month(Date())-1,1) And
([Development Payments].DateRecd)<Date()-Day(Date())+1) AND (([Development
Payments].Notes) Not Like "NOT YET RECEIVED"))
ORDER BY [Development Payments].DonorNo;


As I worked from Design View and have yet to foray into SQL View I got the
results the long way I guess.

Jerry Whittle said:
That query is very strange as it seems to just repeat the exact same criteria
many times. I’ve simplified it below. Also it doesn’t seem to have much to do
with the orginal problem or I’m missing something.

Another possible problem is the use of a reserved word in a table or field
name. In this case “Type†is a reserved word. I put [] around it which should
stop any problems. In the future try to name fields something slightly
different than a reserved word. For example “Note†is a reserved word;
however, your “Notes†isn’t.
http://support.microsoft.com/kb/286335/

SELECT [Development Payments].DonorNo,
[Development Payments].[Type],
[Development Payments].Occurring,
[Development Payments].Amount,
[Development Payments].Duration,
[Development Payments].GA,
[Development Payments].Notes
FROM [Development Payments]
WHERE [Development Payments].[Type] = "S/O"
AND [Development Payments].Occurring IN ("1", "2", "4", "12")
AND [Development Payments].Duration Is Not Null
AND [Development Payments].Notes Not Like "ANOMALY*"
AND [Development Payments].Notes Not Like "not yet received" ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Pitlochry1 said:
Hi Jerry
SELECT [Development Payments].DonorNo, [Development Payments].Type,
[Development Payments].Occurring, [Development Payments].Amount, [Development
Payments].Duration, [Development Payments].GA, [Development Payments].Notes
FROM [Development Payments]
WHERE ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="1") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="1") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="1") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="1") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="1") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="12") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received"));

Jerry Whittle said:
Are you saying that you have created 12 different queries i.e. one for each
month? If so, there must be a better way.

Show us the SQL for one of the queries. Open the query in design view. Next
go to View, SQL View and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have created a query for every calendar month that finds unmatched records
for expected receipts, and another query for every calendar month that finds
unmatched records for unexpected receipts. To open up the report to non
Access programmers I placed a date parameter, however this only works when it
is in the primary query when finding the unmatched records. Is there any way
of resolving the other way? it comes up asking for parameters that are not
there!
Or is there a simpler way of getting the same results? It does work with
query searching for 'last months' receipts, but not when changed to a date
parameter
Click to expand...
Click to expand...
Click to expand...
 
Does this second query also work? I don't see a parameter in it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Pitlochry1 said:
Hi Jerry,

Thanks for that. The query that I pasted is only one half of the Find
Unmatched Query. The other query lists all payments received last
month(which works) but when I put in date parameters things go pearshaped:
SELECT [Development Payments].DonorNo, [Development Payments].Type,
[Development Payments].Occurring, [Development Payments].Amount, [Development
Payments].DateRecd, [Development Payments].Duration, [Development
Payments].GA, [Development Payments].Notes
FROM [Dev Fund Donor Details] RIGHT JOIN [Development Payments] ON [Dev Fund
Donor Details].KeyDonorNo = [Development Payments].DonorNo
GROUP BY [Development Payments].DonorNo, [Development Payments].Type,
[Development Payments].Occurring, [Development Payments].Amount, [Development
Payments].DateRecd, [Development Payments].Duration, [Development
Payments].GA, [Development Payments].Notes
HAVING ((([Development Payments].Type)="S/O") AND (([Development
Payments].DateRecd)>=DateSerial(Year(Date()),Month(Date())-1,1) And
([Development Payments].DateRecd)<Date()-Day(Date())+1) AND (([Development
Payments].Notes) Not Like "ANOMALY*")) OR ((([Development
Payments].Type)="S/O") AND (([Development
Payments].DateRecd)>=DateSerial(Year(Date()),Month(Date())-1,1) And
([Development Payments].DateRecd)<Date()-Day(Date())+1) AND (([Development
Payments].Notes) Not Like "NOT YET RECEIVED"))
ORDER BY [Development Payments].DonorNo;


As I worked from Design View and have yet to foray into SQL View I got the
results the long way I guess.

Jerry Whittle said:
That query is very strange as it seems to just repeat the exact same criteria
many times. I’ve simplified it below. Also it doesn’t seem to have much to do
with the orginal problem or I’m missing something.

Another possible problem is the use of a reserved word in a table or field
name. In this case “Type†is a reserved word. I put [] around it which should
stop any problems. In the future try to name fields something slightly
different than a reserved word. For example “Note†is a reserved word;
however, your “Notes†isn’t.
http://support.microsoft.com/kb/286335/

SELECT [Development Payments].DonorNo,
[Development Payments].[Type],
[Development Payments].Occurring,
[Development Payments].Amount,
[Development Payments].Duration,
[Development Payments].GA,
[Development Payments].Notes
FROM [Development Payments]
WHERE [Development Payments].[Type] = "S/O"
AND [Development Payments].Occurring IN ("1", "2", "4", "12")
AND [Development Payments].Duration Is Not Null
AND [Development Payments].Notes Not Like "ANOMALY*"
AND [Development Payments].Notes Not Like "not yet received" ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Pitlochry1 said:
Hi Jerry
SELECT [Development Payments].DonorNo, [Development Payments].Type,
[Development Payments].Occurring, [Development Payments].Amount, [Development
Payments].Duration, [Development Payments].GA, [Development Payments].Notes
FROM [Development Payments]
WHERE ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="1") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="1") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="1") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="1") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="1") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="12") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received"));

:

Are you saying that you have created 12 different queries i.e. one for each
month? If so, there must be a better way.

Show us the SQL for one of the queries. Open the query in design view. Next
go to View, SQL View and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have created a query for every calendar month that finds unmatched records
for expected receipts, and another query for every calendar month that finds
unmatched records for unexpected receipts. To open up the report to non
Access programmers I placed a date parameter, however this only works when it
is in the primary query when finding the unmatched records. Is there any way
of resolving the other way? it comes up asking for parameters that are not
there!
Or is there a simpler way of getting the same results? It does work with
query searching for 'last months' receipts, but not when changed to a date
parameter
Click to expand...
Click to expand...
Click to expand...
 
Hi Jerry
When the Date Received is changed to Between [start date] and [End date] and
then Find Unmatched Query (with the second query that I pasted, as the first
in the unmatched query) then other parameters are requested without being
needed. Find Unmatched Query SQL(without date parameters in Dev Monthly
Query):
SELECT [Dev Expected S/Os Jan].DonorNo, [Dev Expected S/Os Jan].Type, [Dev
Expected S/Os Jan].Occurring, [Dev Expected S/Os Jan].Amount, [Dev Expected
S/Os Jan].Duration, [Dev Expected S/Os Jan].GA, [Dev Expected S/Os Jan].Notes
FROM [Dev Expected S/Os Jan] LEFT JOIN [Dev Monthly] ON [Dev Expected S/Os
Jan].DonorNo = [Dev Monthly].DonorNo
WHERE ((([Dev Monthly].DonorNo) Is Null) AND (([Dev Monthly].DateRecd) Is
Not Null));

Obviously the whole report with Date Recd as Last Month does work, however
limits the time the query is effective.

Is there a simpler way to do the whole process?
Thanks
Glenn

Jerry Whittle said:
Does this second query also work? I don't see a parameter in it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Pitlochry1 said:
Hi Jerry,

Thanks for that. The query that I pasted is only one half of the Find
Unmatched Query. The other query lists all payments received last
month(which works) but when I put in date parameters things go pearshaped:
SELECT [Development Payments].DonorNo, [Development Payments].Type,
[Development Payments].Occurring, [Development Payments].Amount, [Development
Payments].DateRecd, [Development Payments].Duration, [Development
Payments].GA, [Development Payments].Notes
FROM [Dev Fund Donor Details] RIGHT JOIN [Development Payments] ON [Dev Fund
Donor Details].KeyDonorNo = [Development Payments].DonorNo
GROUP BY [Development Payments].DonorNo, [Development Payments].Type,
[Development Payments].Occurring, [Development Payments].Amount, [Development
Payments].DateRecd, [Development Payments].Duration, [Development
Payments].GA, [Development Payments].Notes
HAVING ((([Development Payments].Type)="S/O") AND (([Development
Payments].DateRecd)>=DateSerial(Year(Date()),Month(Date())-1,1) And
([Development Payments].DateRecd)<Date()-Day(Date())+1) AND (([Development
Payments].Notes) Not Like "ANOMALY*")) OR ((([Development
Payments].Type)="S/O") AND (([Development
Payments].DateRecd)>=DateSerial(Year(Date()),Month(Date())-1,1) And
([Development Payments].DateRecd)<Date()-Day(Date())+1) AND (([Development
Payments].Notes) Not Like "NOT YET RECEIVED"))
ORDER BY [Development Payments].DonorNo;


As I worked from Design View and have yet to foray into SQL View I got the
results the long way I guess.

Jerry Whittle said:
That query is very strange as it seems to just repeat the exact same criteria
many times. I’ve simplified it below. Also it doesn’t seem to have much to do
with the orginal problem or I’m missing something.

Another possible problem is the use of a reserved word in a table or field
name. In this case “Type†is a reserved word. I put [] around it which should
stop any problems. In the future try to name fields something slightly
different than a reserved word. For example “Note†is a reserved word;
however, your “Notes†isn’t.
http://support.microsoft.com/kb/286335/

SELECT [Development Payments].DonorNo,
[Development Payments].[Type],
[Development Payments].Occurring,
[Development Payments].Amount,
[Development Payments].Duration,
[Development Payments].GA,
[Development Payments].Notes
FROM [Development Payments]
WHERE [Development Payments].[Type] = "S/O"
AND [Development Payments].Occurring IN ("1", "2", "4", "12")
AND [Development Payments].Duration Is Not Null
AND [Development Payments].Notes Not Like "ANOMALY*"
AND [Development Payments].Notes Not Like "not yet received" ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hi Jerry
SELECT [Development Payments].DonorNo, [Development Payments].Type,
[Development Payments].Occurring, [Development Payments].Amount, [Development
Payments].Duration, [Development Payments].GA, [Development Payments].Notes
FROM [Development Payments]
WHERE ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="1") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="1") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="1") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="1") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="1") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="2") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="2") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="2") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="4") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received")) OR ((([Development Payments].Type)="S/O") AND
(([Development Payments].Occurring)="4") AND (([Development
Payments].Duration) Is Not Null) AND (([Development Payments].Notes) Not Like
"ANOMALY*" And ([Development Payments].Notes) Not Like "not yet received"))
OR ((([Development Payments].Type)="S/O") AND (([Development
Payments].Occurring)="4") AND (([Development Payments].Duration) Is Not Null)
AND (([Development Payments].Notes) Not Like "ANOMALY*" And ([Development
Payments].Notes) Not Like "not yet received")) OR ((([Development
Payments].Type)="S/O") AND (([Development Payments].Occurring)="12") AND
(([Development Payments].Duration) Is Not Null) AND (([Development
Payments].Notes) Not Like "ANOMALY*" And ([Development Payments].Notes) Not
Like "not yet received"));

:

Are you saying that you have created 12 different queries i.e. one for each
month? If so, there must be a better way.

Show us the SQL for one of the queries. Open the query in design view. Next
go to View, SQL View and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have created a query for every calendar month that finds unmatched records
for expected receipts, and another query for every calendar month that finds
unmatched records for unexpected receipts. To open up the report to non
Access programmers I placed a date parameter, however this only works when it
is in the primary query when finding the unmatched records. Is there any way
of resolving the other way? it comes up asking for parameters that are not
there!
Or is there a simpler way of getting the same results? It does work with
query searching for 'last months' receipts, but not when changed to a date
parameter
Click to expand...
Click to expand...
Click to expand...
 
Back
Top