Re: DCOUNT problem with numbers stored as text

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks again Peo that seems to work as well, though it sounds like
SUMPRODUCT is my best option.

On Jun 4, 12:24 pm, "Peo Sjoblom" <terr...@xxxxxxxx> wrote:
It will filter out 111 as well, you can clear the header in the criteria and
use the
formula Lori provided

=A2<>"11"

where A2 is the first cell with data in the code range

so if you previously had

Code
<>11*

in let's say F1:F2, leave F1 blank and put

=A2<>"11"

in F2

There are other drawback using the D functions, for instance they will not
work if the workbook is closed whereas SUMPRODUCT will work

"MJP" <mpacif...@xxxxxxxxx> wrote in message

news:1180973382.771842.284250@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



Lori,

Changing my criteria to <>11* seemed to work as well at first glance.
What exactly is that doing? If I have data values such as 111, or
11A, etc in my code column is it going to filter them out as well?

Thanks.

On Jun 4, 12:01 pm, Lori <lorimer_mil...@xxxxxxxxxxx> wrote:
Interesting problem. To force a text comparison, you could try using
two criteria:

Code
<>11*
11?*

Repeat any other criteria on the second line and also make sure to use
DCOUNTA for text values. Alternatively use a calculated criteria e.g.
=A2<>"11" although it will run slower.

On 4 Jun, 16:26, MJP <mpacif...@xxxxxxxxx> wrote:

I don't think SUMPRODUCT will accomplish what I'm looking for.

Maybe I oversimplified my problem. There are many other columns in my
raw data worksheet. My DCOUNT functions use many of these columns in
order to get a count of the rows that fall under its criteria.

For example the criteria might be:

Month
=8

Code
<>11

Code
<>K5

Sex
=M

All parts of the criteria seem to work except it is not filtering out
the rows where the Code column equals 11 stored as text. It works if
I format such cells as numeric, but again this is not an option when I
am programatically populating the raw data.

Thanks.

On Jun 4, 11:10 am, "Peo Sjoblom" <terr...@xxxxxxxx> wrote:

You can use sumproduct instead

=SUMPRODUCT(--(Range<>"11"))

--
Regards,

Peo Sjoblom

"MJP" <mpacif...@xxxxxxxxx> wrote in message

news:1180969282.579326.156420@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I have a raw data worksheet that I populate using the OLEDB Jet
provider. Other worksheets reference the raw data and contain
functions to perform various counts of the data.

One column in the raw data contains both textual values (i.e. "K5")
and numeric values (ie. 11). Because I am using the Jet provider,
the
only way I have found to avoid data format errors on import is to
pre-
format the column as General or Text. So, the values such as 11
are
actually numbers stored as text (I look in the function bar and see
the value prepended with an apostrophe: '11).

One of the other worksheets references this column in a DCOUNT
function and is looking for a count where this column is not equal
to
11. (<>11). However it's not filtering out the values of 11
because
they are numbers formatted as text. I have seemingly tried
everything
to try and do a string comparison but no luck. <>"11", <>'11, etc
nothing works.

How can I get this function to work properly on numbers stored as
text
without having to change the format of the raw cells to text
(doesn't
seem to be an option with columns of mixed types using the Jet
provider)?

Thanks.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -


.

Relevant Pages

  • Re: DCOUNT problem with numbers stored as text
    ... the rows where the Code column equals 11 stored as text. ... I format such cells as numeric, but again this is not an option when I ... am programatically populating the raw data. ... Because I am using the Jet provider, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: DCOUNT problem with numbers stored as text
    ... Other worksheets reference the raw data and contain ... One column in the raw data contains both textual values ... Because I am using the Jet provider, ... format the column as General or Text. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: DCOUNT problem with numbers stored as text
    ... raw data worksheet. ... I format such cells as numeric, but again this is not an option when I ... am programatically populating the raw data. ... Because I am using the Jet provider, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: DCOUNT problem with numbers stored as text
    ... Changing my criteria to 11* seemed to work as well at first glance. ... I format such cells as numeric, but again this is not an option when I ... am programatically populating the raw data. ... Because I am using the Jet provider, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: formatting dates in queries
    ... class and can't be called directly without using reflection. ... to get the appropriate factory object for creating a specific connection ... Anyone have any good guidance on how to decide at runtime which format ... vendor writes a NET 2.0 data provider that I don't have), ...
    (microsoft.public.dotnet.framework.adonet)