Wrox Programmer Forums
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 27th, 2008, 10:43 AM
Authorized User
 
Join Date: Apr 2007
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default Search a date range help

Hi. I am still new here and learning well, but I just can not locate info or get started on how to set up my query to search a single date range. I have a field called "Date_Paid" hitting a MySQL database. My table is called "unsolved".

Can anyone get me started here? I assume I will give a text field to enter the date or can I use a pulldown field to select a month and year?

Thank you.

 
Old May 27th, 2008, 03:08 PM
Friend of Wrox
 
Join Date: May 2006
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can use any UI elements that work for you. For starters, using drop-downs to limit user input sounds like a good idea. Later on you can use a calendar or any UI that works for you users.

You will want to validate the input to verify that it is as you expect it to be. Using the drop-downs will make it easier to keep the user from making mistakes, but you still want to verify that the values you receive are within the ranges that you require. For example, you don't want to allow processing for February 31st, right?

So, once you get the input from the user, you'll need to construct some SQL that will find dates within the range specified. You'll want to make sure that your query will actually find dates that are as you suspect. When you get that far, let me know if you need help.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit
 
Old May 27th, 2008, 03:11 PM
Authorized User
 
Join Date: Apr 2007
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi woody.

I am that far now. I have the form. I understand the basics of asp. I am having trouble with the query.

 
Old May 27th, 2008, 03:22 PM
Authorized User
 
Join Date: Apr 2007
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried this:
SQL ="SELECT *, DATE_FORMAT(Date_Paid, '%c-%e-%Y') AS Date_Paid FROM unsolved WHERE Date_Paid Like '%"&monthdate&"%' and Date_Paid Like '"&Yeardate&"%' "

it does return results, BUT... My querystring shows the results based on the day! I need the results to be based on month and year. when I run a querystring, it calls up all records that match by the DAY and YEAR! why is this??? I need the records to show by MONTH and year!

I only have one date field, and I simply want to be able to call up just a particular month.

 
Old May 28th, 2008, 01:08 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Maybe I misunderstood... but you mentioned a "single date range". Please clarify. Do you mean anything on a specific date, or all dates between two dates?

Either way, one inportant thing to consider is how the dates are stored in the database. If you are using a Date datatype in MySql, I believe it is formatted as YYYY-MM-DD. So to match a specific date you can do something like this (I haven't tested this code - it is just from memory, so test it out and see what happens):

SELECT * FROM unsolved WHERE Date_Paid = '2008-5-27';

You'll just have to build your date string (the 2008-5-27 part) dynamically depending on your input.

You can also use the between clause.
Hopefully this helps, if not, I'll try to look at it again tomorrow or sometime soon.





Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit
 
Old May 28th, 2008, 01:24 AM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

I was and are still not sure if I know exactly what you are after either. You say:

;;;I only have one date field, and I simply want to be able to call up just a particular month

Which would lead me to think something like this is wat you are after:

sql = "SELECT * FROM unsolved WHERE MONTH(Date_Paid) = MONTH(yourDateValue);"

Wind is your friend
Matt
www.elitemarquees.com.au
 
Old May 28th, 2008, 07:52 AM
Authorized User
 
Join Date: Apr 2007
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, thanks guys... but I am not sure now how to set up my pulldowns to apss the data string so the MONTH(yourDateValue) can grab it....
I have one pulldown that passes "monthdate". This has numbers 1 thru 12. The other pulldown passes "yeardate". 2006 thru 2008.
The string shows example: "date_test.asp?monthdate=1&yeardate=2008"

so how do I set up the syntax for query to accept and search this string?

if I were to use "SELECT * FROM unsolved WHERE Date_Paid = '2008-5-27';" how do i change this 2008-5-27 dynamically??

Thank again! I am probably making more out of this than what it is.

 
Old May 28th, 2008, 10:27 AM
Authorized User
 
Join Date: Apr 2007
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok i thought i was real close for a solution here....

my query:
SQL ="SELECT *, MONTH(Date_Paid) AS NewDate FROM unsolved WHERE Date_Paid Like '%" & monthdate & "' and Date_Paid Like '"&yeardate&"%' "

BUT... The returned results queries the DAY and year, NOT the MONTH and year!

I just cant get the string to only search the MONTH and year!!
Help!!!!

 
Old May 28th, 2008, 03:05 PM
Friend of Wrox
 
Join Date: May 2006
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Please clarify. Are you trying to find all the records with Date_Paid being for a specific month?



Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit
 
Old May 28th, 2008, 03:08 PM
Authorized User
 
Join Date: Apr 2007
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes,
I am looking to show ALL records of a single month only (i.e., march or may).






Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find a date range between another date rang tayvonne Access 2 August 3rd, 2006 09:50 AM
Date Range Flipflop SQL Language 6 November 25th, 2005 08:36 AM
Need Help With Date Range rpainter Crystal Reports 0 June 20th, 2005 01:01 PM
Help with Date Range PacMed Access 1 February 1st, 2005 12:30 PM
Date Range mrideout BOOK: Beginning ASP.NET 1.0 0 January 2nd, 2005 07:18 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.