Visual Basic&database, SQL-statement using Date-function?

SQL-statement using Date-function?

Author	Message
Able #1 / 9	SQL-statement using Date-function? Dear friends In a table a field is containing dates. From the table I want to retrieve all records where fldDate is within the previous 7 days. I tried this: mySQL = "SELECT * FROM Table WHERE fldDate >= #" & Date - 7 & "#" But this cause error like syntaxerror in date in query.... I have also tried this: mySQL = "SELECT * FROM Table WHERE fldDate >= " & Date - 7 Anybody helps me with this task and tell me what is wrong? Regards Able
Wed, 23 Apr 2003 09:07:25 GMT

klas.educationa #2 / 9	SQL-statement using Date-function? Try using between in your SQL statement... mySQL = "SELECT * FROM Table WHERE fldDate Between #" & fromDate & "#" _ & " And #" & toDate & "#;" -- Quote: > Dear friends > In a table a field is containing dates. From the table I want to retrieve > all records where fldDate is within the previous 7 days. I tried this: > mySQL = "SELECT * FROM Table WHERE fldDate >= #" & Date - 7 & "#" > But this cause error like syntaxerror in date in query.... > I have also tried this: > mySQL = "SELECT * FROM Table WHERE fldDate >= " & Date - 7 > Anybody helps me with this task and tell me what is wrong? > Regards Able
Wed, 23 Apr 2003 03:00:00 GMT

John Blessin #3 / 9	SQL-statement using Date-function? Quote: > Dear friends > In a table a field is containing dates. From the table I want to retrieve > all records where fldDate is within the previous 7 days. I tried this: > mySQL = "SELECT * FROM Table WHERE fldDate >= #" & Date - 7 & "#" > But this cause error like syntaxerror in date in query.... > I have also tried this: > mySQL = "SELECT * FROM Table WHERE fldDate >= " & Date - 7 > Anybody helps me with this task and tell me what is wrong? > Regards Able I presume the 'Date' you mention above is not the name of the variable as it is a reserved word. You need to do something like: mySQL = "SELECT * FROM Table WHERE fldDate >= #" & Date - 7 & "#" dim dtLastWeek as date dim dtNow as date dtNow = Now dtLastWeek = DateAdd("d", -7, dtNow) mySQL = "SELECT * FROM Table WHERE fldDate >= #" Month(dtLastweek) & "/" & Day(dtLastWeek) & "/" & Year(dtLastweek) & "#" -- John Blessing http://www.LbeHelpdesk.com - home of the free helpdesk
Wed, 23 Apr 2003 03:00:00 GMT

John Blessin #4 / 9	SQL-statement using Date-function? Quote: > > Dear friends > > In a table a field is containing dates. From the table I > want to retrieve > > all records where fldDate is within the previous 7 days. I > tried this: > > mySQL = "SELECT * FROM Table WHERE fldDate >= #" & Date - > 7 & "#" > > But this cause error like syntaxerror in date in query.... > > I have also tried this: > > mySQL = "SELECT * FROM Table WHERE fldDate >= " & Date - 7 > > Anybody helps me with this task and tell me what is wrong? > > Regards Able > I presume the 'Date' you mention above is not the name of > the variable as it is a reserved word. You need to do > something like: > mySQL = "SELECT * FROM Table WHERE fldDate >= #" & Date - 7 > & "#" > dim dtLastWeek as date > dim dtNow as date > dtNow = Now > dtLastWeek = DateAdd("d", -7, dtNow) > mySQL = "SELECT * FROM Table WHERE fldDate >= #" > Month(dtLastweek) & "/" & Day(dtLastWeek) & "/" & > Year(dtLastweek) & "#" > -- > John Blessing > http://www.LbeHelpdesk.com - home of the free helpdesk Oops... Ignore the Quote: > mySQL = "SELECT * FROM Table WHERE fldDate >= #" & Date - 7 > & "#" just after the "You need to do something like:". I meant to delete it after copying from earlier in the message. -- John Blessing http://www.LbeHelpdesk.com - home of the free helpdesk "
Thu, 24 Apr 2003 03:00:00 GMT

