|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 9th, 2008, 03:49 PM
posted to microsoft.public.access.queries
|
|||
|
|||
How do I filter data by blank cells in access
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 
|
|
#2
May 9th, 2008, 04:18 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
How do I filter data by blank cells in access
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 "Jason" wrote: 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
|
|
#3
May 9th, 2008, 04:18 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
How do I filter data by blank cells in access
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... -- NTC "Jason" wrote: 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
|
|
#4
May 9th, 2008, 04:28 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
How do I filter data by blank cells in access
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" wrote: 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 "Jason" wrote: 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
|
|
#5
May 9th, 2008, 04:47 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
How do I filter data by blank cells in access
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 "Jason" wrote: 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" wrote: 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 "Jason" wrote: 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
|
|
#6
May 9th, 2008, 04:59 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
How do I filter data by blank cells in access
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 "KARL DEWEY" wrote: 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 "Jason" wrote: 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" wrote: 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 "Jason" wrote: 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
|
|
#7
May 9th, 2008, 06:16 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
How do I filter data by blank cells in access
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 "Jason" wrote: 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 "KARL DEWEY" wrote: 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 "Jason" wrote: 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" wrote: 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 "Jason" wrote: 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
|
|
#8
May 9th, 2008, 08:23 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
How do I filter data by blank cells in access
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 "Jason" wrote: 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
|
|
#9
May 9th, 2008, 09:17 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
How do I filter data by blank cells in access
On Fri, 9 May 2008 12:23:01 -0700, Jason
wrote: 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 "Jason" wrote: 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 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!) -- John W. Vinson [MVP]
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
