Report - Query Criteria problem <>

[Carrie]

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.

 

[Ofer]

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"))

 

[Carrie]

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?

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.

 

[Carrie]

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.

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?

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.

 

[Ofer]

I'm glad you got it sorted out, good luc with your project
--
\\// Live Long and Prosper \\//
BS"D

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.

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?

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.