Report - Query Criteria problem <>

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I originally had one select query and one report for all our outstanding
issues (Status="Open"). We recently had to add a new enforcement category
(Obligations which happens to be 8 in the Enforcement Category table). This
needs a seperate report so I simply copied both the report and the query and
altered them as below (one =8 and one <>8). Both queries run and give me the
results I expect.

However, the report (both identical except the title) will only work with
the query including =8. Whenever I try to open the report for <>8 Access
gets stuck and cannot open the report (no error message). If I change the
query to = any number the report will run but it doesn't seem to like <>. I
have now tried to remove the criteria altogether (so that I would get all
"open" results) but that causes problems with the report too although that is
what I had before!

I have tried making a copy of the working report and changing the source to
the other query but the problem persists.

Works: WHERE (((Compl_MAIN_Table.Status)="Open") AND
((Compl_MAIN_Table.[Enforcement Category])=8) AND
((Company_Lookup.Acclaim_Company)<>"Does not belong to Acclaim"))

Does not work: WHERE (((Compl_MAIN_Table.Status)="Open") AND
((Compl_MAIN_Table.[Enforcement Category])<>8) AND
((Company_Lookup.Acclaim_Company)<>"Does not belong to Acclaim"))

I am sure that there must be some obvious answer but it must be lost on me.
Any direction is much appreciated! Thanks.
 
I don't know why the report should freeze, unless it a big table and the
search take a long time.
Any way, I found out that sometimes using Not In Run faster then <>, worth a
try

WHERE (((Compl_MAIN_Table.Status)="Open") AND
((Compl_MAIN_Table.[Enforcement Category]) Not In(8)) AND
((Company_Lookup.Acclaim_Company)<>"Does not belong to Acclaim"))
 
