Query to search between two dates

[omerbutt]

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:(

[NeoPa]

I know nothing about ASP.
If you just want a query to do a job then let me know.

[omerbutt]

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.

[NeoPa]

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.

[NeoPa]

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.

[NeoPa]

To set up string variable strSQL with the correct SQL string use :

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

[omerbutt]

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 :

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

[NeoPa]

No problem - I'm glad you got what you were looking for :).

[omerbutt]

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 :).

[NeoPa]

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?

[MMcCarthy]

"The title of this thread has been changed to make the problem clearer to experts and others searching for similar threads."

MODERATOR

[omerbutt]

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?

[MMcCarthy]

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

[omerbutt]

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

[MMcCarthy]

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.

[omerbutt]

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.

[NeoPa]

These dates look ok.
Can you do a :

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.