Delete Query

[GLT]

Hi,

I am trying to create a delete query that deletes records older than a
specific date, the date is entered by a field on a form by the user. All
records prior to the date the user enters are removed.

Within that query, the date and time is part of the primary key field, so I
need to extract the date from the primary key field first.

I tried to build this query in the design grid, and when I try to execute
it, it says its too complex to be evaluated.

SELECT tbl01_FullCompare.RecID,
Format(Left((Right([RecID],14)),8),"dd/mm/yyyy") AS Dte
FROM tbl01_FullCompare
WHERE
(((Format(Left((Right([RecID],14)),8),"dd/mm/yyyy"))<[forms]![frm01_DeleteOldData]![fldDateLimit]));

Can anyone advise how to get this working?

Any assistance is greatly appreciated...

Cheers.

 

[John W. Vinson]

Hi,

I am trying to create a delete query that deletes records older than a
specific date, the date is entered by a field on a form by the user. All
records prior to the date the user enters are removed.

Within that query, the date and time is part of the primary key field, so I
need to extract the date from the primary key field first.

Well, that's one of many reasons one should NOT use composite fields in the
table at all, much less as part of a primary key... ouch!!!

I tried to build this query in the design grid, and when I try to execute
it, it says its too complex to be evaluated.

SELECT tbl01_FullCompare.RecID,
Format(Left((Right([RecID],14)),8),"dd/mm/yyyy") AS Dte
FROM tbl01_FullCompare
WHERE
(((Format(Left((Right([RecID],14)),8),"dd/mm/yyyy"))<[forms]![frm01_DeleteOldData]![fldDateLimit]));

It's worse than you think.

The Format() function does not return a date, it returns a text string. The
text string "01/15/1982" is less than the text string "02/01/2010" because
you're doing a string (character by character) comparison, not a date
comparison.

What is the actual value in your RecID field? How is the date formatted? Could
you post an example, indicating what the corresponding date value is?

 

[GLT]

Hi John,

Thanks for your response, here is a sample of the recID field:

RecID
1002412010233653
1012412010233653
1022412010233653
102412010233653
1032412010233653

The last 14 digits are the date and time, I just wanted to extract the date
part only.

Re: Well, that's one of many reasons one should NOT use composite fields in
the table at all, much less as part of a primary key... ouch!!!

Please excuse my ignorance, but what do u mean by a composite field?

The RecID field and associated data is created from a script outside of
Access (ie. I import the data), and the only way I could think of to make
each set of data imported unique was to create a record with a unique ID +
the current date and time.

Cheers,
GT

Hi,

I am trying to create a delete query that deletes records older than a
specific date, the date is entered by a field on a form by the user. All
records prior to the date the user enters are removed.

Within that query, the date and time is part of the primary key field, so I
need to extract the date from the primary key field first.

Well, that's one of many reasons one should NOT use composite fields in the
table at all, much less as part of a primary key... ouch!!!

I tried to build this query in the design grid, and when I try to execute
it, it says its too complex to be evaluated.

SELECT tbl01_FullCompare.RecID,
Format(Left((Right([RecID],14)),8),"dd/mm/yyyy") AS Dte
FROM tbl01_FullCompare
WHERE
(((Format(Left((Right([RecID],14)),8),"dd/mm/yyyy"))<[forms]![frm01_DeleteOldData]![fldDateLimit]));

It's worse than you think.

The Format() function does not return a date, it returns a text string. The
text string "01/15/1982" is less than the text string "02/01/2010" because
you're doing a string (character by character) comparison, not a date
comparison.

What is the actual value in your RecID field? How is the date formatted? Could
you post an example, indicating what the corresponding date value is?

 

[John W. Vinson]

Hi John,

Thanks for your response, here is a sample of the recID field:

RecID
1002412010233653
1012412010233653
1022412010233653
102412010233653
1032412010233653

The last 14 digits are the date and time, I just wanted to extract the date
part only.

Ok: try this. BACK UP YOUR DATABASE FIRST OF COURSE! In fact, test this
several times on a copy of the database; deletion cannot be undone, and this
will blindly accept any date entered by the user and blast away. I would never
give users this kind of power, but it's not my database!

PARAMETERS [forms]![frm01_DeleteOldData]![fldDateLimit] DateTime;
DELETE * FROM tbl01_FullCompare
WHERE
CDate(Format(Left((Right([RecID],14)),8),"@@@@-@@-@@"))
<[forms]![frm01_DeleteOldData]![fldDateLimit]));

Re: Well, that's one of many reasons one should NOT use composite fields in
the table at all, much less as part of a primary key... ouch!!!

Please excuse my ignorance, but what do u mean by a composite field?

A composite field - also called (sarcastically) an "intelligent key" - is one
that is composed of more than one field. It's bad anytime; it's worse when it
contains fields which exist elsewhere in the record.

The RecID field and associated data is created from a script outside of
Access (ie. I import the data), and the only way I could think of to make
each set of data imported unique was to create a record with a unique ID +
the current date and time.

Umm... an autonumber? Add a timestamp field recording when the record was
added, not as part of the primary key? Use a two field primary key containing
the imported record number and the datestamp? Lots of options.

