INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

(OP)
Hello,

I know this is simple, but would like to double check with the experts. I need to remove records from a dbf and only keep what those that were entered on a specific date.

Thank you

RE: How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

You MUST have a date field for doing this.

ldDate = date()
Delete from yourtable where yourdatefield<ldDate 

This will not remove the records, only mark them as deleted. To remove the records physically, you must Use the table Exclusive after my code, and then Pack it.

Also you must Set Deleted On, which in my opinion always should be the default.

NB! Please change your user id into something which hints about your real name, it will make your presence here look more professional. Only trolls use a fake id.

RE: How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

Quote:

Only trolls use a fake id.

Really? First I've heard of it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

As Tore rightly said, you need a field in your table that holds a date. But you will also need to update that field each time you append a new record.

So, in the code that appends the record, do this:

REPLACE YourDateField WITH DATE()

Then use the DELETE command as per Tore's post.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

Yes, and if you follow every thread here you could have seen webuxer several times recently (since September 2016).

It's true the norm at least in the Foxpro section is to have a user handle with the real name. There are some exceptions from the rule and it is surely not a site rule. Even in some private invite only forums, several members don't have a real name handle.

Back on topic: As Tore Bleken already said, your DBF needs a date column to be able to filter this, there is no hidden data about the creation or update times of records in DBF files, there is no automatic transaction log, so you will have to have such a column. To let it contain the date/datetime of creation automatically, it needs to be a DBC table asa that allows default values. You then simply define a date column with default value DATE() or a datetime column with default DATETIME(). You need to do as Mike says for DBFs without default values as feature, i.e. for free and fox3x tables.

And then you have a basis not only for querying data in a given time span, but also deleting data from a time span or except a timespan, that's merely the negated condition.

I repeat what's already said, albeit going a bit into detail about prerequisites and reasons. Everything Tore said technically already remains true, too.

Bye, Olaf.

RE: How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

(OP)
Thank you all for the help, I really appreciate it. By the way, I will change my username to display my real name.

Thank you

RE: How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

Quote:

I will change my username to display my real name.

That's always helpful, Luiz. Thanks.

And I see that Tore has done the same (which is particularly appropriate).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

I just also did add the space, the "Display Name" in your profile section might already exist for years, but as far as I remember didn't exist when I joined in 2004. To me it's sufficient to know a name even just by the signature.

Bye, Olaf.

RE: How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

Same here. When I joined in 2004 the login name had to be without spaces, and the login name was the same as the display name.

RE: How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

To prevent the need for PACK, you could also change your way to create new records this way:

CODE

SET DELETED OFF
LOCATE FOR DELETED()
IF FOUND()
   RECALL
   BLANK DEFAULT AUTOTINC
ELSE
   APPEND BLANK
ENDIF
SET DELETED ON 
That intentionally does not reset DELETED to what it was before but forces the default of deleted being ON after the record recycling. It's generally a setting you only want OFF, when you need to detect and locate deleted rows. The need for that is - without trying to find a more diplomatic euphemism - ridiculous, as any normal database will not keep deleted records, your code shouldn't need access to them in any place but for recycling these DBF file bytes.

A benefit of a PACK once in a while remains. It is useful to get rid of memo bloat and recreate the index trees cleanly (notice index nodes on deleted rows also remain in the CDX file), the RECALL only reuses the RecSize() bytes of the deleted row (of course it also reactivates any referenced memo file section before you BLANK, but that's an unimportant fact). This way of recycling can reduce the frequency of PACKs necessary.

Bye, Olaf.

RE: How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

All that said, a simple way to clean a table for daily data is to ZAP it at start of a day, so it only collects data from today. Eg in initialisation locate a record of a previous date, and if you find one, ZAP the table.

Bye, Olaf.

RE: How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

I've also now added a space: a tiny change that makes a useful difference.

It's so much more satisfactory when you can address people by their name, rather than having to use an arbitrary sequence of letters and digits.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

Hi Tore and or Mike,

Where is the option to change your displayname in this forum?
Mind you I only want to change the displayname, not my login+password.
Thanks for replying.
Jockey2 (Koen)

RE: How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

Hi Koen,

Look for the My Stuff dropdown at the top of the page. Select My Profile. You will see a form with various fields that you ca edit.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17

Mike,

Thanks, so I donot have to hide behind a horse anymore :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close