Opening tables with invalid dates results in blank date fields.

[TruxMan]

Hello Everyone,
I need some help. I'm writting a utility to validate the dates in our tables and when I open the tables in FoxPro I will not see dates that are invalid, I will only see blank date fields. For example, I have some dates that have /-1/
in them and I can see this with Quick View plus but not in FoxPro. Is there anyway I can open the tables for my utility without FoxPro showing me blank dates? (by the way if I open the tables afterwards with Quick View plus the invalid dates are still in there.)

 

[foxdev]

If I understand you correctly, you could get a list of invalid dates this way:

[tt]select * from MyTable where (not empty(MyDateCol)) and dow(MyDateCol) < 1[/tt]


Robert Bradley

http://www.foxdev.com/

 

[TruxMan]

Hey FoxDev,
The problem is that FoxPro seems to be interpreting the dates while it is opening the tables therefore FoxPro sees the date fields as empty. It is not just a matter of how its displayed it just seems that FoxPro see it as invalid and counts it as blank. Using Empty() will return .t. because it sees it as empty. Thank you for the quick response and I would really appreciate any more ideas. I'm really stumped and my boss is waiting for this.

 

[Creeder]

Hi TruxMan,
I am not sure if this will work, but could you do a
REPLACE command for the invalid date columns with .NULL.
values.

Hope this helps.

Yue Jeen

 

[foxdev]

Yue Jeen, that's a good point, one I hadn't thought of. But I'm guessing that TuxMan is looking to see which records have a bad date, rather than trying to fix the bad dates...is that right, TuxMan?

If so, and EMPTY(MYDATECOL) is yielding .T. even though the raw file has something like -1 in it, the only other way I can think to do it is outside of VFP. You'd have to open the table using low-level file functions (FOPEN, etc) and look at the bytes that make up each date column. Not a real difficult thing, but maybe more than what you wanted.

Robert Bradley

http://www.foxdev.com/

 

[TruxMan]

Hey Guys,
Thanks for the suggestions. Creeder, Its funny that you mention the REPLACE command because that is what I use to correct the dates that are invalid. It will go through and leave the invalid fields with fields that have a .null. value, However my boss wants to see a report of what fields were corrected. My first thought was to use an in IF statement with EMPTY() however that is when I ran into the problem I'm having. So FoxDev, you think the file should be opened low level with functions like fopen(). Do you have any suggestions on how exactly I could accomplish this?
Thanks again for the fast responses. This is great!!

 

[TruxMan]

By the way I don't think we can use the fopen(). I'm a little affraid of destroying any of the structure of these tables. This is going to be customer data, not our data. So there isn't any room for error seeing as how its impossible to get people to back up their data. Anyway, Please let me know what you think and if you have any ideas.

 

[foxdev]

Is the table in a production/multiuser environment when you are doing the REPLACE? Can you copy the file before working on it?

FOPEN opens a file read-only by default. Basically, the technique I would use is:

* determine size of the DBF header (use Header())
* determine offset of desired column within a record
* FOPEN, FREAD past the header, then FREAD to the column position
* continue until nauseated

Seriously, probably about 30 lines of code, not counting error checking.

Robert Bradley

http://www.foxdev.com/

 

[DanWalter]

&quot;However my boss wants to see a report of what fields were corrected.&quot;

You could try adding a field called &quot;Fixed&quot; and make it logical depending if a date in the record was .null.ed out, or make it a text field and stick in the name of the field/fields that was/were fixed. If you use table buffering, you could tablerevert if the .null.ed dates were not replaced with a valid date....

Good luck!
Dan Dan Walter
DWalter@zoo.uvm.edu

 

[dgrewe]

Will this work, it will give you a before and after picture of the recrod that was changed
[tt]
close data
Use DBF2Check
copy stru to temp
use temp in 0
goto top
scan all
If any date field fails a vailid test
scatter memvars
insert into temp from memvars
replace fields in dbf2check with null or what ever date you want
scatter memvars
insert into temp from memvars
endif
endscan
select temp
if reccount() >0
report from whatever to print
endif
[/tt]


David W. Grewe
Dave@internationalbid.com
ICQ VFP ActiveList #46145644