How do I filter data by blank cells in access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jason - 09 May 2008 15:49 GMT

I am new to this so be easy on me, please.
I have linked to an ODBC table, and a few excel sheets. When I go to query
I want to filter data by a collum that has a date, if it has a date I don't
want to show that data when I export the report - this works fine with an "if
null" statement. Now I want to filter that same data but I don't want to
show it if it either has a date or text. I have tried putting or ("") and a
few variations of that. Nothing has worked, it either doesn't export it
correctly or it will stop in the middle of the export saying it is the
"improper data type" or something to that effect. Please help. Thanks in
advance

Reply to this Message

NetworkTrade - 09 May 2008 16:18 GMT

if you have the date portion working ok; then trial just the text portion by
itself:

If srtText = "" Or IsNull(srtText) Then

this will check for both nulls and empty fields....

you should get this much working ok by itself before adding the the data
field portion with another OR statement...

Signature

NTC

> I am new to this so be easy on me, please.
> I have linked to an ODBC table, and a few excel sheets. When I go to query
[quoted text clipped - 6 lines]
> "improper data type" or something to that effect. Please help. Thanks in
> advance

Reply to this Message

KARL DEWEY - 09 May 2008 16:18 GMT

>>Now I want to filter that same data but I don't want to show it if it

either has a date or text.
A DateTime datatype field can not have text.

>> I have tried putting or ("") and a few variations of that.

Post your actual syntax.

Signature

KARL DEWEY
Build a little - Test a little

Reply to this Message

Jason - 09 May 2008 16:28 GMT

In excel I have a cell that is of the 'date' format. When I link to that
file in access I can sort by the criteria 'is null' and it will filter out
all of the records that have a date in this cell. If it has a text value it
will not filter it out, but I need it to. Right now my actual syntax is 'is
null' in the criteria field, but I have tried 'is null or ("")' and I have
tried just '("")' thinking that it would only return records that are totally
blank - but it would give me the error I described earlier. Hope this
explains it better. Thanks for the replies.

> >>Now I want to filter that same data but I don't want to show it if it
> either has a date or text.
[quoted text clipped - 13 lines]
> > "improper data type" or something to that effect. Please help. Thanks in
> > advance

Reply to this Message

KARL DEWEY - 09 May 2008 16:47 GMT

Access will see the Excel column either as a date (in that case any text will
be an error) or text. If Access sees the column as text then you can not
perform date functions without converting it to a date like DateSerial.
So what does your 'dates' have that can be distinguished from 'text'? Do
they have slashes or dashes?
Use a calculated field like Test_for_Dash: InStr([YourField], "-") with
criteria >0 to indicate a dash.
Test_for_Slash: InStr([YourField], "/")

Signature

KARL DEWEY
Build a little - Test a little

> In excel I have a cell that is of the 'date' format. When I link to that
> file in access I can sort by the criteria 'is null' and it will filter out
[quoted text clipped - 22 lines]
> > > "improper data type" or something to that effect. Please help. Thanks in
> > > advance

Reply to this Message

Jason - 09 May 2008 16:59 GMT

That looks like it would work, could you explain exactly where to put what
command - I know it seems like I don't know what I am doing, but that is only
because I don't! Thanks again

> Access will see the Excel column either as a date (in that case any text will
> be an error) or text. If Access sees the column as text then you can not
[quoted text clipped - 31 lines]
> > > > "improper data type" or something to that effect. Please help. Thanks in
> > > > advance

Reply to this Message

KARL DEWEY - 09 May 2008 18:16 GMT

Open your query in design view. Scroll to the right until you see a blank
column. Paste Test_for_Dash: InStr([YourField], "-") substituting your
field name. In the next blank column paste Test_for_Slash:
InStr([YourField], "/") again substituting your field name. In the
criteria row of the grid type >0 under the first new column. Drop down
a row and repeat entry.

Signature

KARL DEWEY
Build a little - Test a little

> That looks like it would work, could you explain exactly where to put what
> command - I know it seems like I don't know what I am doing, but that is only
[quoted text clipped - 35 lines]
> > > > > "improper data type" or something to that effect. Please help. Thanks in
> > > > > advance

Reply to this Message

Jason - 09 May 2008 20:23 GMT

I tried that and couldn't get it to work - it says 'syntax error (comma) in
expression 'my query'' I have a screen shot if i could post it somewhere.
Thanks again for your help, sorry to be a bother

Reply to this Message

John W. Vinson - 09 May 2008 21:17 GMT

>I tried that and couldn't get it to work - it says 'syntax error (comma) in
>expression 'my query'' I have a screen shot if i could post it somewhere.
[quoted text clipped - 10 lines]
>> "improper data type" or something to that effect. Please help. Thanks in
>> advance

Try using a criterion of

IS NULL

or

IS NOT NULL

on the field, as appropriate. IIf is *not* appropriate in this case, and the
zero length string "" is not the same as NULL, and is inappropriate for
date/time data in any case.

Rather than posting a screen shot of a query, use View... SQL in the menu and
post the SQL text. That's the *real* query, and the folks who answer here can
read it easily (more easily than a screenshot often!)

Signature

John W. Vinson [MVP]

Reply to this Message

Rate this thread: