I'm working with a database that has dates broken up when stored. I.E.: month, day, and year are each stored in a seperate column. My problem is that I need to query between date ranges and I'm unsure of how to proceed. The plan is to have the user input through a form and then format the input into a date to use for query.

Is it possible to preformat the recordset query such as:

Dim fromdate, todate
fromdate = FormatDateTime(Request(fmonth) & "/" & Request(fday)
           & "/" & Request(fyear), 2)
todate = FormatDateTime(Request(tmonth) & "/" & Request(tday)
           & "/" & Request(tyear), 2)

Select * FROM DB WHERE FormatDateTime(column1date "/" column2date "/"
       column3date, 2) BETWEEN " & fdate & " AND " & tdate

Thanks for any help!

You can use Convert function in SQL

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Om Prakash

It might be easier, if you can, to not separate them when you store them. There are plenty of functions to manipulate dates to pull back just the month, day, etc.

This might not work because you need one column where the formatdatetime is.
Select * FROM DB WHERE FormatDateTime(column1date "/" column2date "/"
       column3date, 2) BETWEEN " & fdate & " AND " & tdate

You will probably have to take apart the input date(s) and select based on month, year, and day.
http://www.devx.com/tips/Tip/5524

select * from db where columnyear between '" & szYear & "'
                   and columnmonth between '" & szMonth ... etc
(variables from the link)

I wish the dates could be stored complete. That would definitely make my life ALOT easier. I knew I would have to do some sort of breakdown but I was hoping I was making a bigger deal out of it than it needed to be.

Thanks for the responses!

Is there any special reason they need to be stored like that? (appart from having to alter any insert/update etc statements) If not, I would:
1..Create a new field
2..Write a script to get each of the three date parts from each row
3..Concantenate them
4..Insert them into your newly created field as a date data type
5..Then alter the pages that address these wierdly stored date parts

Wind is your friend
Matt

Matt,
Everything is read only so I cannot add a new field to the DB.

I do have another question.

I created an Access database and used the BETWEEN operator to query between dates as it should be. Example:

Dim fdate, tdate
fdate = Request.Form("fmonth") & "/" & Request.Form("fday") & "/" & Request.Form("fyear")
tdate = Request.Form("tmonth") & "/" & Request.Form("tday") & "/" & Request.Form("tyear")

"SELECT * FROM dates WHERE date BETWEEN '" & fdate & "' AND '" & _
        tdate & "' ORDER BY date"

It will display all dates as it should if the fmonth and tmonth are less than 10 (1-9) or if the fmonth and tmonth are 10-12. If I query dates where the fmonth is single digit and the tmonth are double digit the I only get results based on the double digit month.

Any idea what may be causing this? Leading zero maybe?

mmmmm your using access without the # round the dates and its working?

;;;Leading zero maybe?
No I dont believe so

Response.write your sql and copy n paste it here from the bowser

Also I would cDate your date values:
cDate Function:
"Returns an expression that has been converted to a Variant of subtype Date"
Eg cDate(fDate) and cDate(tdate)

Wind is your friend
Matt