date problem(Access 2000) to fix this

Results 1 to 3 of 3

Thread: date problem(Access 2000) to fix this

  1. #1
    Join Date
    Dec 1969

    Default date problem(Access 2000) to fix this

    Hi all,<BR><BR>Well I had posted few days again back to square one... I am having a big problem with the date format.<BR><BR>I need date/month/year.<BR><BR>I have changed my regional settings date format to dd/mm/YYYY<BR><BR>and I have made my date/time field as short Date.<BR><BR>But still I am not getting the desired results.<BR><BR>How to fix this....this is really getting to my nerves...I really appreciat

  2. #2
    Join Date
    Dec 1969

    Default Day() Month() Year()

    datetodb = day(date) & "/" & month(date) & "/" & year(date)<BR><BR>Its clumsy but it works...<BR><BR>-- Whol

  3. #3
    Join Date
    Dec 1969

    Default Somebody told you wrong

    You should *NOT* have changed your regional settings.<BR><BR>You should have left them alone, happily producing DD/MM/YYYY.<BR><BR>The *ONLY* place where this is likely to get you in trouble is when you try to INSERT or UPDATE an Access DB or do a query based on a date.<BR><BR>And *THEN* the best thing to do--and the thing the works for *ANY* database!--is to use the ISO date format! Which is YYYY-MM-DD.<BR><BR>And you do that through a simple function:<BR><BR>&#060;%<BR>Function ISODate( dt )<BR> If Not IsDate( dt ) Then<BR> ISODate = "NULL"<BR> Else<BR> dt = CDate(dt)<BR> ISODate = "#" & Year(dt) & "-" & Month(dt) & "-" & Day(dt) & "#"<BR> End If<BR>End Function<BR>%&#062;<BR><BR>And now use that ISODate function for building your ACCESS queries:<BR><BR>&#060;%<BR>SQL = "SELECT * FROM table WHERE someDateField = " & ISODate(Request("someField))<BR>%&#062;<BR>Or<BR>& #060;%<BR>SQL = "INSERT INTO table (someDateField, ... ) VALUES(" & ISODate(somevalue) & "," ...<BR>%&#062;<BR><BR>Basically, you use ISODate where before you might have used CDATE.<BR><BR>Note that you do *NOT* put the #...# into the SQL statement. You let the ISODate function do that for you. Because if the value you pass to ISODate is *not* a valid date, then it automtically returns a NULL for you which will be usable in your SQL query.<BR><BR>By doing this--and leaving your LCID (Locale) alone--you get the best of both worlds.<BR><BR>FINALLY...<BR><BR>If you are using some DB *other* than Access, replace the # characters above with &#039; characters.<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