comparing date in access

Results 1 to 2 of 2

Thread: comparing date in access

  1. #1
    Join Date
    Dec 1969

    Default comparing date in access

    How do I check or compare date using an sql statement in access from asp.I have tried the following but it failed.<BR><BR>strsql= "Select * from table where date = # " & date &"#"<BR><BR>also <BR>what sql statement can i use to select all records which have a field having no data yet.<BR>

  2. #2
    Join Date
    Dec 1969

    Default Two things wrong...

    strsql= "Select * from table where date = # " & date &"#"<BR><BR>(1) The word "date" is a reserved word in Access, so if you have a field in your table with that name, you should change it!<BR><BR>However, if you are stubborn about the name, you *can* enclose the field name in [...] to force Access to see it as a field name instead of as a keyword:<BR><BR>strsql= "Select * from table where [date] = # " & date &"#"<BR><BR>(2) You *say* that the field is a date field in Access. But *IS IT*??? Are you sure it isn&#039;t a DATE/TIME field?<BR><BR>If so, are you sure that it doesn&#039;t contain values such as<BR>&nbsp; &nbsp; April 13, 2001 6:30:12 PM<BR>???<BR><BR>Because if it does, then it will *NEVER* be *equal* to the current Date! Because the current Date (actually, the Date() function) *always* has a time of midnight, at the beginning of the day. So you are trying to do<BR>&nbsp; &nbsp; April 13, 2001 6:30:12 PM = April 13, 2001 0:00:00 AM<BR><BR>If this is your problem, the solution in Access is easy: Access has the same DateValue function available to you as does VBScript (look it up in either set of docs!).<BR><BR>So you can do:<BR><BR>strsql= "Select * from table where DateValue([date]) = # " & date &"#"<BR><BR>Finally, if you really *are* comparing vs. today&#039;s date, then why get the date in VBScript code, at all? Access, as noted, has the Date() function just as much available as does VBS. So:<BR><BR>strsql= "Select * from table where DateValue([date]) = Date()"<BR><BR>*********<BR><BR>Hokay?<BR><BR><BR> Oh...your other question...<BR><BR>It depends on the field type, but *probably* something like:<BR><BR>SELECT * FROM table WHERE someField is Null<BR><BR>Been too long since I used that part of Access. Maybe it is<BR><BR>SELECT * FROM table WHERE isNull(someField)<BR><BR>but I don&#039;t think so.<BR><BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts