DateDiff function throwing error in Where clause
in [Queries]
|
From: Petr Danes on 3 May 2010 14:04 I have a set of queries that generate dates and want to use DateDiff to select those records with certain dates are greater than others. The following clause keeps giving me an incompatible data type error: WHERE DateDiff('d',[From date],[DatumStvoreni])>0 I have the exact expression in the SELECT clause and it show positive and negative integers just fine, but when I add this test, I get the error. There are no bad values in the dataset, I've looked, and when I export the DateDiff column to a temp table and run a query on that, it works fine. Only when I use the test directly with the DateDiff function does it bomb. I also tried putting CDate() around the field expressions and it didn't help. Pete -- This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas.
From: Tom Lake on 3 May 2010 14:26 "Petr Danes" <skruspammers(a)no.spam> wrote in message news:OqbHUsu6KHA.420(a)TK2MSFTNGP02.phx.gbl... > I have a set of queries that generate dates and want to use DateDiff to select those > records with certain dates are greater than others. The following clause keeps giving me > an incompatible data type error: > > WHERE DateDiff('d',[From date],[DatumStvoreni])>0 > > I have the exact expression in the SELECT clause and it show positive and negative > integers just fine, but when I add this test, I get the error. There are no bad values > in the dataset, I've looked, and when I export the DateDiff column to a temp table and > run a query on that, it works fine. Only when I use the test directly with the DateDiff > function does it bomb. I also tried putting CDate() around the field expressions and it > didn't help. I get an error when I use single quotes. I have to use this: DateDiff("d",[From date],[DatumStvoreni]) The whole expression I tried was this: WHERE (DateDiff("d",[From date],[DatumStvoreni])>0) Tom Lake
From: Petr Danes on 3 May 2010 14:37 Tried that as well - no soap. Sometimes I get a message that the query was too complicated and can't be evaluated. Here's the whole thing: SELECT Stvoreni_Convert.DatumStvoreni, DateDiff("d",[From date],[DatumStvoreni]) AS Rozdil, Stvoreni_Convert.AkcesPodrobnostiAutoID INTO STV FROM Stvoreni_Convert WHERE (((DateDiff("d",[From date],[DatumStvoreni]))>0)); The source of that query is this one (Stvoreni_Convert): SELECT Stvoreni.Stvoreni, getdate([stvoreni]) AS DatumStvoreni, Stvoreni.AkcesPodrobnostiAutoID FROM Stvoreni WHERE (((getdate([stvoreni]))<>'')); and the source of that one is here(Stvoreni): SELECT IIf(InStrRev(nz([Inventarizace]),Chr(13) & Chr(10))>0,Mid(nz([Inventarizace]),2+InStrRev(nz([Inventarizace]),Chr(13) & Chr(10))),nz([Inventarizace])) AS Stvoreni, AkcesPodrobnosti.AkcesPodrobnostiAutoID FROM AkcesPodrobnosti WHERE (((nz([Inventarizace]))<>'') AND ((AkcesPodrobnosti.EvidenceLetter) Is Not Null)); AkcesPodrobnosti is a table, with Inventarizace as a memo field. Getdate is a VBA function extracts a date from a mess of text and returns it as a string. All the preceding works fine, until I add the WHERE clause in the top query. Pete "Tom Lake" <toml_12953(a)hotmail.com> p�se v diskusn�m pr�spevku news:uFzLV2u6KHA.3504(a)TK2MSFTNGP05.phx.gbl... > > "Petr Danes" <skruspammers(a)no.spam> wrote in message > news:OqbHUsu6KHA.420(a)TK2MSFTNGP02.phx.gbl... >> I have a set of queries that generate dates and want to use DateDiff to >> select those records with certain dates are greater than others. The >> following clause keeps giving me an incompatible data type error: >> >> WHERE DateDiff('d',[From date],[DatumStvoreni])>0 >> >> I have the exact expression in the SELECT clause and it show positive and >> negative integers just fine, but when I add this test, I get the error. >> There are no bad values in the dataset, I've looked, and when I export >> the DateDiff column to a temp table and run a query on that, it works >> fine. Only when I use the test directly with the DateDiff function does >> it bomb. I also tried putting CDate() around the field expressions and it >> didn't help. > > I get an error when I use single quotes. I have to use this: > > DateDiff("d",[From date],[DatumStvoreni]) > > The whole expression I tried was this: > > WHERE (DateDiff("d",[From date],[DatumStvoreni])>0) > > Tom Lake > > >
From: Petr Danes on 3 May 2010 14:40 I just tried the last query in another form, using a direct comparison instead of the DateDiff function. Same error. SELECT Stvoreni_Convert.DatumStvoreni, CDate([DatumStvoreni]) AS Rozdil, Stvoreni_Convert.AkcesPodrobnostiAutoID INTO STV FROM Stvoreni_Convert WHERE (((CDate([DatumStvoreni]))>CDate([From date]))); Pete "Tom Lake" <toml_12953(a)hotmail.com> p�se v diskusn�m pr�spevku news:uFzLV2u6KHA.3504(a)TK2MSFTNGP05.phx.gbl... > > "Petr Danes" <skruspammers(a)no.spam> wrote in message > news:OqbHUsu6KHA.420(a)TK2MSFTNGP02.phx.gbl... >> I have a set of queries that generate dates and want to use DateDiff to >> select those records with certain dates are greater than others. The >> following clause keeps giving me an incompatible data type error: >> >> WHERE DateDiff('d',[From date],[DatumStvoreni])>0 >> >> I have the exact expression in the SELECT clause and it show positive and >> negative integers just fine, but when I add this test, I get the error. >> There are no bad values in the dataset, I've looked, and when I export >> the DateDiff column to a temp table and run a query on that, it works >> fine. Only when I use the test directly with the DateDiff function does >> it bomb. I also tried putting CDate() around the field expressions and it >> didn't help. > > I get an error when I use single quotes. I have to use this: > > DateDiff("d",[From date],[DatumStvoreni]) > > The whole expression I tried was this: > > WHERE (DateDiff("d",[From date],[DatumStvoreni])>0) > > Tom Lake > > >
From: Douglas J. Steele on 3 May 2010 17:00
You sure you've got valid values for both fields in every row? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Petr Danes" <skruspammers(a)no.spam> wrote in message news:uT4baAv6KHA.5476(a)TK2MSFTNGP06.phx.gbl... >I just tried the last query in another form, using a direct comparison >instead of the DateDiff function. Same error. > > SELECT Stvoreni_Convert.DatumStvoreni, CDate([DatumStvoreni]) AS Rozdil, > Stvoreni_Convert.AkcesPodrobnostiAutoID INTO STV > FROM Stvoreni_Convert > WHERE (((CDate([DatumStvoreni]))>CDate([From date]))); > > Pete > > > > "Tom Lake" <toml_12953(a)hotmail.com> p�se v diskusn�m pr�spevku > news:uFzLV2u6KHA.3504(a)TK2MSFTNGP05.phx.gbl... >> >> "Petr Danes" <skruspammers(a)no.spam> wrote in message >> news:OqbHUsu6KHA.420(a)TK2MSFTNGP02.phx.gbl... >>> I have a set of queries that generate dates and want to use DateDiff to >>> select those records with certain dates are greater than others. The >>> following clause keeps giving me an incompatible data type error: >>> >>> WHERE DateDiff('d',[From date],[DatumStvoreni])>0 >>> >>> I have the exact expression in the SELECT clause and it show positive >>> and negative integers just fine, but when I add this test, I get the >>> error. There are no bad values in the dataset, I've looked, and when I >>> export the DateDiff column to a temp table and run a query on that, it >>> works fine. Only when I use the test directly with the DateDiff function >>> does it bomb. I also tried putting CDate() around the field expressions >>> and it didn't help. >> >> I get an error when I use single quotes. I have to use this: >> >> DateDiff("d",[From date],[DatumStvoreni]) >> >> The whole expression I tried was this: >> >> WHERE (DateDiff("d",[From date],[DatumStvoreni])>0) >> >> Tom Lake >> >> >> > > |