matching dates from imported *.dbf file?
 

HI all,
I have a simple WS of 3 columns date,custnum,invnum that has dates over past
5 years. I simply want to have user input the date desired and get
corresponding data to another WS. I'm sure there are many ways to do this
and I thought I'd try working w/ Advanced filter.
My problem is that I can't get my user input date to match up with anything
in the imported database date. I tried all kinds of formatting and even
tried copying a cell from the import data and paste it into the user data
cell. It won't match up even with itself!

Could you explain to me what I am missing?

--
jeff

matching dates from imported *.dbf file?
 

Sounds like your dates from the dbf are being interpreted as Text. assume
the first date is in A2. In any cell put in the formula

=istext(A2)

If it returns true, then this is you problem. Select the column and format
it as a data format. Now do data=TexttoColumns, select delimited and on
the next dialog, make sure the delimiter comma and nothing else (or any
delimiter not in the column). this will cause Excel to reevaluate the dates
and convert them to dates. Your istext formula should then show false.

When you copy and paste the data to another location, Excel probably
converted it to dates, so the problem is exacerbated.

Another way to convert would be to select the column and do

Edit=Replace
what: \
With: \

this assumes the dates have a \ in them.

--
regards,
Tom Ogilvy


"jeffP" wrote in message
...
HI all,
I have a simple WS of 3 columns date,custnum,invnum that has dates over
past
5 years. I simply want to have user input the date desired and get
corresponding data to another WS. I'm sure there are many ways to do this
and I thought I'd try working w/ Advanced filter.
My problem is that I can't get my user input date to match up with
anything
in the imported database date. I tried all kinds of formatting and even
tried copying a cell from the import data and paste it into the user data
cell. It won't match up even with itself!

Could you explain to me what I am missing?

--
jeff

matching dates from imported *.dbf file?
 

Tom,
This information is good, and I'm learning but it still didn't help this
situation.Istext is false on all columns of data. And it's not just dates. I
tried to use the advanced filter with the custnum column (straight 4 digit
numeric code) and it just brings the entire data list without filtering
anything out.
I'm lost here so any help or ideas would be appreciated.

--
jeffP



"Tom Ogilvy" wrote in message
...
Sounds like your dates from the dbf are being interpreted as Text.
assume
the first date is in A2. In any cell put in the formula

=istext(A2)

If it returns true, then this is you problem. Select the column and
format
it as a data format. Now do data=TexttoColumns, select delimited and on
the next dialog, make sure the delimiter comma and nothing else (or any
delimiter not in the column). this will cause Excel to reevaluate the
dates
and convert them to dates. Your istext formula should then show false.

When you copy and paste the data to another location, Excel probably
converted it to dates, so the problem is exacerbated.

Another way to convert would be to select the column and do

Edit=Replace
what: \
With: \

this assumes the dates have a \ in them.

--
regards,
Tom Ogilvy


"jeffP" wrote in message
...
HI all,
I have a simple WS of 3 columns date,custnum,invnum that has dates over
past
5 years. I simply want to have user input the date desired and get
corresponding data to another WS. I'm sure there are many ways to do
this
and I thought I'd try working w/ Advanced filter.
My problem is that I can't get my user input date to match up with
anything
in the imported database date. I tried all kinds of formatting and even
tried copying a cell from the import data and paste it into the user dat
a
cell. It won't match up even with itself!

Could you explain to me what I am missing?

--
jeff

matching dates from imported *.dbf file?
 

Try using the autofilter.

--
Regards,
Tom Ogilvy

"jeffP" wrote in message
...
Tom,
This information is good, and I'm learning but it still didn't help this
situation.Istext is false on all columns of data. And it's not just dates.
I
tried to use the advanced filter with the custnum column (straight 4 digit
numeric code) and it just brings the entire data list without filtering
anything out.
I'm lost here so any help or ideas would be appreciated.

--
jeffP



"Tom Ogilvy" wrote in message
...
Sounds like your dates from the dbf are being interpreted as Text.
assume
the first date is in A2. In any cell put in the formula

=istext(A2)

If it returns true, then this is you problem. Select the column and
format
it as a data format. Now do data=TexttoColumns, select delimited and
on
the next dialog, make sure the delimiter comma and nothing else (or any
delimiter not in the column). this will cause Excel to reevaluate the
dates
and convert them to dates. Your istext formula should then show false.

When you copy and paste the data to another location, Excel probably
converted it to dates, so the problem is exacerbated.

Another way to convert would be to select the column and do

Edit=Replace
what: \
With: \

this assumes the dates have a \ in them.

--
regards,
Tom Ogilvy


"jeffP" wrote in message
...
HI all,
I have a simple WS of 3 columns date,custnum,invnum that has dates
over
past
5 years. I simply want to have user input the date desired and get
corresponding data to another WS. I'm sure there are many ways to do
this
and I thought I'd try working w/ Advanced filter.
My problem is that I can't get my user input date to match up with
anything
in the imported database date. I tried all kinds of formatting and
even
tried copying a cell from the import data and paste it into the user
dat
a
cell. It won't match up even with itself!

Could you explain to me what I am missing?

--
jeff

matching dates from imported *.dbf file?
 

Tom,
The auto filter does work and I guess that's the way I have to go to get
this project done. I was trying to do the entire thing programmatically
(loop an If compare) and eliminate users needing to use the autofilter.
Plus I got stubborn and intrigued as to why I can't compare to this former
*dbf. Oh well. Thanks for your help and if you have any other ideas I
really am curious as to why this won't work.

--
jeffP


"Tom Ogilvy" wrote in message
...
Try using the autofilter.

--
Regards,
Tom Ogilvy

"jeffP" wrote in message
...
Tom,
This information is good, and I'm learning but it still didn't help this
situation.Istext is false on all columns of data. And it's not just
dates.
I
tried to use the advanced filter with the custnum column (straight 4
digit
numeric code) and it just brings the entire data list without filtering
anything out.
I'm lost here so any help or ideas would be appreciated.

--
jeffP



"Tom Ogilvy" wrote in message
...
Sounds like your dates from the dbf are being interpreted as Text.
assume
the first date is in A2. In any cell put in the formula

=istext(A2)

If it returns true, then this is you problem. Select the column and
format
it as a data format. Now do data=TexttoColumns, select delimited and
on
the next dialog, make sure the delimiter comma and nothing else (or
any
delimiter not in the column). this will cause Excel to reevaluate the
dates
and convert them to dates. Your istext formula should then show
false.

When you copy and paste the data to another location, Excel probably
converted it to dates, so the problem is exacerbated.

Another way to convert would be to select the column and do

Edit=Replace
what: \
With: \

this assumes the dates have a \ in them.

--
regards,
Tom Ogilvy


"jeffP" wrote in message
...
HI all,
I have a simple WS of 3 columns date,custnum,invnum that has dates
over
past
5 years. I simply want to have user input the date desired and get
corresponding data to another WS. I'm sure there are many ways to do
this
and I thought I'd try working w/ Advanced filter.
My problem is that I can't get my user input date to match up with
anything
in the imported database date. I tried all kinds of formatting and
even
tried copying a cell from the import data and paste it into the user
dat
a
cell. It won't match up even with itself!

Could you explain to me what I am missing?

--
jeff

matching dates from imported *.dbf file?
 

You can manage an autofilter with code, same as an advanced filter. Turn on
the macro recorder and do it manually to see the basic syntax.

--
Regards,
Tom Ogilvy


"jeffP" wrote in message
...
Tom,
The auto filter does work and I guess that's the way I have to go to get
this project done. I was trying to do the entire thing programmatically
(loop an If compare) and eliminate users needing to use the autofilter.
Plus I got stubborn and intrigued as to why I can't compare to this former
*dbf. Oh well. Thanks for your help and if you have any other ideas I
really am curious as to why this won't work.

--
jeffP


"Tom Ogilvy" wrote in message
...
Try using the autofilter.

--
Regards,
Tom Ogilvy

"jeffP" wrote in message
...
Tom,
This information is good, and I'm learning but it still didn't help
this
situation.Istext is false on all columns of data. And it's not just
dates.
I
tried to use the advanced filter with the custnum column (straight 4
digit
numeric code) and it just brings the entire data list without
filtering
anything out.
I'm lost here so any help or ideas would be appreciated.

--
jeffP



"Tom Ogilvy" wrote in message
...
Sounds like your dates from the dbf are being interpreted as Text.
assume
the first date is in A2. In any cell put in the formula

=istext(A2)

If it returns true, then this is you problem. Select the column
and
format
it as a data format. Now do data=TexttoColumns, select delimited
and
on
the next dialog, make sure the delimiter comma and nothing else (or
any
delimiter not in the column). this will cause Excel to reevaluate
the
dates
and convert them to dates. Your istext formula should then show
false.

When you copy and paste the data to another location, Excel probably
converted it to dates, so the problem is exacerbated.

Another way to convert would be to select the column and do

Edit=Replace
what: \
With: \

this assumes the dates have a \ in them.

--
regards,
Tom Ogilvy


"jeffP" wrote in message
...
HI all,
I have a simple WS of 3 columns date,custnum,invnum that has dates
over
past
5 years. I simply want to have user input the date desired and get
corresponding data to another WS. I'm sure there are many ways to
do
this
and I thought I'd try working w/ Advanced filter.
My problem is that I can't get my user input date to match up with
anything
in the imported database date. I tried all kinds of formatting and
even
tried copying a cell from the import data and paste it into the
user
dat
a
cell. It won't match up even with itself!

Could you explain to me what I am missing?

--
jeff