sql with Access and dates in UK format

	Home Register Search Help Login

Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

sql with Access and dates in UK format

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP and Database >> sql with Access and dates in UK format

Page: [1]


yogaboy Posts: 377 Joined: 5/22/2004 Status: offline	sql with Access and dates in UK format - 12/29/2004 23:56:57 Before I kill myself in frustration, someone please please please tell me how to use BETWEEN and get it to work Here's my much maligned code myNow = Date() sDate = sMonth & "-" & 01 & "-" & sYear WHERE (KeyDocuments.DatePublished) BETWEEN #" & myNow & "# AND #" & sDate & "#)" The dates are stored as strings in the Access database in the form dd-mmm-yyyy eg 31-12-2004 that stuff with sDate above is me trying to get my dates in the right format for the query, but I have tried so many different formats and sql statements and I either get errors or everything with a date spewing back at me. --------------------------------------------------- I thought I'd be clever and tried this (KeyDocuments.DatePublished BETWEEN #" & myNow & "# AND #" & DateAdd("m",-1,myNow)& "#)" and it appeared to work, bringing back everything since the beginning of Nov until now. But it also brings back 2 other records, one with 11-Mar-2004, and 11-Jan-2004. What's that about? -------------------------------------------------------------------- my final post before I crawl into bed, badly beaten by Microsoft... (KeyDocuments.DatePublished BETWEEN #" & myNow & "# AND #" & DateAdd("m",-3,myNow)& "#)" brings back everything in Nov 2004, Dec 2004, and anything in January from any year as long as it's the first, and a few things from April and March randomly thrown in. How nice. ---------------------- I thought I'd collate my posts or no one will read them - it reads like the log of a man set adrift at sea!!! < Message edited by yogaboy -- 12/30/2004 0:21:41 >

Spooky

Posts: 26603
Joined: 11/11/1998
From: Middle Earth
Status: offline

RE: sql with Access and dates in UK format - 12/30/2004 14:16:39

What is sDate supposed to represent?
Try and use the international format yyyy/mm/dd via the function below

Function DateFormat(dt)
	DateFormat = year(dt)&"/"&left("00",2-len(month(dt)))&month(dt)&"/"&left("00",2-len(day(dt)))&day(dt) &" " & formatdatetime(dt,4)
End Function

myNow =DateFormat(Date())

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

��k�

Database / DRW help Check out the Tutorials for Frontpage Databases

Forum FAQ' s

Developers Edition Spooky Login User Registration and Management tool

Personalized Frontpage Hosting

(in reply to yogaboy)


yogaboy Posts: 377 Joined: 5/22/2004 Status: offline	RE: sql with Access and dates in UK format - 12/30/2004 17:59:43 I've found the source of the error, and surprise surprise, it was my fault! I was using a function to enter everything into the database as format dd-mm-yyyy, and that worked very nicely. It's just a pity I set the database field as type text, and not type date. As soon as I changed that, it worked! Thanks for the 'n'th time Spooky. (in reply to Spooky)

Page: [1]

All Forums >> Web Development >> ASP and Database >> sql with Access and dates in UK format

Page: [1]

Jump to: 1

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts