I have coded a form for users to do filtering with on data that I pull
from an SQL Server database. I have a field "Invoice Number" which is a
varchar(255) column in SQL Server.

I have a criteria range with the column headings in the first row and
the cells below to fill with the criteria.

Invoice Number often contains numbers with leading zeros, for example
0005962. If I set my criteria for Invoice Number to 0005962 and apply my
advanced filter, I get the row with invoice number 0005962 as expected.

However, if I set my criteria to <>0005962 and apply my advanced filter,
instead of all the rows except that invoice number, I get all rows.

I have tried <>'0005962, <>"0005962", <>"'0005962" - no success.

What has worked is <>0005962*

I have checked the original data and there are no trailing spaces in the
data.

Why does the * work when nothing else does? I'm not keen on using a
wildcard if it is not necessary; is there a way to set my criteria in
this instance without using the wildcard?

Thanks,
Dianne

have you tried recording a macro while you're doing the filter manually? then
you can look at the code and see what the criteria is.

--


Gary


"Dianne Butterworth" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have coded a form for users to do filtering with on data that I pull from an
>SQL Server database. I have a field "Invoice Number" which is a varchar(255)
>column in SQL Server.
>
> I have a criteria range with the column headings in the first row and the
> cells below to fill with the criteria.
>
> Invoice Number often contains numbers with leading zeros, for example 0005962.
> If I set my criteria for Invoice Number to 0005962 and apply my advanced
> filter, I get the row with invoice number 0005962 as expected.
>
> However, if I set my criteria to <>0005962 and apply my advanced filter,
> instead of all the rows except that invoice number, I get all rows.
>
> I have tried <>'0005962, <>"0005962", <>"'0005962" - no success.
>
> What has worked is <>0005962*
>
> I have checked the original data and there are no trailing spaces in the data.
>
> Why does the * work when nothing else does? I'm not keen on using a wildcard
> if it is not necessary; is there a way to set my criteria in this instance
> without using the wildcard?
>
> Thanks,
> Dianne