Query to exclude records that have data in specific text field

[JrMontgom]

I have data in table called tblIncome with the fields as follows:




[AccountNumIn] as Text example "610910"
[Incometype] as Text example "EBCY"
[DateEnd] as Text in mm/dd/yyyy format for example Jan 1, 2013 = "01/01/2013", 02/01/2013" etc.
[Quarter] as text with a lookup table with 4 choices "1st Quarter", "2nd Quarter" etc.

The query

SELECT tblIncome.AccountNumIn, tblIncome.IncomeType, tblIncome.DateStart, tblIncome.DateEnd, tblIncome.CEFAmt, tblIncome.Quarter
FROM tblIncome
WHERE (((tblIncome.AccountNumIn)="610910") AND ((tblIncome.IncomeType)="EBCY") AND (Year(DateValue([dateend]))="2013"));

works but I can't exclude the data in the field [Quarter] when it contains "1st Quarter,2nd Quarter,3rd Quarter,4th Quarter" strings. When I used the following Query

SELECT tblIncome.AccountNumIn, tblIncome.IncomeType, tblIncome.DateStart, tblIncome.DateEnd, tblIncome.CEFAmt, tblIncome.Quarter
FROM tblIncome
WHERE (((tblIncome.AccountNumIn)="610910") AND ((tblIncome.IncomeType)="EBCY") AND ((tblIncome.Quarter)=IsNull([Quarter])) AND ((Year(DateValue([dateend])))="2013"));

the result is zero records when their should be 12 from [dateEnd] = 01/01/2013 to 12/31/2013.
Any guesses as to how I can exclude the [Quarter] records while retaining the other records?

[June7]

Values in Quarter field are "1st Quarter", "2nd Quarter" etc.?

(tblIncome.Quarter)=IsNull([Quarter]) won't work

IsNull([Quarter]) returns a Boolean True/False (Yes/No) value

Try:

(tblIncome.Quarter) Not LIKE "*Quarter*"

[JrMontgom]

Values in Quarter field are "1st Quarter", "2nd Quarter" etc.?

(tblIncome.Quarter)=IsNull([Quarter]) won't work

IsNull([Quarter]) returns a Boolean True/False (Yes/No) value

Try:

(tblIncome.Quarter) Not LIKE "*Quarter*"

I added the above to my query

SELECT tblIncome.AccountNumIn, tblIncome.IncomeType, tblIncome.DateStart, tblIncome.DateEnd, tblIncome.CEFAmt, tblIncome.Quarter
FROM tblIncome
WHERE (((tblIncome.AccountNumIn)="610910") AND ((tblIncome.IncomeType)="EBCY") AND (([tblIncome].[Quarter]) Not ALike "*Quarter*") AND ((Year(DateValue([dateend])))="2013"));

and ran the query but It ONLY showed 4 records i.e. the records with data in the field [Quarter] and excluded all the other 12 records. Please note MS Access 2010 uses "Alike" instead of "Like", is that a problem? I used Like but Alike was substituted when I ran the query.

[June7]

My 2010 uses LIKE.

Review http://answers.microsoft.com/en-us/o...b-68b599b31bf5

Maybe ALIKE is the opposite of LIKE? What happens if you drop NOT?

[JrMontgom]

I did as suggested and dropped in Options the Ansii 92 and now Alike does not appear but the results are the same i.e. no data for the Not Like and when I use Like only 4 records appear i.e. "1st QUarter", "2nd Quarter" etc. I would expect there to be, with the Not Like code to show 12 records excluding the ones with '1st Quarter" etc but I get NO records? Certainly is a puzzle. I really appreciate your help and especially your prompt response to my cries for help. Could it have something to do with the fact the [Quarter] field has a lookup set of strings? (example "1st Quarter", "2nd Quarter" etc.?

[June7]

Quarter is a lookup? Then that is definitely the issue. The actual value in the field is the foreign key, not the associated alias descriptor. I NEVER set lookups in table.

How many possible values are there?

[JrMontgom]

Quarter is a lookup? Then that is definitely the issue. The actual value in the field is the foreign key, not the associated alias descriptor. I NEVER set lookups in table.

How many possible values are there?

Thanks for your quick reply. I set the lookups in the table design and they are the strings: "1st Quarter","2nd Quarter","3rd Quarter","4th Quarter". On further investigation I can create a working query using Like but can't use Not Like ("*Quarter*). When I use the "NOT" SQL cmd it shows NO records if I use Like ("*Quarter*) it shows all the records meeting the AND criteria i.e. the records with data in the [Quarter] field. I will try changing the table definition and not use the Lookup strings and see if that makes a difference but the fact it works with LIKE but fails with NOT LIKE is very weird. Once again thanks for the help.

[June7]

So if the field does not have one of the 4 values it must be null. You want the records where the field is null? Will any record ever be null in this field?

[Quarter] Is Null

[JrMontgom]

So if the field does not have one of the 4 values it must be null. You want the records where the field is null? Will any record ever be null in this field?

[Quarter] Is Null

Yes to be exact I want the records which have [Incometype] = "EBCY" AND [AccountNumIn] = "610910" AND Year(datevalue([DateEnd])) = "2013" AND IsNull([Quarter]). The [Quarter] field can be null or have string values denoting the Financial quarter.

[June7]

So did that work?

[JrMontgom]

So did that work?

Yes finally I just used IS Null in [Quarter] field and ignored Like command. Why Like didn't work still illudes me. Thanks for your help

[June7]

Because

Null LIKE "*Quarter*"

returns Null

Null cannot be used in logical expressions, see Error 5 in http://allenbrowne.com/casu-12.html

[JrMontgom]

Because

Null LIKE "*Quarter*"

returns Null




Null cannot be used in logical expressions, see Error 5 in http://allenbrowne.com/casu-12.html

Thanks I finally read the Allen Browne link and it answers all my questions. The man is a clear and concise writer I would love to be as clear as he is ;-)