p2p.wrox.com: Archives - asp

asp_databases thread: Between two dates

Message #1 by Adil Hindistan <Adilhn@y...> on Mon, 18 Dec 2000 16:32:31 +0200

I need the records between 2 dates.



strSQL ="SELECT * FROM iptable WHERE recorddate BETWEEN #" & _

	FormatDateTime(Date,2) &  "# AND #" & FormatDateTime(Date-7,2) & _

	"# ORDER BY Tarih DESC"



Database is access. recorddate field is a Date/Time fields and data in the

format "05.12.2000 15:45:00"



Interestingly, 





Adil Hindistan, CE-93

www.scorion.net

ICQ:26477783



--- 

NEED TECHNICAL TIPS, TOOLS, AND INSIGHTS?  Is FREE okay?

Visit EarthWeb for the latest in IT Management, Software Development, 

Web Development, Networking & Communications, and Hardware & Systems.  

Click on http://www.earthweb.com for FREE articles, tutorials,

and discussions from the experts.

---

You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')

To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com

Message #2 by Adil Hindistan <Adilhn@y...> on Mon, 18 Dec 2000 17:04:21 +0200

Sorry for the previous non-complete message.



I need the records between today and a week ago. So my sql statement is



strSQL ="SELECT * FROM iptable WHERE recorddate BETWEEN #" & _

	FormatDateTime(Date,2) &  "# AND #" & FormatDateTime(Date-7,2) & _

	"# ORDER BY recorddate DESC"





Produces the error: Microsoft JET Database Engine error '80040e07' : Syntax

error in date in query expression 



Database is access. recorddate field is a Date/Time fields and data in the

format "05.12.2000 15:45:00"



Interestingly, 



strSQL ="SELECT * FROM iptable WHERE recorddate LIKE '" & _

	FormatDateTime(Date,2) &  "%' ORDER BY recorddate DESC"



display the records of today, as ecpected.



Any idea how should I construct the sql statement



Adil Hindistan, CE-93

www.scorion.net

ICQ:26477783 



---

FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS

IN YOUR INBOX!

Get the latest and best HTML, XML, and JavaScript tips, tools, and 

developments from the experts.  Sign up for one or more of EarthWeb's

FREE IT newsletters at http://www.earthweb.com today!  

---

You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')

To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com

Message #3 by "Ken Schaefer" <ken@a...> on Tue, 19 Dec 2000 11:03:35 +1100

Adil,



I suggest you check your field format. If you are using ' marks, then I

suspect your field is of type text, not of type date/time.



http://www.adopenstatic.com/faq/80040e07.asp

deals with 80040e07 errors.



Another thing...try using DateAdd() for this part:



FormatDateTime(Date-7,2)



Instead of subtracting 7 from Date(), using Dateadd() to add -7

days/weeks/hours etc to Date()



Cheers

Ken



----- Original Message -----

From: "Adil Hindistan" <Adilhn@y...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, December 19, 2000 2:04 AM

Subject: [asp_databases] RE: Between two dates





> Sorry for the previous non-complete message.

>

> I need the records between today and a week ago. So my sql statement is

>

> strSQL ="SELECT * FROM iptable WHERE recorddate BETWEEN #" & _

> FormatDateTime(Date,2) &  "# AND #" & FormatDateTime(Date-7,2) & _

> "# ORDER BY recorddate DESC"

>

>

> Produces the error: Microsoft JET Database Engine error '80040e07' :

Syntax

> error in date in query expression

>

> Database is access. recorddate field is a Date/Time fields and data in the

> format "05.12.2000 15:45:00"

>

> Interestingly,

>

> strSQL ="SELECT * FROM iptable WHERE recorddate LIKE '" & _

> FormatDateTime(Date,2) &  "%' ORDER BY recorddate DESC"

>

> display the records of today, as ecpected.

>

> Any idea how should I construct the sql statement







---

FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS

IN YOUR INBOX!

Get the latest and best HTML, XML, and JavaScript tips, tools, and 

developments from the experts.  Sign up for one or more of EarthWeb's

FREE IT newsletters at http://www.earthweb.com today!  

---

You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')

To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com

Message #4 by "Dallas Martin" <dmartin@z...> on Mon, 18 Dec 2000 20:15:39 -0500

this works,



SELECT data FROM table WHERE table.data BETWEEN '1/1/2000' AND '3/1/2000'



hth,

Dallas Martin



----- Original Message -----

From: "Adil Hindistan" <Adilhn@y...>

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, December 18, 2000 9:32 AM

Subject: [asp_databases] Between two dates





> I need the records between 2 dates.

>

> strSQL ="SELECT * FROM iptable WHERE recorddate BETWEEN #" & _

> FormatDateTime(Date,2) &  "# AND #" & FormatDateTime(Date-7,2) & _

> "# ORDER BY Tarih DESC"

>

> Database is access. recorddate field is a Date/Time fields and data in the

> format "05.12.2000 15:45:00"

>

> Interestingly,

>

>

> Adil Hindistan, CE-93

> www.scorion.net

> ICQ:26477783





--- 

FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND

INSIGHTS IN YOUR INBOX!

Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips, tools, and 

developments from the experts.  Sign up for one or more of EarthWeb?s

FREE IT newsletters at http://www.earthweb.com today!  

---

You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')

To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com

Message #5 by "Dallas Martin" <dmartin@z...> on Mon, 18 Dec 2000 20:17:31 -0500

Okay,



SELECT data FROM table WHERE table.data BETWEEN NOW()-7 AND NOW()



hth,

Dallas Martin



----- Original Message -----

From: "Adil Hindistan" <Adilhn@y...>

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, December 18, 2000 10:04 AM

Subject: [asp_databases] RE: Between two dates





> Sorry for the previous non-complete message.

>

> I need the records between today and a week ago. So my sql statement is

>

> strSQL ="SELECT * FROM iptable WHERE recorddate BETWEEN #" & _

> FormatDateTime(Date,2) &  "# AND #" & FormatDateTime(Date-7,2) & _

> "# ORDER BY recorddate DESC"

>

>

> Produces the error: Microsoft JET Database Engine error '80040e07' :

Syntax

> error in date in query expression

>

> Database is access. recorddate field is a Date/Time fields and data in the

> format "05.12.2000 15:45:00"

>

> Interestingly,

>

> strSQL ="SELECT * FROM iptable WHERE recorddate LIKE '" & _

> FormatDateTime(Date,2) &  "%' ORDER BY recorddate DESC"

>

> display the records of today, as ecpected.

>

> Any idea how should I construct the sql statement

>

> Adil Hindistan, CE-93

> www.scorion.net

> ICQ:26477783





---

FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS

IN YOUR INBOX!

Get the latest and best HTML, XML, and JavaScript tips, tools, and 

developments from the experts.  Sign up for one or more of EarthWeb's

FREE IT newsletters at http://www.earthweb.com today!  

---

You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')

To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com

Message #6 by Adil Hindistan <Adilhn@y...> on Tue, 19 Dec 2000 11:33:38 +0200

I actually realized the problem after zillions of trials indeed.



The field was a Date/Time field, no problem there! It was recording the

date/time like



19.12.2000 11:34:00



So, using a SELECT Condition like:



WHERE recorddate = #19.12.2000# should work right ?



WRONG! Don't ask me why, I do not know yet! But,



WHERE recorddate = #19/12/2000# works and produces the expected (!) result.



I noticed "/" in the URL you sent me Ken, thank you.





Adil Hindistan, CE-93

www.scorion.net

ICQ:26477783



> -----Original Message-----

> From: Ken Schaefer [mailto:ken@a...]

> Sent: 19 Aral?k 2000 Sal? 02:04

> To: ASP Databases

> Subject: [asp_databases] RE: Between two dates

> 

> 

> Adil,

> 

> I suggest you check your field format. If you are using ' 

> marks, then I

> suspect your field is of type text, not of type date/time.

> 

> http://www.adopenstatic.com/faq/80040e07.asp

> deals with 80040e07 errors.

> 

> Another thing...try using DateAdd() for this part:

> 

> FormatDateTime(Date-7,2)

> 

> Instead of subtracting 7 from Date(), using Dateadd() to add -7

> days/weeks/hours etc to Date()

> 

> Cheers

> Ken

> 

> ----- Original Message -----

> From: "Adil Hindistan" <Adilhn@y...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Tuesday, December 19, 2000 2:04 AM

> Subject: [asp_databases] RE: Between two dates

> 

> 

> > Sorry for the previous non-complete message.

> >

> > I need the records between today and a week ago. So my sql 

> statement is

> >

> > strSQL ="SELECT * FROM iptable WHERE recorddate BETWEEN #" & _

> > FormatDateTime(Date,2) &  "# AND #" & FormatDateTime(Date-7,2) & _

> > "# ORDER BY recorddate DESC"

> >

> >

> > Produces the error: Microsoft JET Database Engine error '80040e07' :

> Syntax

> > error in date in query expression

> >

> > Database is access. recorddate field is a Date/Time fields 

> and data in the

> > format "05.12.2000 15:45:00"

> >

> > Interestingly,

> >

> > strSQL ="SELECT * FROM iptable WHERE recorddate LIKE '" & _

> > FormatDateTime(Date,2) &  "%' ORDER BY recorddate DESC"

> >

> > display the records of today, as ecpected.

> >

