Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ACCESS changes query all by itself

Status
Not open for further replies.

sblanche

Technical User
Jun 19, 2002
95
US
When I enter the following in a query line

(not "export" and not "import" and not "*international*") or is null

The query works fine but Access changes the 1 line into 2 lines and then changes the query to read--

<>"export" and <>"import and not like <>"*international*"

and then on the second line it added
Is Null

Any way to keep ACCESS from make 2 lines in the query?

Thanks,

 
unless those are boolean fields, i don't understand what you're trying to do, and i think you'll get a syntax error if you try to run the query

rudy
SQL Consulting
 
One of the fields in my query is CounSubSubject. There are 20 possible pick items for this field. In my query, I want to see the records where this field is blank or DOES NOT have the pick items of export, import or International. In my query grid, under that field I enter:

(not "export" and not "import" and not "*international*") or is null

The query works ok but since I have to enter this on 3 lines, I am ending up with 6 lines since ACCESS wants to change what I am typing in and make 2 lines from 1. I also have other parameters that I have to enter in the query so I am trying to minmize the number of grid lines that I have to use.
Thanks-
 
what is the datatype of the CounSubSubject field?

could you please view the query in SQL view, and paste it here?

rudy
SQL Consulting
 
Oh Rudy--Are you sure you want to see the SQL. Its a large query and gives me a headache just looking at it. What I did was remove as much as I could of the other parameters and just left the problem I am having so it won't be so large. What you see first is what I type in on one line which works-- What you see second is what happens to the query after it is saved and then ACCESS changes it and then makes the second line--It's a text field. Thank you for taking the time to look at this. Hope your eyes don't cross.

SELECT TBLOCCMain.OCCNumber, TBLOCCMain.AttorneyType, TBLOCCMain.CounTypeID, TblLookAttorney.Team, TBLOCCMain.CounMainSubject, TBLOCCMain.CounClient2ID, TBLOCCMain.CounClientID, TBLOCCMain.AssignType, TBLLinkCounDate.[Out OCC], TBLLinkCounDate.[Internal Date], TBLOCCMain.CounCrossSubject, TBLOCCMain.MgmentProjectType
FROM (TBLOCCMain LEFT JOIN TBLLinkCounDate ON TBLOCCMain.OCCNumber = TBLLinkCounDate.OCCNumber) LEFT JOIN TblLookAttorney ON TBLOCCMain.AttorneyID = TblLookAttorney.AttorneyID
WHERE (((TBLOCCMain.AttorneyType)="c") AND ((TblLookAttorney.Team)="cfsan") AND ((TBLOCCMain.CounMainSubject)="foods") AND ((TBLOCCMain.AssignType)="c") AND ((TBLLinkCounDate.[Out OCC]) Is Null) AND ((TBLLinkCounDate.[Internal Date]) Is Null) AND ((Not (TBLOCCMain.CounCrossSubject)="export" And Not (TBLOCCMain.CounCrossSubject)="import" And (TBLOCCMain.CounCrossSubject) Not Like "*International*") Or (TBLOCCMain.CounCrossSubject) Is Null));

AFter query has been saved--

SELECT TBLOCCMain.OCCNumber, TBLOCCMain.AttorneyType, TBLOCCMain.CounTypeID, TblLookAttorney.Team, TBLOCCMain.CounMainSubject, TBLOCCMain.CounClient2ID, TBLOCCMain.CounClientID, TBLOCCMain.AssignType, TBLLinkCounDate.[Out OCC], TBLLinkCounDate.[Internal Date], TBLOCCMain.CounCrossSubject, TBLOCCMain.MgmentProjectType
FROM (TBLOCCMain LEFT JOIN TBLLinkCounDate ON TBLOCCMain.OCCNumber = TBLLinkCounDate.OCCNumber) LEFT JOIN TblLookAttorney ON TBLOCCMain.AttorneyID = TblLookAttorney.AttorneyID
WHERE (((TBLOCCMain.AttorneyType)="c") AND ((TblLookAttorney.Team)="cfsan") AND ((TBLOCCMain.CounMainSubject)="foods") AND ((TBLOCCMain.AssignType)="c") AND ((TBLLinkCounDate.[Out OCC]) Is Null) AND ((TBLLinkCounDate.[Internal Date]) Is Null) AND ((TBLOCCMain.CounCrossSubject)<>"export" And (TBLOCCMain.CounCrossSubject)<>"import" And (TBLOCCMain.CounCrossSubject) Not Like "*International*")) OR (((TBLOCCMain.AttorneyType)="c") AND ((TblLookAttorney.Team)="cfsan") AND ((TBLOCCMain.CounMainSubject)="foods") AND ((TBLOCCMain.AssignType)="c") AND ((TBLLinkCounDate.[Out OCC]) Is Null) AND ((TBLLinkCounDate.[Internal Date]) Is Null) AND ((TBLOCCMain.CounCrossSubject) Is Null));
 
after careful review of both forms of the query, my conclusion is that they are equivalent

you may want to rewrite the where clause in SQL view like this:
Code:
 where TBLOCCMain.AttorneyType = 'c'
   and TblLookAttorney.Team = 'cfsan'
   and TBLOCCMain.CounMainSubject = 'foods'
   and TBLOCCMain.AssignType = 'c'
   and TBLLinkCounDate.[Out OCC] Is Null
   and TBLLinkCounDate.[Internal Date] Is Null
   and ( TBLOCCMain.CounCrossSubject Is Null
      or not
         ( TBLOCCMain.CounCrossSubject 
                in ('export','import')
        or TBLOCCMain.CounCrossSubject 
                like '*International*'
         )
       )
and see if access rewrites it again

rudy
SQL Consulting
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top