Access Date/Time issue

[AtomCom2001]

Hi there people,

I know you must get these question all of the time, but I have to ask anyways.

I have a SQL select statement which gathers data from an ACCESS databases. It select the data via a date range from one of the fields.

[QUOTE]OleCmd.CommandText = "select * from deliveries where daily_report >= #" + Format(MinDate, "dd/MM/yyyy") + "# and daily_report <= #" + Format(MaxDate, "dd/MM/yyyy") + "#"[/QUOTE]

if the date I am selecting on is like the 27/05/2007 it all works fine, problem is when the date is something like 02/06/2007 it fails...

Any ideas ???

 

[giadich]

Have you checked out the output of Format() function using the input specified???

 

[AtomCom2001]

Yes the output of the format is correct... and with the 27/05/2007 the correct info is even returned ..

Any ideas ??

 

[giadich]

quick test: grab that select statement and run it in Access see if any error turns up.

 

[JohnH]

I think that query should be:

SELECT * FROM deliveries WHERE (daily_report >= ?) AND (daily_report <= ?)

Then you add parameters of Date type to the command and just set the dates (not strings) as parameter values.

 

[AtomCom2001]

JohnH may I thank you very much.

Your suggestion worked like a dream!!!

Though I did have to set the OleDbType to DBDATE instead of DATE.

Please find below the changed but now working correctly code.

        OleCmd.CommandText = "select * from deliveries where (daily_report >= ?) and (daily_report <= ?)"

        OleParaCol.Add("MinDate", MinDate).OleDbType = OleDb.OleDbType.DBDate
        OleParaCol.Add("Maxdate", MinDate).OleDbType = OleDb.OleDbType.DBDate

 

[Arg81]

damn it!! Was going to post that yesterday when I saw the thread but thought that won't be the answer

That's why I don't really like Access and querying it, especially with date fields!

 

[cjard]

if the date I am selecting on is like the 27/05/2007 it all works fine, problem is when the date is something like 02/06/2007 it fails...

Any ideas ???

Dont form your SQLs like that; it's not the right way to do it. For more info, see the PQ link in my signature. Also see the DW2 link in my signature, section on "Creating a Form to Search Data"

 

[AtomCom2001]

WOW!!! many many thanks..

I did not realise what a SQL coding dinosaur I was until I read that .. !! What you say makes a lot of sense and has may interesting points in it ... and I am even going to be as sad as to go over a lot of my sql statements and recoded the PROPERLY !!

 

[cjard]

Welcome!

ps; ensure you find out the parameter syntax for your chosen db

racle
@sqlserver
?mysql

access doesnt use named params, add in the order the ? appear
?

also, be aware that we dont actually write this code any more; we jsut provide the statement to the IDE and it does the coding part. See the DW2 link in my signature..