Query regarding Date Ranges

spraveens · January 13th, 2004, 05:29 AM

Hi

I need to write a query whereby I provide 2 dates and the results should have all the records with their dates with in the provided range specified by the 2 dates .How do I do this ?

Praveen

owain · January 13th, 2004, 05:35 AM

Code:

SELECT Fields
  FROM YourTable
 WHERE DateField BETWEEN EarlierDate AND LaterDate

Regards
Owain Williams

spraveens · January 13th, 2004, 06:03 AM

Hi

Here the variables fdate and tdate have the value assigned from a form using "request.form" , Now Iam using the following statement and it is returning null records even though there are some satifying records.Iam using Access database. The statement Iam using is :

sqlstmt= "select * from projectx where sdate BETWEEN "&fdate&" AND "&tdate

Set Rs = Con.Execute (sqlstmt)

pls help.

owain · January 13th, 2004, 06:16 AM

You need to quote your dates in your SQL statement:

Code:

sqlstmt = "SELECT * FROM projectx WHERE sdate BETWEEN '" & _
    fdate & "' AND '" & tdate & "'"

For example, if fdate is 2003-01-01 and tdate is 2004-01-01 then the SQL statement that will be executed will be:

Code:

SELECT * FROM projectx WHERE sdate BETWEEN '2003-01-01' AND '2004-01-01'

You may also want to check that the dates in your variables are formatted correctly for your installation, for example US or UK date format.

Regards
Owain Williams

spraveens · January 13th, 2004, 07:18 AM

Hi I did exactly as you quoted above and now Iam getting the following error.The query is returning an error which is

Error Type:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
/sops/projectx/report.asp, line 21

owain · January 13th, 2004, 07:25 AM

Didn't realise you were using Access, in that case you need to wrap the dates in hashes (#) not quotes:

Code:

sqlstmt = "SELECT * FROM projectx WHERE sdate BETWEEN #" & _
    fdate & "# AND #" & tdate & "#"

That should solve your problem.

Regards
Owain Williams

spraveens · January 13th, 2004, 07:56 AM

God Bless You Mr.Owain

I've been trying to figure out this problem for 6 hours now and finnaly you have given me the solution and the program is running fine now. I dont have words to thank you.

Regards.

praveen:D

owain · January 13th, 2004, 08:11 AM

A tip for the future, if you do not know how to write the query you want, fire up Access and create the query using the query designer. Then view the query in SQL view and copy the relevant parts into your code.

It is a bad way of learning SQL because Access uses a very funny dialect but it should help you if you get stuck.

In the mean time may I recommend you invest in a good SQL programming book? There are literally hundreds of these available in almost every SQL dialect imaginable. I have several myself and I must say that knowing the language really helps you understand what is going on and how to construct efficient and complex queries.

Regards
Owain Williams