By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

Query to search between two dates

omerbutt
100+
P: 638
i am having an access database and have created a table named RECORDS
which has the following fields

cust_name......(customer name)
items..............(items)
date_pur..........(purchase date)
cmp_name......(company name)

Now i have a form through which i input 2 dates

date_from
date_to

i get these values onto another asp page and i want to search all the records that are between these 2 dates and display them i have no idea for the query to be executed plz plz plz plz if any1 could help me:(
Nov 29 '06 #1
Share this Question
Share on Google+
16 Replies


NeoPa
Expert Mod 15k+
P: 31,660
I know nothing about ASP.
If you just want a query to do a job then let me know.
Nov 30 '06 #2

omerbutt
100+
P: 638
yes sir currently my query is
"SELECT * from table_name where field_name>= '"& date_from &"' AND field_name <='"& date_to &"' "

the datatype for the database filed "date_pur" is text i have tried other queries includin the keyword BETWEEN but none of it is giving me the exact results,when i input the dates
date_from=1/1/2006(mm/dd/yyyy)
date_to=8/2/2006(mm/dd/yyyy)

it retrieves correct results but whenever i switch to double figures in the date or month section for example i input the dates
date_from=1/12/2006
date_to=1/22/2006

it gives me the dates that are even smaller than the "date_from" which is not required plz if u can guide me plz doo :( im in aloot mess

tahnks aloot in advance
I know nothing about ASP.
If you just want a query to do a job then let me know.
Dec 4 '06 #3

NeoPa
Expert Mod 15k+
P: 31,660
We can help.
Your SQL is not right.
What is the field type of [Field_Name]?
What is the variable type of Date_From & Date_To?
How is the data put into Date_From & Date_To?
You will need to use the Format() function, but I won't know exactly how without answers to these questions.
Dec 4 '06 #4

NeoPa
Expert Mod 15k+
P: 31,660
Forget last post - I was responding to your SQL which referred to these names but your first post makes it clearer.
Give me some time & I'll get something posted for you.
Dec 4 '06 #5

NeoPa
Expert Mod 15k+
P: 31,660
To set up string variable strSQL with the correct SQL string use :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * " & _
  2.          "FROM [RECORDS] " & _
  3.          "WHERE (CDate(Date_Pur) Between #" & _
  4.          Format(Date_From,"m/d/yyyy") & "# And #" & _
  5.          Format(Date_To,"m/d/yyyy") & "#)"
Dec 4 '06 #6

omerbutt
100+
P: 638
thanks buddy i wud try it today thankx alooooooooooooooooooot for dat much help thank u ver much and hope to get help in future ;)

To set up string variable strSQL with the correct SQL string use :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * " & _
  2.          "FROM [RECORDS] " & _
  3.          "WHERE (CDate(Date_Pur) Between #" & _
  4.          Format(Date_From,"m/d/yyyy") & "# And #" & _
  5.          Format(Date_To,"m/d/yyyy") & "#)"
Dec 6 '06 #7

NeoPa
Expert Mod 15k+
P: 31,660
No problem - I'm glad you got what you were looking for :).
Dec 6 '06 #8

omerbutt
100+
P: 638
SIR I AM RECIEN=VING THE ERROR "type mismatch 'format '" :( when i try to run the query now wot:(



No problem - I'm glad you got what you were looking for :).
Dec 8 '06 #9

NeoPa
Expert Mod 15k+
P: 31,660
SIR I AM RECEIVING THE ERROR "type mismatch 'format '" :( when I try to run the query now wot:(
You need to post EXACTLY (sorry to shout ;)) what your code says and let us know where the error message occurred. Last question - What is the type of Date_Pur?
Dec 8 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
"The title of this thread has been changed to make the problem clearer to experts and others searching for similar threads."

MODERATOR
Dec 9 '06 #11

omerbutt
100+
P: 638
Data type of Date_Pur is "text" and the error is occuring on the query :( if u say so i can copy paste the exact error message here ?



You need to post EXACTLY (sorry to shout ;)) what your code says and let us know where the error message occurred. Last question - What is the type of Date_Pur?
Dec 16 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
Data type of Date_Pur is "text" and the error is occuring on the query :( if u say so i can copy paste the exact error message here ?
I would go ahead and do that

Mary
Dec 16 '06 #13

omerbutt
100+
P: 638
to make my problem more clear let me tell you that ithe error occours in the query where i am comparing the dates through the 'FORMAT' KEYWORD ...MEANS THE ERROR ORIGINALLY RESIDES ON THE "format" KEYWORD. PLEASE REPLY ME AS SOON AS POSSIBLE i would be very thankfull to you :)

strSQL = "SELECT * " & _
"FROM [RECORDS] " & _
"WHERE (CDate(Date_Pur) Between #" & _
Format(Date_From,"m/d/yyyy") & "# And #" & _
Format(Date_To,"m/d/yyyy") & "#)"

I would go ahead and do that

Mary
Dec 19 '06 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
to make my problem more clear let me tell you that ithe error occours in the query where i am comparing the dates through the 'FORMAT' KEYWORD ...MEANS THE ERROR ORIGINALLY RESIDES ON THE "format" KEYWORD. PLEASE REPLY ME AS SOON AS POSSIBLE i would be very thankfull to you :)

strSQL = "SELECT * " & _
"FROM [RECORDS] " & _
"WHERE (CDate(Date_Pur) Between #" & _
Format(Date_From,"m/d/yyyy") & "# And #" & _
Format(Date_To,"m/d/yyyy") & "#)"
It sounds like Date_Pur is not in a structure that can be read as a date. Can you post a sample of the values in this field.
Dec 19 '06 #15

omerbutt
100+
P: 638
yeah sure here they are same to same copied from the database filed named"Date_Pur"

Date_Pur
11/2/2006
11/3/2006
11/3/2006
11/3/2005
11/5/2006
11/5/2006
12/1/2006
11/1/2006
11/5/2006
12/2/2006
12/2/2006
12/2/2006
12/2/2006
4/5/2006
11/2/2006
11/1/2006
11/1/2006
11/1/2006
11/2/2006


It sounds like Date_Pur is not in a structure that can be read as a date. Can you post a sample of the values in this field.
Dec 19 '06 #16

NeoPa
Expert Mod 15k+
P: 31,660
These dates look ok.
Can you do a :
Expand|Select|Wrap|Line Numbers
  1. Debug.Print strSQL
after you've set up your strSQL string and before you use it.
Then, using Ctrl-G, Copy and paste the resultant string in here.
Dec 19 '06 #17

Post your reply

Sign in to post your reply or Sign up for a free account.