Matthew Fishe #5 / 9	SQL-statement using Date-function? Quote: >Dear friends >In a table a field is containing dates. From the table I want to retrieve >all records where fldDate is within the previous 7 days. I tried this: >mySQL = "SELECT * FROM Table WHERE fldDate >= #" & Date - 7 & "#" >But this cause error like syntaxerror in date in query.... Beats me! I tried out something very like this and it worked just fine. Of course, being a pedant, I always put a ';' on the end of my SQL strings, but I doubt that made the difference. I am assuming that your fldDate field IS set to the Date datatype? Could you post the entire code of your Sub/Function?
Fri, 25 Apr 2003 08:56:07 GMT

nakho #6 / 9	SQL-statement using Date-function? are you sending parameter values using the Command Parameter object? i think you should use mySQL = "SELECT * FROM Table WHERE fldDate >= ?" and Dim oParam as new ADODB.Parameter oParam.Value = dDate-7 oParam.Type = adDate or maybe adDBDate oConnection.Parameters.Append oParam where oConnection is your ADO connection object Quote: >Dear friends >In a table a field is containing dates. From the table I want to retrieve >all records where fldDate is within the previous 7 days. I tried this: >mySQL = "SELECT * FROM Table WHERE fldDate >= #" & Date - 7 & "#" >But this cause error like syntaxerror in date in query.... >I have also tried this: >mySQL = "SELECT * FROM Table WHERE fldDate >= " & Date - 7 >Anybody helps me with this task and tell me what is wrong? >Regards Able
Tue, 06 May 2003 03:00:00 GMT

Kim McKenzi #7 / 9	SQL-statement using Date-function? Put the date in a string variable using sDate = Date -7 then query against the variable. "Select * from tablename where field >= '" & sDate Quote: > Dear friends > In a table a field is containing dates. From the table I want to retrieve > all records where fldDate is within the previous 7 days. I tried this: > mySQL = "SELECT * FROM Table WHERE fldDate >= #" & Date - 7 & "#" > But this cause error like syntaxerror in date in query.... > I have also tried this: > mySQL = "SELECT * FROM Table WHERE fldDate >= " & Date - 7 > Anybody helps me with this task and tell me what is wrong? > Regards Able
Tue, 06 May 2003 03:00:00 GMT

Don Rhode #8 / 9	SQL-statement using Date-function? Dim dStart as date, MySQL as string dStart = date - 7 mySQL = "select * from table where fldDate >= '" & dStart & "'" Quote: > are you sending parameter values using the Command Parameter object? > i think you should use > mySQL = "SELECT * FROM Table WHERE fldDate >= ?" > and > Dim oParam as new ADODB.Parameter > oParam.Value = dDate-7 > oParam.Type = adDate or maybe adDBDate > oConnection.Parameters.Append oParam > where oConnection is your ADO connection object > >Dear friends > >In a table a field is containing dates. From the table I want to retrieve > >all records where fldDate is within the previous 7 days. I tried this: > >mySQL = "SELECT * FROM Table WHERE fldDate >= #" & Date - 7 & "#" > >But this cause error like syntaxerror in date in query.... > >I have also tried this: > >mySQL = "SELECT * FROM Table WHERE fldDate >= " & Date - 7 > >Anybody helps me with this task and tell me what is wrong? > >Regards Able
Wed, 07 May 2003 03:00:00 GMT

DTyr #9 / 9	SQL-statement using Date-function? Depending on the database you are accessing, there could be a few problems. You might try this dim sDate as string sDate = format(dateadd("d",-7,Date),"mm/dd/yyyy") 'to both subtract the seven and format the date into a convertable string Then use your sql statement "Select * from tablename where field >= '" & sDate & "'" don't forget that second delimeter Quote: > Put the date in a string variable using sDate = Date -7 then query against > the variable. > "Select * from tablename where field >= '" & sDate > > Dear friends > > In a table a field is containing dates. From the table I want to retrieve > > all records where fldDate is within the previous 7 days. I tried this: > > mySQL = "SELECT * FROM Table WHERE fldDate >= #" & Date - 7 & "#" > > But this cause error like syntaxerror in date in query.... > > I have also tried this: > > mySQL = "SELECT * FROM Table WHERE fldDate >= " & Date - 7 > > Anybody helps me with this task and tell me what is wrong? > > Regards Able
Sat, 20 Sep 2003 04:38:27 GMT

Page 1 of 1

[ 9 post ]