Date
Dates in SQL statements must be in US (United States) short format, and surrounded by the # symbol. For example:
SQL = "Select * from Employee where StartDate >= #" & _
Format(StrtDate, "MM/DD/YY") & "#"
From http://www.adit.co.uk/html/sql_for_access.htm
On 7/10/06, Cook, Kathy <[EMAIL PROTECTED]> wrote:
I believe that I have used BETWEEN with Access before, but had to use the CreateODBCDate function to get it in the format Access was looking for (which is much different that what the Oracle I normally use now looks for).Kathy Cook
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Christopher Jordan
Sent: Monday, July 10, 2006 1:30 PM
To: Dallas/Fort Worth ColdFusion User Group Mailing List
Subject: Re: [DFW CFUG] Trouble Obtaining a date range from an ACCESS databaseRay,
I should have mentioned in my previous email, that I'm not 100% certain that the BETWEEN statement works in MS Access, but I figure it's worth a shot. :)
Chris
Ray Hughes wrote:Hi All,
I am having a problem acquiring a date selection range from an access database.
When I use the following query on an SQl database it works fine.
SELECT dateCreated
FROM cars
WHERE dateCreated > '2006-01-09'
anD dateCreated < '2006-01-11'
If I use the above query on an access database I receive
"Error: Data type mismatch in criteria _expression_. (State:22005, Native Code: FFFFF42A)"
When I use the following query with no Tick marks. I obtain zero records.
SELECT dateCreated
FROM cars
WHERE dateCreated > 2006-01-09
anD dateCreated < 2006-01-11
When I use the following query I receive 60 records.
SELECT dateCreated
FROM cars
I have 60 records entered for january. At least two records for each day.
I have verified that the data exists for these days.
The dateCreated is of datatype DATETIME.
I really could use some help.
Regards
_________________________________________________________________
Don't just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/
_______________________________________________
Reply to DFWCFUG: [email protected]
Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives: http://www.mail-archive.com/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.HostMySite.com
www.teksystems.com/
_______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
