Is Not Null doesn't work

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

Guest

I have a field with names and I want to filter out the null records.
In the criteria I put Is Not Null, but it doesn't work. I also have it
by date, between enter start date and enter end date. It seems to
work if I only do a week at a time. However there are over 1000
records and when I enter dates to get all records, it also returns
the null fields.
What I really want to do is count the records, but cannot get the
count or Dcount to work.
Thanks for any help
Roger
 
i tried your is not null, and it is working for dates as well as text.
you can also try to input:
<>null
see if it works!
rest question is not clear.
1) what do you want for dates?
2)as far as counting is concerned, a)open a new query, write count()in the
field name. within () u can either use * or you can use the name of your
column. b) open access help and search for "expressions" and you can get
some help.
hope it works!
shivalee
 
I have a field with names and I want to filter out the null records.
In the criteria I put Is Not Null, but it doesn't work. I also have it
by date, between enter start date and enter end date. It seems to
work if I only do a week at a time. However there are over 1000
records and when I enter dates to get all records, it also returns
the null fields.
What I really want to do is count the records, but cannot get the
count or Dcount to work.
Thanks for any help
Roger
Click to expand...

Please open your Query in SQL view and post the SQL text here. IS NOT
NULL will indeed return only those records where the field is not
null; however, is it possible that you have a Text field with Allow
Zero Length set to true? You may have some fields containing "" -
which is NOT the same as NULL.

If you'll explain what you mean by "cannot get the count or Dcount to
work" with a specific example someone might be able to help.

John W. Vinson[MVP]
 
I think the problem is that it should be "Not Is Null" instead of "Is Not
Null" ???
 
John
Here is the sql statement.

SELECT Bingo.ID, Bingo.Date, Bingo.AccountName, Bingo.TicketSupplier,
Bingo.CharityName, Bingo.CheckNumber, Bingo.AmountofExpense,
Bingo.PaidForTickets, Bingo.[PaidTo50%Charity], Bingo.[PaidTo5%Charity],
Bingo.TicketName, Bingo.SerialNumber, Bingo.CostPerBag,
IIf([AmountPaidOut]>0,Round([Bingo]![CostPerBag]*1.0675,2)) AS
[CostPerBagW/Tax], Bingo.TicketsPerBag, Bingo.PayOutPerBag,
Bingo.IdealProfit, IIf([AmountPaidOut]>0,[TicketsPerBag]-[TicketsPulled],0)
AS [Tickets Sold], Bingo.AmountPaidOut, Bingo.TicketsPulled,
Bingo.TicketsShort,
IIf([AmountPaidOut]>0,[TicketsPerBag]-[AmountPaidOut]-[TicketsPulled]-[TicketsShort],0)
AS AmountTurnedIn2, Bingo.WeeklyTurnIn, Bingo.AuditorAdjustment,
IIf([AmountPaidOut]>0,[Tickets Sold]-[AmountPaidOut]-[CostPerBagW/Tax],0) AS
NetProfit, IIf([AmountPaidOut]>0,[Tickets Sold]-[AmountPaidOut],0) AS
GrossProfit,
([AmountTurnedIn]-[AmountofExpense]-[PaidForTickets]-[PaidTo50%Charity]-[PaidTo5%Charity]+[AuditorAdjustment])
AS Balance, Bingo.AmountTurnedIn, [NetProfit]*0.05 AS 5Charity,
Bingo.WeeklyTurnIn, Bingo.InvoiceNumber,
IIf([AmountTurnedIn]>0,[AmountTurnedIn],"") AS CountTurnedIn
FROM Bingo
WHERE (((Bingo.TicketName) Is Not Null))
ORDER BY Bingo.Date;

I need the other info for a report, but I want to count the ticket names, thus
the is not null request
Thanks for any help

Roger
 
I think the problem is that it should be "Not Is Null" instead of "Is Not
Null" ???
Click to expand...

No. That's not the problem. [Fieldname] IS NOT NULL is the correct
syntax to find records where [Fieldname] contains a non-NULL value.

John W. Vinson[MVP]
 
Thanks Guys
Apparently the field was not null
I created a new column with if statement with the
false being null. It now works perfectly ( I Think )
Thanks again
Roger

John Vinson said:
I think the problem is that it should be "Not Is Null" instead of "Is Not
Null" ???
Click to expand...

No. That's not the problem. [Fieldname] IS NOT NULL is the correct
syntax to find records where [Fieldname] contains a non-NULL value.

John W. Vinson[MVP]
Click to expand...
 
What about John's suggestion that the field may contain an empty string?
What if you change your criteria to ...

WHERE Bingo.TicketName IS NOT NULL And Bingo.TicketName <> ""

.... that's two double quotes with nothing (not even a space) between them at
the end.

Does that make a difference?

--
Brendan Reynolds (MVP)


XeniaEagle said:
John
Here is the sql statement.

SELECT Bingo.ID, Bingo.Date, Bingo.AccountName, Bingo.TicketSupplier,
Bingo.CharityName, Bingo.CheckNumber, Bingo.AmountofExpense,
Bingo.PaidForTickets, Bingo.[PaidTo50%Charity], Bingo.[PaidTo5%Charity],
Bingo.TicketName, Bingo.SerialNumber, Bingo.CostPerBag,
IIf([AmountPaidOut]>0,Round([Bingo]![CostPerBag]*1.0675,2)) AS
[CostPerBagW/Tax], Bingo.TicketsPerBag, Bingo.PayOutPerBag,
Bingo.IdealProfit,
IIf([AmountPaidOut]>0,[TicketsPerBag]-[TicketsPulled],0)
AS [Tickets Sold], Bingo.AmountPaidOut, Bingo.TicketsPulled,
Bingo.TicketsShort,
IIf([AmountPaidOut]>0,[TicketsPerBag]-[AmountPaidOut]-[TicketsPulled]-[TicketsShort],0)
AS AmountTurnedIn2, Bingo.WeeklyTurnIn, Bingo.AuditorAdjustment,
IIf([AmountPaidOut]>0,[Tickets Sold]-[AmountPaidOut]-[CostPerBagW/Tax],0)
AS
NetProfit, IIf([AmountPaidOut]>0,[Tickets Sold]-[AmountPaidOut],0) AS
GrossProfit,
([AmountTurnedIn]-[AmountofExpense]-[PaidForTickets]-[PaidTo50%Charity]-[PaidTo5%Charity]+[AuditorAdjustment])
AS Balance, Bingo.AmountTurnedIn, [NetProfit]*0.05 AS 5Charity,
Bingo.WeeklyTurnIn, Bingo.InvoiceNumber,
IIf([AmountTurnedIn]>0,[AmountTurnedIn],"") AS CountTurnedIn
FROM Bingo
WHERE (((Bingo.TicketName) Is Not Null))
ORDER BY Bingo.Date;

I need the other info for a report, but I want to count the ticket names,
thus
the is not null request
Thanks for any help

Roger



John Vinson said:
Please open your Query in SQL view and post the SQL text here. IS NOT
NULL will indeed return only those records where the field is not
null; however, is it possible that you have a Text field with Allow
Zero Length set to true? You may have some fields containing "" -
which is NOT the same as NULL.

If you'll explain what you mean by "cannot get the count or Dcount to
work" with a specific example someone might be able to help.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Back
Top