Delete Query
in [Queries]
|
From: GLT on 4 Feb 2010 22:36 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.
From: John W. Vinson on 4 Feb 2010 23:16 On Thu, 4 Feb 2010 19:36:01 -0800, GLT <GLT(a)discussions.microsoft.com> wrote: >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 [MVP]
From: GLT on 5 Feb 2010 00:24 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 "John W. Vinson" wrote: > On Thu, 4 Feb 2010 19:36:01 -0800, GLT <GLT(a)discussions.microsoft.com> wrote: > > >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 [MVP] > . >
From: John W. Vinson on 5 Feb 2010 02:23 On Thu, 4 Feb 2010 21:24:01 -0800, GLT <GLT(a)discussions.microsoft.com> wrote: >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 > >"John W. Vinson" wrote: > >> On Thu, 4 Feb 2010 19:36:01 -0800, GLT <GLT(a)discussions.microsoft.com> wrote: >> >> >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 [MVP] >> . >> -- John W. Vinson [MVP]
From: GLT on 5 Feb 2010 04:16 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. "John W. Vinson" wrote: > On Thu, 4 Feb 2010 21:24:01 -0800, GLT <GLT(a)discussions.microsoft.com> wrote: > > >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 > > > >"John W. Vinson" wrote: > > > >> On Thu, 4 Feb 2010 19:36:01 -0800, GLT <GLT(a)discussions.microsoft.com> wrote: > >> > >> >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 [MVP] > >> . > >> > -- > > John W. Vinson [MVP] > . >
|
Next
|
Last
Pages: 1 2 Prev: Specific filter for the data in a field Next: Insert Items from Yes/No CheckBox into Query |