dates not filtering

[remco5897]

I am trying to filter a record set on a date like so:

SQLstmt = SQLstmt & "WHERE EventDate LIKE " & DateValue(Request.Form("DateInput"))

Unfortunatly, it does not work. It just does not show any records. There is no error. What am I doing wrong?


Other info:
ASP classic
MS Access 2000
PWS

 

[Chopstik]

Doesn't Access require # around dates? Also, why are you using LIKE instead of =?

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[TonyRosen]

SQLstmt = SQLstmt & "WHERE EventDate LIKE '%" & Request.Form("DateInput") & "%';"

Even though, I'd hope you're checking to see if it's a date before this or it will error with a bogus date.

 

[TonyRosen]

OOpps ... forgot it was ACCESS ..

SQLstmt = SQLstmt & "WHERE EventDate LIKE '#" & Request.Form("DateInput") & "#';

 

[remco5897]

Chopstick,

No particular reason why I use LIKE, I copied it from some old code. This is not causing the problem. I just tested it.

What do you mean MS Access requires # around dates?

tnx.

 

[Chopstik]

When referring to dates within Access, you need to surround them with "#" (pound sign). It is something within Access. I believe that Access Help has information on this.

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[remco5897]

Tnx Chopstick and Tony,

Unfortunately nothing you guys suggested worked.


Tony: this is the result of your suggestion:

SELECT * FROM tbl_Events WHERE EventDate LIKE '#7/8/2004#';

It looks good to me, no errors but also no records showing up.


Does this have anything to do with how your local machine deals with dates.

Thnx for the effort guys ...

 

[Chopstik]

Are you able to paste that code into Access and get it to work? Also, are you sure you have records that have that date? Simple things, but better to eliminate the possible before dealing with the less likely/less probable. ;-)

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[TonyRosen]

Actually, the more I think about it .. I'd do it like this:

SQLstmt = "SELECT * FROM tbl_Events WHERE EventDate = '" & DateVar & "';"

Since you are putting in an exact date.

 

[DreXor]

SELECT * FROM tbl_Events WHERE EventDate LIKE '#7/8/2004#';

you were close, try it this way:

SELECT * FROM [tbl_Events] WHERE EventDate = #7/8/2004#

notice no single quotes around the #'s single quotes are pretty universal text qualifiers, in access, pound signs are date qualifiers, and they tend to not like co-mingling.
the square brackets are just a precautionary step too, sometimes access drivers will burp pretty good on underscores in names due to underscore being an escape sequence.

other than that, unless your eventdate field is TEXT, use equality or inequality comparisons, if it's text use your like statements, kind of goofy logic, and sometimes is unpredictable, when something is "like" a date, either its a given date or not.

hope the info helps


DreX
aKa - Robert

 

[remco5897]

DreX,

Thnx for your input, removing those single quotes around #date# worked like a charm.

Sorry for the somewhat late reply ... the weekend got in the way ).

Also thnx to Chopstick and Tony ...

Remco

 

[DreXor]

not a problem,glad to be of help

DreX
aKa - Robert