|
Subject:
|
Query regarding Date Ranges
|
|
Posted By:
|
spraveens
|
Post Date:
|
1/13/2004 4:29:27 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
|
|
Reply By:
|
owain
|
Reply Date:
|
1/13/2004 4:35:30 AM
|
SELECT Fields
FROM YourTable
WHERE DateField BETWEEN EarlierDate AND LaterDate
Regards
Owain Williams
|
|
Reply By:
|
spraveens
|
Reply Date:
|
1/13/2004 5:03:48 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.
|
|
Reply By:
|
owain
|
Reply Date:
|
1/13/2004 5:16:36 AM
|
You need to quote your dates in your SQL statement:
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:
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
|
|
Reply By:
|
spraveens
|
Reply Date:
|
1/13/2004 6:18:40 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
|
|
Reply By:
|
owain
|
Reply Date:
|
1/13/2004 6:25:17 AM
|
Didn't realise you were using Access, in that case you need to wrap the dates in hashes (#) not quotes:
sqlstmt = "SELECT * FROM projectx WHERE sdate BETWEEN #" & _
fdate & "# AND #" & tdate & "#" That should solve your problem.
Regards
Owain Williams
|
|
Reply By:
|
spraveens
|
Reply Date:
|
1/13/2004 6:56:50 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
|
|
Reply By:
|
owain
|
Reply Date:
|
1/13/2004 7:11:28 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
|
