Display records where date is between nnnnn and nnnnn

[sinistapenguin]

Hi

Can anyone help me with this relatively simple question?

I would like to display on a page a repeating region which shows all records that are between 2 dates.

I was thinking of having a form where you enter StartDate and EndDate and the records shown below this form are only those that appear between these dates.

I know there is a WHERE rsRecordDate IN... statement, but I don't know how to use it.
I didn't knwo if you can do WHERE rsRecordDate > varStartDate and rsRecordDate < varEndDate.

Can someone out there give me some pointers.

NB. The database is MSAccess and the dates are stored as ShortDate format.

Thanks again

Sinista

 

[Sitehelp]

ok I dont really know too much about this all but I think that the WHERE statement should be written as:

SELECT ename, sal
FROM emp
WHERE sal BETWEEN 1000 AND 1500

I think its something like that but obviously changing the table and row names. Remember date default format in SQL is DD-MON-YY. Maybe changing the numbers above to the fields that contains the txt might work???????????????????? I am going to need to do this bit soon too so if u have had no luck by then I will post my result, that is if I can sort it myself as I am pretty new at all this too!

 

[Cheech]

You have to be very careful with dates between asp and database. I take the date apart and reformat it every time its used. My SELECT statement for this is

"SELECT *  FROM MYtable  WHERE fldDate BETWEEN #" & strFirstDay & "# AND #" & strLastDay & "# ORDER BY fldDate DESC"

Cheech

[Peace][Pipe]

 

[sinistapenguin]

Thanks for that.

I don't really understand what you mean by "take the date apart".

fldDate is presumably the field in the record that contains the data, but where you have strFirstDay and strLastDay, could these be variables taken from text fields entered on a previous page?

Also, the PC I am using seems to default the date to US layout ie. MM/DD/YY even though all my settings on the O/S are UK layout DD/MM/YY.

Any ideas?

Sinista

 

[Cheech]

Try tweeking this

strDate = Date() 'or request.form("txtDatefld") 
dd=DatePart("d", strDate)
if len(dd)=1 then dd= "0" & dd end if
mm=DatePart("m", strDate)
if len(mm)=1 then mm= "0" & mm end if
yy=DatePart("yyyy", strDate)
if len(yy)=2 then yy= "20" & yy end if
strFirstDate = dd & "/" & mm & "/" & yy

Just repeat the process to get the last date

Cheech

[Peace][Pipe]

 

[HowardMarks]

Why not use the built-in DateDiff function?

e.g.
strSQL = "SELECT * FROM mydatabase WHERE DateDiff(""m"", timefield, #" & datStartDate & "#) < 0 AND DateDiff(""m"", timefield, #" & datEndDate & "#) > 0"

Would select all records where the "timefield" is between "datStartDate" and "datEndDate"




Nick (Webmaster)

info@npfx.com

http://www.npfx.com

http://www.artswales.org

 

[sinistapenguin]

I can't seem to get this to work at all.

No matter which way I try to do it, I get no records being shown

HELP PLEASE!!!!!

Thanks

Sinista