query criteria expression for blanks

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

Guest

What query criteria expression would display all records in a particular
column with blank fields?

The database I'm working with is a pharmacy medication record. The query is
used for the purposes of ordering refills. I've got everything except how to
exclude prescriptions that have been discontinued. In the [Discontinued
Date] criteria I've tried entering Is Null, =0, =" ", etc. However, there is
one pesky little record with a 4/28/06 discontinued date that keeps showing
up, even when I used the criteria <>#4/28/2006#. Any ideas?
 
In the [Discontinued Date] criteria I've tried entering Is Null, =0, =" ",
etc.
Is the field datatype DateTime or a text field? A text field can contain
null, zero, or zero lenght. It can not have " " as Access removes any
trailing spaces. Criteria for zero lenght ="" with no space between quotes.
If you have a text field the data in this record could have a leading space.

Mike said:
What query criteria expression would display all records in a particular
column with blank fields?

The database I'm working with is a pharmacy medication record. The query is
used for the purposes of ordering refills. I've got everything except how to
exclude prescriptions that have been discontinued. In the [Discontinued
Date] criteria I've tried entering Is Null, =0, =" ", etc. However, there is
one pesky little record with a 4/28/06 discontinued date that keeps showing
up, even when I used the criteria <>#4/28/2006#. Any ideas?
Click to expand...
 
Is this a date/time field? If so, I'm betting that record is formatted to
show just the date; however, it also has a time component in it. Your Where
clause won't catch it.

SELECT [Discontinued Date], Format([Discontinued Date], "General Date")
FROM YourTable
WHERE [Discontinued Date] >= #4/28/2006#
AND [Discontinued Date] < #4/29/2006# ;
 
It's possible that you have a corrupt index on that field. If that field is
indexed, then read on.

If you are using the criteria of "Is Null" and that is working for all the
other records, then I would suspect that is the problem.

First thing, I would try would be to temporary change the date in that
record and see if that makes a difference. IF so, then I would try changing
the date back.

If that doesn't work, try a searching against a calculated field
Field: Len(Trim([Discontinued Date] & ""))
Criteria: 0

If that works to give you correct results, then I would definitely suspect a
corrupt index.

Steps I would try next AFTER making a backup of the database.
Delete the index
Compact the database
Create the index

If that fails then
Make a copy of the table
Delete the original table
Compact the database
Rename the copy to the original table name
Compact the database
 
Is the field datatype DateTime or a text field? A text field can contain
null, zero, or zero lenght. It can not have " " as Access removes any
trailing spaces. Criteria for zero lenght ="" with no space between quotes.
Click to expand...

It is a DateTime field, but ="" (with no space) brought up the error
message: "Data type mismatch in criteria expression"
If you have a text field the data in this record could have a leading space.
Click to expand...

I've tried deleting and reentering the entire record, as well as just the
date several times and it didn't work. Thanks for the reply though. I
appreciate it.
 
SELECT [Discontinued Date], Format([Discontinued Date], "General Date")
FROM YourTable
WHERE [Discontinued Date] >= #4/28/2006#
AND [Discontinued Date] < #4/29/2006# ;
Click to expand...

This would be entered under criteria right? I'm not familiar with the
operators, but couldn't it be formatted in the table instead of using an
expression? Also, the "Where / And" bit looks a little too specific. It
might weed that one record out, but I need something more general that will
weed out all records that have any kind of entry in [Discontinued Date] .
Maybe I'm not understanding what you're suggesting though.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Mike said:
What query criteria expression would display all records in a particular
column with blank fields?

The database I'm working with is a pharmacy medication record. The query is
used for the purposes of ordering refills. I've got everything except how to
exclude prescriptions that have been discontinued. In the [Discontinued
Date] criteria I've tried entering Is Null, =0, =" ", etc. However, there is
one pesky little record with a 4/28/06 discontinued date that keeps showing
up, even when I used the criteria <>#4/28/2006#. Any ideas?
Click to expand...
Click to expand...
 
If that field is
indexed, then read on.
Click to expand...

It's not. ...thank God! It would take me all day to follow those
directions...mostly because I don't know how to "Compact the database". Care
to explain for future reference?
 
Open the database in design mode
Select Tools: Database Utilities: Compact and Repair from the menu
 
Back
Top