Access. Dates. Stoopid.

[Spudhead]

Gaaaaahh. This is proper doing my nut in. From SQL Server - which treats dates like... dates - to Access, which treats dates like some sort of lost language of the freakin' ancients.

Right, I've got this:

SELECT * FROM tblAvailability WHERE 1=1 AND departDate=#10/05/2003#;

(The "1=1" is there cos the WHERE clause is built up in an ASP loop and it's easier. Trust me.)

As it is, it selects no rows. But it should; there's data in there that should match.

I've tried wrapping the date in single quotes instead of hashes; I get a "data type mismatch" error. I've tried wrapping it in nothing; el zilcho.

The [sorry excuse for a] database is, I'm sure, storing dates in a "DD/MM/YYYY" format.

Sorry for the... robust... language, but really. I should know this. I shouldn't even have to think about it, far less post silly threads asking how on earth you're supposed to query date fields in Access.

Not A Happy Chuffing Bunny

[raf]

(The "1=1" is there cos the WHERE clause is built up in an ASP loop and it's easier. Trust me.)

Hmm? So the variable is called 1? How weird (how would this be easier? do you replace the 1 further down in the script (cause this would replace both 1's)?). And the date is in the future, so you're sure there are records that meet the criteria + have 1=1?
Did you try it without the 1=1? Or with departDate < #10/05/2003# ?
Did you look at the executed statement with
response.write sqlvariable
response.end

[Spudhead]

Hey raf,

No, the 1=1 is just a conditional that will always return true. I'm looping through form fields in the ASP, and if the field isn't empty, use it to add a chunk to the SQL string;

if fieldValue<>"" then
strSQL=strSQL& " AND " & fieldName & "='" & fieldValue & "'"
end if

The 1=1 is just so I can do a simple loop, and not have to worry about:

"is this the first form field that has a value and thus needs to be added, and if so it needs a 'WHERE', not an 'AND' etc etc"

Also:
I've tried taking "1=1" out. It's not that, it's the date that it's falling over on.
I've tried using 'departDate < #10/05/2003#' and it returned all records - regardless of whether they were before or after that date. Same with <=. Greater than, equal to, or 'greater than or equal to' all produce no records.

I'm going to invent some new swear words in a minute.... :rolleyes:

[raf]

Did you try putting the dates in the db in mm/dd/yyyy format?

I've noticed and read somewhere that ASP will always treat the datevalues as mm/dd/yyyy, so the RDBM will search for 05/10/2003.

[Spudhead]

It had better not :mad: :rolleyes:

I'll try it - might be tomorrow though. I need a pint....

[arnyinc]

Originally posted by Spudhead

SELECT * FROM tblAvailability WHERE 1=1 AND departDate=#10/05/2003#;


As far as the departDate, that is the correct SQL syntax in Access. Either the 1=1 is confusing it or the date is being stored strangely. It's in a date/time field and you are using the same format (mm/dd/yy, dd/mm/yy, mm/dd/yyyy, dd/mm/yyyy) in each one, right?

[Roy Sinclair]

The 1=1 is perfectly valid and can be occasionally useful for generating a condition which is always true. All it's doing is comparing a constant to a constant and since you can validly use a constant on both sides of a comparison operator this is a valid comparison. The key here is that by using that comparison he can add any other conditions he wants by appending them with "and (condition)" without having to worry about whether it's the first condition to be tested or not.

For the rest of the problem, I wouldn't know how to convince Access to compare dates so I'm not going to even guess at why it's going cockeyed on that date comparison, my forte is SQL Server which while not great at date comparisons can be within reason.

[raf]

Hahaaa. That's what it's for. Nice trick. I usually solve this with

For each box in request.form
if request.form(box) = "1" then
if row = 1 then
sql = sql + "(Id=" + box + ")"
else
sql = sql + " OR (Id=" + box + ")"
end if
row = row + 1
end if
next

if row = 0 then
response.write ("<font color='red'>No records selected to be deleted.</font>")
response.write("<br><br><a href='javascript:history.back();'>Back</a>")
else
...


(cause i usually need the counter to check if it's necessary to run the statement or not, but it can come inhandy)

[Spudhead]

It's definitely a Date/Time field and, looking at it, the dates are all in the format dd/mm/yyyy. I don't know. I'm now working on convincing the head of the department that requested it that searching by date isn't that useful.

[Roy Sinclair]

One thing to watch out for in Date-time fields is the "time" component. When all you're comparing is the date portion of the field the time component can be a real nuisance if it's not set to 00:00.