> > Any idea how should I construct the sql statement

> 

> 

> 



--- 

NEED TECHNICAL TIPS, TOOLS, AND INSIGHTS?  Is FREE okay?

Visit EarthWeb for the latest in IT Management, Software Development, 

Web Development, Networking & Communications, and Hardware & Systems.  

Click on http://www.earthweb.com for FREE articles, tutorials,

and discussions from the experts.

---

You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')

To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com

Message #7 by Adil Hindistan <Adilhn@y...> on Tue, 19 Dec 2000 11:10:07 +0200

Result: 

SELECT * FROM iptable WHERE recorddate BETWEEN 19.12.2000 11:07:00 AND

12.12.2000 11:07:00 

Microsoft JET Database Engine error '80040e14' 



Syntax error (missing operator) in query expression 'recorddate BETWEEN

19.12.2000 11:07:00 AND 12.12.2000 11:07:00'. 



Adil Hindistan, CE-93

www.scorion.net

ICQ:26477783



> -----Original Message-----

> From: Dallas Martin [mailto:dmartin@z...]

> Sent: 19 Aral?k 2000 Sal? 03:18

> To: ASP Databases

> Subject: [asp_databases] RE: Between two dates

> 

> 

> Okay,

> 

> SELECT data FROM table WHERE table.data BETWEEN NOW()-7 AND NOW()

> 

> hth,

> Dallas Martin

> 

> ----- Original Message -----

> From: "Adil Hindistan" <Adilhn@y...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Monday, December 18, 2000 10:04 AM

> Subject: [asp_databases] RE: Between two dates

> 

> 

> > Sorry for the previous non-complete message.

> >

> > I need the records between today and a week ago. So my sql 

> statement is

> >

> > strSQL ="SELECT * FROM iptable WHERE recorddate BETWEEN #" & _

> > FormatDateTime(Date,2) &  "# AND #" & FormatDateTime(Date-7,2) & _

> > "# ORDER BY recorddate DESC"

> >

> >

> > Produces the error: Microsoft JET Database Engine error '80040e07' :

> Syntax

> > error in date in query expression

> >

> > Database is access. recorddate field is a Date/Time fields 

> and data in the

> > format "05.12.2000 15:45:00"

> >

> > Interestingly,

> >

> > strSQL ="SELECT * FROM iptable WHERE recorddate LIKE '" & _

> > FormatDateTime(Date,2) &  "%' ORDER BY recorddate DESC"

> >

> > display the records of today, as ecpected.

> >

> > Any idea how should I construct the sql statement

> >

> > Adil Hindistan, CE-93

> > www.scorion.net

> > ICQ:26477783

> 



---

FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS

IN YOUR INBOX!

Get the latest and best HTML, XML, and JavaScript tips, tools, and 

developments from the experts.  Sign up for one or more of EarthWeb's

FREE IT newsletters at http://www.earthweb.com today!  

---

You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')

To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com

Message #8 by "Dallas Martin" <dmartin@z...> on Tue, 19 Dec 2000 19:52:07 -0500

Sorry, I forgot to enclosed the WHERE clause within parens.

Here's the correct code for ACCESS.



SELECT Orders.*

FROM Orders

WHERE (((Orders.OrderDate) Between Now()-180 And Now()));



Again,

My Apologies,

Dallas Martin







----- Original Message -----

From: "Adil Hindistan" <Adilhn@y...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, December 19, 2000 4:10 AM

Subject: [asp_databases] RE: Between two dates





> Result:

> SELECT * FROM iptable WHERE recorddate BETWEEN 19.12.2000 11:07:00 AND

> 12.12.2000 11:07:00

> Microsoft JET Database Engine error '80040e14'

>

> Syntax error (missing operator) in query expression 'recorddate BETWEEN

> 19.12.2000 11:07:00 AND 12.12.2000 11:07:00'.

>

> Adil Hindistan, CE-93

> www.scorion.net

> ICQ:26477783

>

> > -----Original Message-----

> > From: Dallas Martin [mailto:dmartin@z...]

> > Sent: 19 Aral?k 2000 Sal? 03:18

> > To: ASP Databases

> > Subject: [asp_databases] RE: Between two dates

> >

> >

> > Okay,

> >

> > SELECT data FROM table WHERE table.data BETWEEN NOW()-7 AND NOW()

> >

> > hth,

> > Dallas Martin





--- 

NEED TECHNICAL TIPS, TOOLS, AND INSIGHTS?  Is FREE okay?

Visit EarthWeb for the latest in IT Management, Software Development, 

Web Development, Networking & Communications, and Hardware & Systems.  

Click on http://www.earthweb.com for FREE articles, tutorials,

and discussions from the experts.

---

You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')

To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com

Return to Index

P2P Archives