Correctly using dates with and SQL Query in an ADP

[uthomsst]

I am using access 2002 to create an.adp project. I have a number of tables which I have imported from an old mde database that someone developed. In this database there are two fields d3cdate (ShortDate Format) and d3ctime (Medium Time Format)

The database is held on an MSDE server (Latest version)

When I import the data the date and time both have the same format dd/mm/yy hh:mm:ss

The problem I have is that when I try to query the data I never get any results returned or I get error messages.

I have tried a thousand combinations such as

strSQL = "SELECT * FROM s3data "
strSQL = strSQL & "WHERE d3cdate = " & DateValue("30/04/2004 06:43:00")

strSQL = "SELECT * FROM s3data "
strSQL = strSQL & "WHERE d3cdate =#" & [DateFrom] & "#"

strSQL = "SELECT * FROM s3data "
strSQL = strSQL & "WHERE d3cdate ='#" & [DateFrom] & "#'"

strSQL = "SELECT * FROM s3data "
strSQL = strSQL & "WHERE d3cdate =" & Format([FromDate], "0")

When I just use the date I get no results (Perhaps because the database field inlcudes the time). When I include the time the form crashes (I think because the space causes an invalid SQL statement)

I've been at this for a week and am now more than a liitle depressed any ideas or FAQ's for this.

Thanks for any help.

 

[scriverb]

Change the format to mm/dd/yyyy. I see that in your literal criteria you are using dd/mm/yyyy. I have seen this before and with this change the date comparisons begin working.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]