|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
date criteria in VB Express queryIn Microsoft Access I can write a query that includes the criteria:
Between Date()-7 And Date() to retrieve the records from a table that fall within the last week. I'm trying to write a procedure to do this in VB.NET (2005 Express Edition). But I always get the message that the Jet database engine does not recognize the syntax (of the Date function I assume). I've also tried Now() and it works but only by itself. I seem to add or subtract time from it. What's the best syntax to use? I'm working in the Query Designer. Thanks! Daniel Daniel,
Can you show us the query (is it an SQL procedure), than we get probably better at your problem. I do by instance not know the statement "Between" Cor Show quoteHide quote "Daniel" <danielcre***@gmail.com> schreef in bericht news:1160689576.105662.255210@i42g2000cwa.googlegroups.com... > In Microsoft Access I can write a query that includes the criteria: > > Between Date()-7 And Date() > > to retrieve the records from a table that fall within the last week. > > I'm trying to write a procedure to do this in VB.NET (2005 Express > Edition). But I always get the message that the Jet database engine > does not recognize the syntax (of the Date function I assume). I've > also tried Now() and it works but only by itself. I seem to add or > subtract time from it. > > What's the best syntax to use? I'm working in the Query Designer. > > Thanks! > Daniel > Well, in Access I would write this query to get the results I want:
SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date BETWEEN Date()-7 And Date(); This uses the built in Date() function which retrieves the current system date to pull everything from the "tblDates" table that has an entry_date within the last 7 days. How do I write this query in VB 2005 Express in the Query Designer? I am working with an OLEDB connection object linked to my Access 2003 (Microsoft Jet 4.0) database. The problem seems to be that when the TableAdapter object tries to query the Jet db engine, the Jet engine returns an error that it doesn't know what the Date() function is. I thought the whole point of the adapter object was to take your query and translate it into syntax compatible with the type of database you are querying. But instead, it seem to be passing the literal value of my query directly to Jet. Thanks so much! Show quoteHide quote On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote: > Daniel, > > Can you show us the query (is it an SQL procedure), than we get probably > better at your problem. > > I do by instance not know the statement "Between" > > Cor > > "Daniel" <danielcre***@gmail.com> schreef in berichtnews:1160689576.105662.255***@i42g2000cwa.googlegroups.com... > > > In Microsoft Access I can write a query that includes the criteria: > > > Between Date()-7 And Date() > > > to retrieve the records from a table that fall within the last week. > > > I'm trying to write a procedure to do this in VB.NET (2005 Express > > Edition). But I always get the message that the Jet database engine > > does not recognize the syntax (of the Date function I assume). I've > > also tried Now() and it works but only by itself. I seem to add or > > subtract time from it. > > > What's the best syntax to use? I'm working in the Query Designer. > > > Thanks! > > Daniel How about using the following as you're query:
<pseudocode> SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and tblDates.entry_date < " & now().tostring() </pseudocode>Thanks, Seth Rowe Daniel wrote: Show quoteHide quote > Well, in Access I would write this query to get the results I want: > > SELECT tblDates.* FROM tblDates > WHERE tblDates.entry_date BETWEEN Date()-7 And Date(); > > This uses the built in Date() function which retrieves the current > system date to pull everything from the "tblDates" table that has an > entry_date within the last 7 days. > > How do I write this query in VB 2005 Express in the Query Designer? I > am working with an OLEDB connection object linked to my Access 2003 > (Microsoft Jet 4.0) database. The problem seems to be that when the > TableAdapter object tries to query the Jet db engine, the Jet engine > returns an error that it doesn't know what the Date() function is. I > thought the whole point of the adapter object was to take your query > and translate it into syntax compatible with the type of database you > are querying. But instead, it seem to be passing the literal value of > my query directly to Jet. > > Thanks so much! > > > > On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> > wrote: > > Daniel, > > > > Can you show us the query (is it an SQL procedure), than we get probably > > better at your problem. > > > > I do by instance not know the statement "Between" > > > > Cor > > > > "Daniel" <danielcre***@gmail.com> schreef in berichtnews:1160689576.105662.255***@i42g2000cwa.googlegroups.com... > > > > > In Microsoft Access I can write a query that includes the criteria: > > > > > Between Date()-7 And Date() > > > > > to retrieve the records from a table that fall within the last week. > > > > > I'm trying to write a procedure to do this in VB.NET (2005 Express > > > Edition). But I always get the message that the Jet database engine > > > does not recognize the syntax (of the Date function I assume). I've > > > also tried Now() and it works but only by itself. I seem to add or > > > subtract time from it. > > > > > What's the best syntax to use? I'm working in the Query Designer. > > > > > Thanks! > > > Daniel > SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date Oops, left out an equals sign:> >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and tblDates.entry_date < " & > now().tostring() SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and tblDates.entry_date <= " & now().tostring() Thanks,Seth Rowe rowe_newsgroups wrote: Show quoteHide quote > How about using the following as you're query: > > <pseudocode> > > SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date > >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and tblDates.entry_date < " & now().tostring() > > </pseudocode> > > Thanks, > > Seth Rowe > > > Daniel wrote: > > Well, in Access I would write this query to get the results I want: > > > > SELECT tblDates.* FROM tblDates > > WHERE tblDates.entry_date BETWEEN Date()-7 And Date(); > > > > This uses the built in Date() function which retrieves the current > > system date to pull everything from the "tblDates" table that has an > > entry_date within the last 7 days. > > > > How do I write this query in VB 2005 Express in the Query Designer? I > > am working with an OLEDB connection object linked to my Access 2003 > > (Microsoft Jet 4.0) database. The problem seems to be that when the > > TableAdapter object tries to query the Jet db engine, the Jet engine > > returns an error that it doesn't know what the Date() function is. I > > thought the whole point of the adapter object was to take your query > > and translate it into syntax compatible with the type of database you > > are querying. But instead, it seem to be passing the literal value of > > my query directly to Jet. > > > > Thanks so much! > > > > > > > > On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> > > wrote: > > > Daniel, > > > > > > Can you show us the query (is it an SQL procedure), than we get probably > > > better at your problem. > > > > > > I do by instance not know the statement "Between" > > > > > > Cor > > > > > > "Daniel" <danielcre***@gmail.com> schreef in berichtnews:1160689576.105662.255***@i42g2000cwa.googlegroups.com... > > > > > > > In Microsoft Access I can write a query that includes the criteria: > > > > > > > Between Date()-7 And Date() > > > > > > > to retrieve the records from a table that fall within the last week. > > > > > > > I'm trying to write a procedure to do this in VB.NET (2005 Express > > > > Edition). But I always get the message that the Jet database engine > > > > does not recognize the syntax (of the Date function I assume). I've > > > > also tried Now() and it works but only by itself. I seem to add or > > > > subtract time from it. > > > > > > > What's the best syntax to use? I'm working in the Query Designer. > > > > > > > Thanks! > > > > Daniel Rowe,
Only as addition: and than with parameters. http://www.vb-tips.com/dbpages.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137 Cor Show quoteHide quote "rowe_newsgroups" <rowe_em***@yahoo.com> schreef in bericht news:1160750940.763039.190930@b28g2000cwb.googlegroups.com... >> SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date >> >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and >> >tblDates.entry_date < " & >> now().tostring() > > Oops, left out an equals sign: > > SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date >>=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and >>tblDates.entry_date <= " & now().tostring() > > Thanks, > > Seth Rowe > > > rowe_newsgroups wrote: >> How about using the following as you're query: >> >> <pseudocode> >> >> SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date >> >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and >> >tblDates.entry_date < " & now().tostring() >> >> </pseudocode> >> >> Thanks, >> >> Seth Rowe >> >> >> Daniel wrote: >> > Well, in Access I would write this query to get the results I want: >> > >> > SELECT tblDates.* FROM tblDates >> > WHERE tblDates.entry_date BETWEEN Date()-7 And Date(); >> > >> > This uses the built in Date() function which retrieves the current >> > system date to pull everything from the "tblDates" table that has an >> > entry_date within the last 7 days. >> > >> > How do I write this query in VB 2005 Express in the Query Designer? I >> > am working with an OLEDB connection object linked to my Access 2003 >> > (Microsoft Jet 4.0) database. The problem seems to be that when the >> > TableAdapter object tries to query the Jet db engine, the Jet engine >> > returns an error that it doesn't know what the Date() function is. I >> > thought the whole point of the adapter object was to take your query >> > and translate it into syntax compatible with the type of database you >> > are querying. But instead, it seem to be passing the literal value of >> > my query directly to Jet. >> > >> > Thanks so much! >> > >> > >> > >> > On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> >> > wrote: >> > > Daniel, >> > > >> > > Can you show us the query (is it an SQL procedure), than we get >> > > probably >> > > better at your problem. >> > > >> > > I do by instance not know the statement "Between" >> > > >> > > Cor >> > > >> > > "Daniel" <danielcre***@gmail.com> schreef in >> > > berichtnews:1160689576.105662.255***@i42g2000cwa.googlegroups.com... >> > > >> > > > In Microsoft Access I can write a query that includes the criteria: >> > > >> > > > Between Date()-7 And Date() >> > > >> > > > to retrieve the records from a table that fall within the last >> > > > week. >> > > >> > > > I'm trying to write a procedure to do this in VB.NET (2005 Express >> > > > Edition). But I always get the message that the Jet database engine >> > > > does not recognize the syntax (of the Date function I assume). I've >> > > > also tried Now() and it works but only by itself. I seem to add or >> > > > subtract time from it. >> > > >> > > > What's the best syntax to use? I'm working in the Query Designer. >> > > >> > > > Thanks! >> > > > Daniel > Thanks. I could do this from code. But my question is: How do you enter
parameters that are created on the fly by a function call in the query designer? I know if Access can do it, .NET can do it too...somehow! Show quoteHide quote On Oct 13, 12:50 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote: > Rowe, > > Only as addition: and than with parameters. > > http://www.vb-tips.com/dbpages.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44... > > Cor > > "rowe_newsgroups" <rowe_em***@yahoo.com> schreef in berichtnews:1160750940.763039.190***@b28g2000cwb.googlegroups.com... > > >> SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date > >> >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and > >> >tblDates.entry_date < " & > >> now().tostring() > > > Oops, left out an equals sign: > > > SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date > >>=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and > >>tblDates.entry_date <= " & now().tostring() > > > Thanks, > > > Seth Rowe > > > rowe_newsgroups wrote: > >> How about using the following as you're query: > > >> <pseudocode> > > >> SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date > >> >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and > >> >tblDates.entry_date < " & now().tostring() > > >> </pseudocode> > > >> Thanks, > > >> Seth Rowe > > >> Daniel wrote: > >> > Well, in Access I would write this query to get the results I want: > > >> > SELECT tblDates.* FROM tblDates > >> > WHERE tblDates.entry_date BETWEEN Date()-7 And Date(); > > >> > This uses the built in Date() function which retrieves the current > >> > system date to pull everything from the "tblDates" table that has an > >> > entry_date within the last 7 days. > > >> > How do I write this query in VB 2005 Express in the Query Designer? I > >> > am working with an OLEDB connection object linked to my Access 2003 > >> > (Microsoft Jet 4.0) database. The problem seems to be that when the > >> > TableAdapter object tries to query the Jet db engine, the Jet engine > >> > returns an error that it doesn't know what the Date() function is. I > >> > thought the whole point of the adapter object was to take your query > >> > and translate it into syntax compatible with the type of database you > >> > are querying. But instead, it seem to be passing the literal value of > >> > my query directly to Jet. > > >> > Thanks so much! > > >> > On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> > >> > wrote: > >> > > Daniel, > > >> > > Can you show us the query (is it an SQL procedure), than we get > >> > > probably > >> > > better at your problem. > > >> > > I do by instance not know the statement "Between" > > >> > > Cor > > >> > > "Daniel" <danielcre***@gmail.com> schreef in > >> > > berichtnews:1160689576.105662.255***@i42g2000cwa.googlegroups.com... > > >> > > > In Microsoft Access I can write a query that includes the criteria: > > >> > > > Between Date()-7 And Date() > > >> > > > to retrieve the records from a table that fall within the last > >> > > > week. > > >> > > > I'm trying to write a procedure to do this in VB.NET (2005 Express > >> > > > Edition). But I always get the message that the Jet database engine > >> > > > does not recognize the syntax (of the Date function I assume). I've > >> > > > also tried Now() and it works but only by itself. I seem to add or > >> > > > subtract time from it. > > >> > > > What's the best syntax to use? I'm working in the Query Designer. > > >> > > > Thanks! > >> > > > Daniel Daniel,
In the same way, but not in the designer of course. Use the intelisence for that, to find the names of your paremeters. Cor Show quoteHide quote "Daniel" <danielcre***@gmail.com> schreef in bericht news:1160762714.223326.42110@f16g2000cwb.googlegroups.com... > Thanks. I could do this from code. But my question is: How do you enter > parameters that are created on the fly by a function call in the query > designer? > > I know if Access can do it, .NET can do it too...somehow! > > > > > On Oct 13, 12:50 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> > wrote: >> Rowe, >> >> Only as addition: and than with parameters. >> >> http://www.vb-tips.com/dbpages.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44... >> >> Cor >> >> "rowe_newsgroups" <rowe_em***@yahoo.com> schreef in >> berichtnews:1160750940.763039.190***@b28g2000cwb.googlegroups.com... >> >> >> SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date >> >> >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and >> >> >tblDates.entry_date < " & >> >> now().tostring() >> >> > Oops, left out an equals sign: >> >> > SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date >> >>=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and >> >>tblDates.entry_date <= " & now().tostring() >> >> > Thanks, >> >> > Seth Rowe >> >> > rowe_newsgroups wrote: >> >> How about using the following as you're query: >> >> >> <pseudocode> >> >> >> SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date >> >> >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and >> >> >tblDates.entry_date < " & now().tostring() >> >> >> </pseudocode> >> >> >> Thanks, >> >> >> Seth Rowe >> >> >> Daniel wrote: >> >> > Well, in Access I would write this query to get the results I want: >> >> >> > SELECT tblDates.* FROM tblDates >> >> > WHERE tblDates.entry_date BETWEEN Date()-7 And Date(); >> >> >> > This uses the built in Date() function which retrieves the current >> >> > system date to pull everything from the "tblDates" table that has an >> >> > entry_date within the last 7 days. >> >> >> > How do I write this query in VB 2005 Express in the Query Designer? >> >> > I >> >> > am working with an OLEDB connection object linked to my Access 2003 >> >> > (Microsoft Jet 4.0) database. The problem seems to be that when the >> >> > TableAdapter object tries to query the Jet db engine, the Jet engine >> >> > returns an error that it doesn't know what the Date() function is. I >> >> > thought the whole point of the adapter object was to take your query >> >> > and translate it into syntax compatible with the type of database >> >> > you >> >> > are querying. But instead, it seem to be passing the literal value >> >> > of >> >> > my query directly to Jet. >> >> >> > Thanks so much! >> >> >> > On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> >> >> > wrote: >> >> > > Daniel, >> >> >> > > Can you show us the query (is it an SQL procedure), than we get >> >> > > probably >> >> > > better at your problem. >> >> >> > > I do by instance not know the statement "Between" >> >> >> > > Cor >> >> >> > > "Daniel" <danielcre***@gmail.com> schreef in >> >> > > berichtnews:1160689576.105662.255***@i42g2000cwa.googlegroups.com... >> >> >> > > > In Microsoft Access I can write a query that includes the >> >> > > > criteria: >> >> >> > > > Between Date()-7 And Date() >> >> >> > > > to retrieve the records from a table that fall within the last >> >> > > > week. >> >> >> > > > I'm trying to write a procedure to do this in VB.NET (2005 >> >> > > > Express >> >> > > > Edition). But I always get the message that the Jet database >> >> > > > engine >> >> > > > does not recognize the syntax (of the Date function I assume). >> >> > > > I've >> >> > > > also tried Now() and it works but only by itself. I seem to add >> >> > > > or >> >> > > > subtract time from it. >> >> >> > > > What's the best syntax to use? I'm working in the Query >> >> > > > Designer. >> >> >> > > > Thanks! >> >> > > > Daniel > So you're saying that it's not possible to enter a function call as an
SQL query parameter in VB's Query Designer? Show quoteHide quote On Oct 13, 1:35 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote: > Daniel, > > In the same way, but not in the designer of course. > > Use the intelisence for that, to find the names of your paremeters. > > Cor > > "Daniel" <danielcre***@gmail.com> schreef in berichtnews:1160762714.223326.42***@f16g2000cwb.googlegroups.com... > > > Thanks. I could do this from code. But my question is: How do you enter > > parameters that are created on the fly by a function call in the query > > designer? > > > I know if Access can do it, .NET can do it too...somehow! > > > On Oct 13, 12:50 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> > > wrote: > >> Rowe, > > >> Only as addition: and than with parameters. > > >>http://www.vb-tips.com/dbpages.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44... > > >> Cor > > >> "rowe_newsgroups" <rowe_em***@yahoo.com> schreef in > >> berichtnews:1160750940.763039.190***@b28g2000cwb.googlegroups.com... > > >> >> SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date > >> >> >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and > >> >> >tblDates.entry_date < " & > >> >> now().tostring() > > >> > Oops, left out an equals sign: > > >> > SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date > >> >>=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and > >> >>tblDates.entry_date <= " & now().tostring() > > >> > Thanks, > > >> > Seth Rowe > > >> > rowe_newsgroups wrote: > >> >> How about using the following as you're query: > > >> >> <pseudocode> > > >> >> SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date > >> >> >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and > >> >> >tblDates.entry_date < " & now().tostring() > > >> >> </pseudocode> > > >> >> Thanks, > > >> >> Seth Rowe > > >> >> Daniel wrote: > >> >> > Well, in Access I would write this query to get the results I want: > > >> >> > SELECT tblDates.* FROM tblDates > >> >> > WHERE tblDates.entry_date BETWEEN Date()-7 And Date(); > > >> >> > This uses the built in Date() function which retrieves the current > >> >> > system date to pull everything from the "tblDates" table that has an > >> >> > entry_date within the last 7 days. > > >> >> > How do I write this query in VB 2005 Express in the Query Designer? > >> >> > I > >> >> > am working with an OLEDB connection object linked to my Access 2003 > >> >> > (Microsoft Jet 4.0) database. The problem seems to be that when the > >> >> > TableAdapter object tries to query the Jet db engine, the Jet engine > >> >> > returns an error that it doesn't know what the Date() function is. I > >> >> > thought the whole point of the adapter object was to take your query > >> >> > and translate it into syntax compatible with the type of database > >> >> > you > >> >> > are querying. But instead, it seem to be passing the literal value > >> >> > of > >> >> > my query directly to Jet. > > >> >> > Thanks so much! > > >> >> > On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> > >> >> > wrote: > >> >> > > Daniel, > > >> >> > > Can you show us the query (is it an SQL procedure), than we get > >> >> > > probably > >> >> > > better at your problem. > > >> >> > > I do by instance not know the statement "Between" > > >> >> > > Cor > > >> >> > > "Daniel" <danielcre***@gmail.com> schreef in > >> >> > > berichtnews:1160689576.105662.255***@i42g2000cwa.googlegroups.com... > > >> >> > > > In Microsoft Access I can write a query that includes the > >> >> > > > criteria: > > >> >> > > > Between Date()-7 And Date() > > >> >> > > > to retrieve the records from a table that fall within the last > >> >> > > > week. > > >> >> > > > I'm trying to write a procedure to do this in VB.NET (2005 > >> >> > > > Express > >> >> > > > Edition). But I always get the message that the Jet database > >> >> > > > engine > >> >> > > > does not recognize the syntax (of the Date function I assume). > >> >> > > > I've > >> >> > > > also tried Now() and it works but only by itself. I seem to add > >> >> > > > or > >> >> > > > subtract time from it. > > >> >> > > > What's the best syntax to use? I'm working in the Query > >> >> > > > Designer. > > >> >> > > > Thanks! > >> >> > > > Daniel It may be possible (I don't know for sure - I don't use the designer),
but why would you? Personally, I like to have direct control over what goes in to my programs, it increases you knowledge of the language (instead of the ide) and can also prevent subtle errors from creeping into your code. The designer doesn't know everything your project does and might generate conflicting or inefficient code. Just my thoughts, Seth Rowe Daniel wrote: Show quoteHide quote > So you're saying that it's not possible to enter a function call as an > SQL query parameter in VB's Query Designer? > > > > On Oct 13, 1:35 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> > wrote: > > Daniel, > > > > In the same way, but not in the designer of course. > > > > Use the intelisence for that, to find the names of your paremeters. > > > > Cor > > > > "Daniel" <danielcre***@gmail.com> schreef in berichtnews:1160762714.223326.42***@f16g2000cwb.googlegroups.com... > > > > > Thanks. I could do this from code. But my question is: How do you enter > > > parameters that are created on the fly by a function call in the query > > > designer? > > > > > I know if Access can do it, .NET can do it too...somehow! > > > > > On Oct 13, 12:50 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> > > > wrote: > > >> Rowe, > > > > >> Only as addition: and than with parameters. > > > > >>http://www.vb-tips.com/dbpages.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44... > > > > >> Cor > > > > >> "rowe_newsgroups" <rowe_em***@yahoo.com> schreef in > > >> berichtnews:1160750940.763039.190***@b28g2000cwb.googlegroups.com... > > > > >> >> SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date > > >> >> >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and > > >> >> >tblDates.entry_date < " & > > >> >> now().tostring() > > > > >> > Oops, left out an equals sign: > > > > >> > SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date > > >> >>=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and > > >> >>tblDates.entry_date <= " & now().tostring() > > > > >> > Thanks, > > > > >> > Seth Rowe > > > > >> > rowe_newsgroups wrote: > > >> >> How about using the following as you're query: > > > > >> >> <pseudocode> > > > > >> >> SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date > > >> >> >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and > > >> >> >tblDates.entry_date < " & now().tostring() > > > > >> >> </pseudocode> > > > > >> >> Thanks, > > > > >> >> Seth Rowe > > > > >> >> Daniel wrote: > > >> >> > Well, in Access I would write this query to get the results I want: > > > > >> >> > SELECT tblDates.* FROM tblDates > > >> >> > WHERE tblDates.entry_date BETWEEN Date()-7 And Date(); > > > > >> >> > This uses the built in Date() function which retrieves the current > > >> >> > system date to pull everything from the "tblDates" table that has an > > >> >> > entry_date within the last 7 days. > > > > >> >> > How do I write this query in VB 2005 Express in the Query Designer? > > >> >> > I > > >> >> > am working with an OLEDB connection object linked to my Access 2003 > > >> >> > (Microsoft Jet 4.0) database. The problem seems to be that when the > > >> >> > TableAdapter object tries to query the Jet db engine, the Jet engine > > >> >> > returns an error that it doesn't know what the Date() function is. I > > >> >> > thought the whole point of the adapter object was to take your query > > >> >> > and translate it into syntax compatible with the type of database > > >> >> > you > > >> >> > are querying. But instead, it seem to be passing the literal value > > >> >> > of > > >> >> > my query directly to Jet. > > > > >> >> > Thanks so much! > > > > >> >> > On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> > > >> >> > wrote: > > >> >> > > Daniel, > > > > >> >> > > Can you show us the query (is it an SQL procedure), than we get > > >> >> > > probably > > >> >> > > better at your problem. > > > > >> >> > > I do by instance not know the statement "Between" > > > > >> >> > > Cor > > > > >> >> > > "Daniel" <danielcre***@gmail.com> schreef in > > >> >> > > berichtnews:1160689576.105662.255***@i42g2000cwa.googlegroups.com... > > > > >> >> > > > In Microsoft Access I can write a query that includes the > > >> >> > > > criteria: > > > > >> >> > > > Between Date()-7 And Date() > > > > >> >> > > > to retrieve the records from a table that fall within the last > > >> >> > > > week. > > > > >> >> > > > I'm trying to write a procedure to do this in VB.NET (2005 > > >> >> > > > Express > > >> >> > > > Edition). But I always get the message that the Jet database > > >> >> > > > engine > > >> >> > > > does not recognize the syntax (of the Date function I assume). > > >> >> > > > I've > > >> >> > > > also tried Now() and it works but only by itself. I seem to add > > >> >> > > > or > > >> >> > > > subtract time from it. > > > > >> >> > > > What's the best syntax to use? I'm working in the Query > > >> >> > > > Designer. > > > > >> >> > > > Thanks! > > >> >> > > > Daniel There are times when typing out a SQL statement in code is just fine.
But when I'm working with joining several tables or need to enter a lot of different criteria, I would MUCH rather use a visual tool for query design. I think a visual method is much more apt to prevent subtle errors than trying to "picture" the layout and relationships of every object in your head while you type out SELECT statement after SELECT statement. Besides, I think hand coding will become increasingly minimized and everything will go to a more visual approach in the programming world as time goes on. We don't feel the need to get into the code that adjusts pixel depth when editing a picture in Photoshop or do all HTML coding in Notepad anymore. You don't have to jump in there and work in Assembly or machine language everytime you need to communicate with hardware anymore. And there is no real need to make a pie crust from scratch anymore when you can buy one at the store and cut out all that manual work (...and know it will taste good!). As they say on the Ask.com commercial: "Use tools. Feel human." Show quoteHide quote On Oct 13, 6:35 pm, "rowe_newsgroups" <rowe_em***@yahoo.com> wrote: > It may be possible (I don't know for sure - I don't use the designer), > but why would you? Personally, I like to have direct control over what > goes in to my programs, it increases you knowledge of the language > (instead of the ide) and can also prevent subtle errors from creeping > into your code. The designer doesn't know everything your project does > and might generate conflicting or inefficient code. > > Just my thoughts, > > Seth Rowe > > Daniel wrote: > > So you're saying that it's not possible to enter a function call as an > > SQL query parameter in VB's Query Designer? > > > On Oct 13, 1:35 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> > > wrote: > > > Daniel, > > > > In the same way, but not in the designer of course. > > > > Use the intelisence for that, to find the names of your paremeters. > > > > Cor > > > > "Daniel" <danielcre***@gmail.com> schreef in berichtnews:1160762714.223326.42***@f16g2000cwb.googlegroups.com... > > > > > Thanks. I could do this from code. But my question is: How do you enter > > > > parameters that are created on the fly by a function call in the query > > > > designer? > > > > > I know if Access can do it, .NET can do it too...somehow! > > > > > On Oct 13, 12:50 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> > > > > wrote: > > > >> Rowe, > > > > >> Only as addition: and than with parameters. > > > > >>http://www.vb-tips.com/dbpages.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44... > > > > >> Cor > > > > >> "rowe_newsgroups" <rowe_em***@yahoo.com> schreef in > > > >> berichtnews:1160750940.763039.190***@b28g2000cwb.googlegroups.com... > > > > >> >> SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date > > > >> >> >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and > > > >> >> >tblDates.entry_date < " & > > > >> >> now().tostring() > > > > >> > Oops, left out an equals sign: > > > > >> > SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date > > > >> >>=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and > > > >> >>tblDates.entry_date <= " & now().tostring() > > > > >> > Thanks, > > > > >> > Seth Rowe > > > > >> > rowe_newsgroups wrote: > > > >> >> How about using the following as you're query: > > > > >> >> <pseudocode> > > > > >> >> SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date > > > >> >> >=" & DateAdd(DateInterval.Day, -7, Now()).ToString() & " and > > > >> >> >tblDates.entry_date < " & now().tostring() > > > > >> >> </pseudocode> > > > > >> >> Thanks, > > > > >> >> Seth Rowe > > > > >> >> Daniel wrote: > > > >> >> > Well, in Access I would write this query to get the results I want: > > > > >> >> > SELECT tblDates.* FROM tblDates > > > >> >> > WHERE tblDates.entry_date BETWEEN Date()-7 And Date(); > > > > >> >> > This uses the built in Date() function which retrieves the current > > > >> >> > system date to pull everything from the "tblDates" table that has an > > > >> >> > entry_date within the last 7 days. > > > > >> >> > How do I write this query in VB 2005 Express in the Query Designer? > > > >> >> > I > > > >> >> > am working with an OLEDB connection object linked to my Access 2003 > > > >> >> > (Microsoft Jet 4.0) database. The problem seems to be that when the > > > >> >> > TableAdapter object tries to query the Jet db engine, the Jet engine > > > >> >> > returns an error that it doesn't know what the Date() function is. I > > > >> >> > thought the whole point of the adapter object was to take your query > > > >> >> > and translate it into syntax compatible with the type of database > > > >> >> > you > > > >> >> > are querying. But instead, it seem to be passing the literal value > > > >> >> > of > > > >> >> > my query directly to Jet. > > > > >> >> > Thanks so much! > > > > >> >> > On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> > > > >> >> > wrote: > > > >> >> > > Daniel, > > > > >> >> > > Can you show us the query (is it an SQL procedure), than we get > > > >> >> > > probably > > > >> >> > > better at your problem. > > > > >> >> > > I do by instance not know the statement "Between" > > > > >> >> > > Cor > > > > >> >> > > "Daniel" <danielcre***@gmail.com> schreef in > > > >> >> > > berichtnews:1160689576.105662.255***@i42g2000cwa.googlegroups.com... > > > > >> >> > > > In Microsoft Access I can write a query that includes the > > > >> >> > > > criteria: > > > > >> >> > > > Between Date()-7 And Date() > > > > >> >> > > > to retrieve the records from a table that fall within the last > > > >> >> > > > week. > > > > >> >> > > > I'm trying to write a procedure to do this in VB.NET (2005 > > > >> >> > > > Express > > > >> >> > > > Edition). But I always get the message that the Jet database > > > >> >> > > > engine > > > >> >> > > > does not recognize the syntax (of the Date function I assume). > > > >> >> > > > I've > > > >> >> > > > also tried Now() and it works but only by itself. I seem to add > > > >> >> > > > or > > > >> >> > > > subtract time from it. > > > > >> >> > > > What's the best syntax to use? I'm working in the Query > > > >> >> > > > Designer. > > > > >> >> > > > Thanks! > > > >> >> > > > Daniel 
Other interesting topics
Converting Hex Fraction to Dec
Copying cells to successive rows in Excel Property Grid Resursive Folders - Exclude Folders "Optional" reference? Convert C# "writer.Write(@" to VB ? ASP web app VS 2005 nightmares Adding a control programmatically... Oracle throwing an exception, "ORA-01036: illegal variable name/nu |
|||||||||||||||||||||||