Hi, Thanks for your response. The Not In didn't work either and there are
only about 9 records on the report (so it isn't overly large) but I've
noticed that some of the comments (long text field) are quite long - is it
possible that the length of this field could be causing the problem?

Ofer said:
I don't know why the report should freeze, unless it a big table and the
search take a long time.
Any way, I found out that sometimes using Not In Run faster then <>, worth a
try

WHERE (((Compl_MAIN_Table.Status)="Open") AND
((Compl_MAIN_Table.[Enforcement Category]) Not In(8)) AND
((Company_Lookup.Acclaim_Company)<>"Does not belong to Acclaim"))
--
\\// Live Long and Prosper \\//
BS"D


Carrie said:
Hi, I originally had one select query and one report for all our outstanding
issues (Status="Open"). We recently had to add a new enforcement category
(Obligations which happens to be 8 in the Enforcement Category table). This
needs a seperate report so I simply copied both the report and the query and
altered them as below (one =8 and one <>8). Both queries run and give me the
results I expect.

However, the report (both identical except the title) will only work with
the query including =8. Whenever I try to open the report for <>8 Access
gets stuck and cannot open the report (no error message). If I change the
query to = any number the report will run but it doesn't seem to like <>. I
have now tried to remove the criteria altogether (so that I would get all
"open" results) but that causes problems with the report too although that is
what I had before!

I have tried making a copy of the working report and changing the source to
the other query but the problem persists.

Works: WHERE (((Compl_MAIN_Table.Status)="Open") AND
((Compl_MAIN_Table.[Enforcement Category])=8) AND
((Company_Lookup.Acclaim_Company)<>"Does not belong to Acclaim"))

Does not work: WHERE (((Compl_MAIN_Table.Status)="Open") AND
((Compl_MAIN_Table.[Enforcement Category])<>8) AND
((Company_Lookup.Acclaim_Company)<>"Does not belong to Acclaim"))

I am sure that there must be some obvious answer but it must be lost on me.
Any direction is much appreciated! Thanks.
Click to expand...
Click to expand...
 
Me Again - found the problem - one of the comments boxes had a lot of text in
it so, I shortened it up and the report runs fine.

Carrie said:
Hi, Thanks for your response. The Not In didn't work either and there are
only about 9 records on the report (so it isn't overly large) but I've
noticed that some of the comments (long text field) are quite long - is it
possible that the length of this field could be causing the problem?

Ofer said:
I don't know why the report should freeze, unless it a big table and the
search take a long time.
Any way, I found out that sometimes using Not In Run faster then <>, worth a
try

WHERE (((Compl_MAIN_Table.Status)="Open") AND
((Compl_MAIN_Table.[Enforcement Category]) Not In(8)) AND
((Company_Lookup.Acclaim_Company)<>"Does not belong to Acclaim"))
--
\\// Live Long and Prosper \\//
BS"D


Carrie said:
Hi, I originally had one select query and one report for all our outstanding
issues (Status="Open"). We recently had to add a new enforcement category
(Obligations which happens to be 8 in the Enforcement Category table). This
needs a seperate report so I simply copied both the report and the query and
altered them as below (one =8 and one <>8). Both queries run and give me the
results I expect.

However, the report (both identical except the title) will only work with
the query including =8. Whenever I try to open the report for <>8 Access
gets stuck and cannot open the report (no error message). If I change the
query to = any number the report will run but it doesn't seem to like <>. I
have now tried to remove the criteria altogether (so that I would get all
"open" results) but that causes problems with the report too although that is
what I had before!

I have tried making a copy of the working report and changing the source to
the other query but the problem persists.

Works: WHERE (((Compl_MAIN_Table.Status)="Open") AND
((Compl_MAIN_Table.[Enforcement Category])=8) AND
((Company_Lookup.Acclaim_Company)<>"Does not belong to Acclaim"))

Does not work: WHERE (((Compl_MAIN_Table.Status)="Open") AND
((Compl_MAIN_Table.[Enforcement Category])<>8) AND
((Company_Lookup.Acclaim_Company)<>"Does not belong to Acclaim"))

I am sure that there must be some obvious answer but it must be lost on me.
Any direction is much appreciated! Thanks.
Click to expand...
Click to expand...
Click to expand...
 
I'm glad you got it sorted out, good luc with your project
--
\\// Live Long and Prosper \\//
BS"D


Carrie said:
Me Again - found the problem - one of the comments boxes had a lot of text in
it so, I shortened it up and the report runs fine.

Carrie said:
Hi, Thanks for your response. The Not In didn't work either and there are
only about 9 records on the report (so it isn't overly large) but I've
noticed that some of the comments (long text field) are quite long - is it
possible that the length of this field could be causing the problem?

Ofer said:
I don't know why the report should freeze, unless it a big table and the
search take a long time.
Any way, I found out that sometimes using Not In Run faster then <>, worth a
try

WHERE (((Compl_MAIN_Table.Status)="Open") AND
((Compl_MAIN_Table.[Enforcement Category]) Not In(8)) AND
((Company_Lookup.Acclaim_Company)<>"Does not belong to Acclaim"))
--
\\// Live Long and Prosper \\//
BS"D


:

Hi, I originally had one select query and one report for all our outstanding
issues (Status="Open"). We recently had to add a new enforcement category
(Obligations which happens to be 8 in the Enforcement Category table). This
needs a seperate report so I simply copied both the report and the query and
altered them as below (one =8 and one <>8). Both queries run and give me the
results I expect.

However, the report (both identical except the title) will only work with
the query including =8. Whenever I try to open the report for <>8 Access
gets stuck and cannot open the report (no error message). If I change the
query to = any number the report will run but it doesn't seem to like <>. I
have now tried to remove the criteria altogether (so that I would get all
"open" results) but that causes problems with the report too although that is
what I had before!

I have tried making a copy of the working report and changing the source to
the other query but the problem persists.

Works: WHERE (((Compl_MAIN_Table.Status)="Open") AND
((Compl_MAIN_Table.[Enforcement Category])=8) AND
((Company_Lookup.Acclaim_Company)<>"Does not belong to Acclaim"))

Does not work: WHERE (((Compl_MAIN_Table.Status)="Open") AND
((Compl_MAIN_Table.[Enforcement Category])<>8) AND
((Company_Lookup.Acclaim_Company)<>"Does not belong to Acclaim"))

I am sure that there must be some obvious answer but it must be lost on me.
Any direction is much appreciated! Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top