Cheers,
GT

Hi,

I am trying to create a delete query that deletes records older than a
specific date, the date is entered by a field on a form by the user. All
records prior to the date the user enters are removed.

Within that query, the date and time is part of the primary key field, so I
need to extract the date from the primary key field first.

Well, that's one of many reasons one should NOT use composite fields in the
table at all, much less as part of a primary key... ouch!!!

I tried to build this query in the design grid, and when I try to execute
it, it says its too complex to be evaluated.

SELECT tbl01_FullCompare.RecID,
Format(Left((Right([RecID],14)),8),"dd/mm/yyyy") AS Dte
FROM tbl01_FullCompare
WHERE
(((Format(Left((Right([RecID],14)),8),"dd/mm/yyyy"))<[forms]![frm01_DeleteOldData]![fldDateLimit]));

It's worse than you think.

The Format() function does not return a date, it returns a text string. The
text string "01/15/1982" is less than the text string "02/01/2010" because
you're doing a string (character by character) comparison, not a date
comparison.

What is the actual value in your RecID field? How is the date formatted? Could
you post an example, indicating what the corresponding date value is?

 

[GLT]

Hi John,

Thanks for your reply - you have been a big help.

Point taken on all acounts - if you mean create a primary key by indexing
the two fields then I got it - learnt how to do that in another thread I
posted...

The only thing is, if I have a Uid field (split from the date field) that
the script creates, set to Indexed no duplicates, won't that cause an issue
when when I load (import) subsequent sets of data? The script just starts
each Uid from 1 everytime its run...

Cheers,
GT.

Hi John,

Thanks for your response, here is a sample of the recID field:

RecID
1002412010233653
1012412010233653
1022412010233653
102412010233653
1032412010233653

The last 14 digits are the date and time, I just wanted to extract the date
part only.

Ok: try this. BACK UP YOUR DATABASE FIRST OF COURSE! In fact, test this
several times on a copy of the database; deletion cannot be undone, and this
will blindly accept any date entered by the user and blast away. I would never
give users this kind of power, but it's not my database!

PARAMETERS [forms]![frm01_DeleteOldData]![fldDateLimit] DateTime;
DELETE * FROM tbl01_FullCompare
WHERE
CDate(Format(Left((Right([RecID],14)),8),"@@@@-@@-@@"))
<[forms]![frm01_DeleteOldData]![fldDateLimit]));

Re: Well, that's one of many reasons one should NOT use composite fields in
the table at all, much less as part of a primary key... ouch!!!

Please excuse my ignorance, but what do u mean by a composite field?

A composite field - also called (sarcastically) an "intelligent key" - is one
that is composed of more than one field. It's bad anytime; it's worse when it
contains fields which exist elsewhere in the record.

The RecID field and associated data is created from a script outside of
Access (ie. I import the data), and the only way I could think of to make
each set of data imported unique was to create a record with a unique ID +
the current date and time.

Umm... an autonumber? Add a timestamp field recording when the record was
added, not as part of the primary key? Use a two field primary key containing
the imported record number and the datestamp? Lots of options.

Cheers,
GT

Hi,

I am trying to create a delete query that deletes records older than a
specific date, the date is entered by a field on a form by the user. All
records prior to the date the user enters are removed.

Within that query, the date and time is part of the primary key field, so I
need to extract the date from the primary key field first.

Well, that's one of many reasons one should NOT use composite fields in the
table at all, much less as part of a primary key... ouch!!!

I tried to build this query in the design grid, and when I try to execute
it, it says its too complex to be evaluated.

SELECT tbl01_FullCompare.RecID,
Format(Left((Right([RecID],14)),8),"dd/mm/yyyy") AS Dte
FROM tbl01_FullCompare
WHERE
(((Format(Left((Right([RecID],14)),8),"dd/mm/yyyy"))<[forms]![frm01_DeleteOldData]![fldDateLimit]));

It's worse than you think.

The Format() function does not return a date, it returns a text string. The
text string "01/15/1982" is less than the text string "02/01/2010" because
you're doing a string (character by character) comparison, not a date
comparison.

What is the actual value in your RecID field? How is the date formatted? Could
you post an example, indicating what the corresponding date value is?

 

[John W. Vinson]

Hi John,

Thanks for your reply - you have been a big help.

Point taken on all acounts - if you mean create a primary key by indexing
the two fields then I got it - learnt how to do that in another thread I
posted...

The only thing is, if I have a Uid field (split from the date field) that
the script creates, set to Indexed no duplicates, won't that cause an issue
when when I load (import) subsequent sets of data? The script just starts
each Uid from 1 everytime its run...

Well, then don't DO that.

If you want the UID to repeat, don't index it uniquely! It can be *ONE OF THE
FIELDS* in a multifield (two fields, up to ten fields) unique index; if you
have a two-field unique index on the UID and the datestamp, then either field
alone can have repeats, but the combination cannot.

 

[GLT]

OK got it - sorry learned about indexing after i developed my db, but will
change it to use indexing, hopefully makes things less complicated as well...

Thanks a billion for your help...

Cheers...