How do I filter data by blank cells in access

[Jason]

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

 

[NetworkTrade]

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...

 

[KARL DEWEY]

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.
Post your actual syntax.

 

[Jason]

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.

 

[KARL DEWEY]

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], "/")

 

[Jason]

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
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], "/")

--
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
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.

 

[KARL DEWEY]

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.

--
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
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
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], "/")

--
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
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.
A DateTime datatype field can not have text.

I have tried putting or ("") and a few variations of that.
Post your actual syntax.
--
KARL DEWEY
Build a little - Test a little


:

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

 

[Jason]

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

 

[John W. Vinson]

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

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!)