View Full Version : ado: date(field) that falls within the stated dates ...

08-17-2007, 04:17 PM
"...New records must have a date that falls within the stated dates of the trip and does not match the date of an existing record..."
I read this in an exercise(Luce-"ASP") , WELL how I give to a date field in a record of a table a value between two predefined dates but not a date of another record in the same table ?

08-17-2007, 06:18 PM
I can't figure out a way to do this that doesn't involve two database trips. And even worse, I can't figure out a way to do it that doesn't involve embedding loops.

This is what I got:

dim dStart, dEnd, aDates, dCurrentDate, dTestDate, blnDateExists, dFirstAvailableDate

aDates = Array("17/1/2007", "19/1/2007")

dStart = cDate("15/1/2007")
dEnd = cDate("20/1/2007")

for i = dStart to dEnd

dCurrentDate = cDate(i)
blnDateExists = false

for j = lBound(aDates) to uBound(aDates)

dTestDate = cDate(aDates(j))
if dCurrentDate = dTestDate then blnDateExists = true


if not blnDateExists then
dFirstAvailableDate = dCurrentDate
exit for
end if




Assuming that aDates in this example represents the results of a database query to get all distinct dates between your start and end dates, this method simply loops through from your start to end dates, seeing if there's a matching date in the array each time. First blank it comes to, is your available date for putting into the INSERT.

There's probably a better way. I hope there is, because that solution looks properly clunky to me :(