Date Calculations in Access

• • Reply

  13 years ago

  by toveyj

  John Tovey

  , United Kingdom

  Joined 13 years ago

  Hi There

  I'm not really a developer - I just dabble to help me with my day job! I'm trying to do some VBA code which includes updating a date field in an existing table. I extract the date from another table (field in this source table defined as Short Date format) then use DateAdd to Subtract 2 days from it in VBA. Having done this I then use an SQL staement to update the Field with this calculated value in my existing table (Field format also = Short Date). When I watch the variable during theVBA run it shows as a Data Type = Date and the date shown is correct. After the SQL runs to update the table with this value however  and I look at the values added to the table they are in time rather than date format.  For instance when i try to update the Field to 15/06/2007 what actually appears is 00:01:48.

  Any thoughts on what I'm doing wrong gratefully accepted!

   

   

  Report abuse
• • Reply

  13 years ago

  by khari6579

  Hari K

  Chennai, India

  Joined 15 years ago
  Hi John
   If you set the DataType as Date/Time (ShortDate) in the Access Table then it wont show Date and Time inside the Aceess DB(query shown records). I also checked in my Access File. Where is this problem you are facing, i mean in the DB itself or your coding(FrontEnd). If you are facing in the FrontEnd then which lang are you using...give me some more details to resolve your problem.
  
  I may be also your Regional setting problem...check you Regional Settings in the Control Panel.
  
  
  Good Luck
  
  Regards
  
  Hari K.....
  
  
  
  
  
  
  
  
  
  
  
  
  Report abuse
• • Reply

  13 years ago

  by toveyj

  John Tovey

  , United Kingdom

  Joined 13 years ago

  Hi Hari

  Not sure if this helps. This is my VBA code. The date that comes out of Record 0 in Table [Act Update] is 18/06/2007. The Value for Right_Date then = 15/06/2007 - which is what I expected. When I open the Table and view the field "Referral Creation Date" in Table [New Referrals] after it has been updated by the code below the value that appears in the Field isn't 15/06/2007 as I expected but "00:01:48"

  Dim cnnX As ADODB.Connection

  Dim MyRecordSet As New ADODB.Recordset

  Dim Extract_Date As Date

  Dim Right_Date As Date

   

   

  Set cnnX = CurrentProject.Connection

  MyRecordSet.ActiveConnection = cnnX

   

  MyRecordSet.Open "[Act Update]"

  MyRecordSet.MoveFirst

  Extract_Date = MyRecordSet.Fields(0).Value

  Right_Date = DateAdd("d", -1, Extract_Date)

  DayofWk = Weekday(Right_Date, 2)

   

  If DayofWk = 7 Then

  Right_Date = DateAdd("d", -2, Right_Date)

  ElseIf DayofWk = 6 Then

  Right_Date = DateAdd("d", -1, Right_Date)

  End If

   

  DoCmd.RunSQL "UPDATE [New Referrals] SET [New Referrals].[Referral Creation Date] =" & Right_Date

  Thanks for your help!

   

  John T

  Report abuse
• • Reply

  13 years ago

  by khari6579

  Hari K

  Chennai, India

  Joined 15 years ago
  Hi John
   Try to update this method... and verify the DataType of "Referal Creation Date"
  
  DoCmd.RunSQL "Update [New Referrals] Set [Referral Creation Date]=#" & Format(Right_Date,"dd/MMM/yyyy") & "#"
  
  
  
  I hope this may solve your problem.
  
  Good Luck
  
  Regards
  Hari K......
   
  
  
  
  
  
  
  
  
  
  
  
  Report abuse
• • Reply

  13 years ago

  by toveyj

  John Tovey

  , United Kingdom

  Joined 13 years ago

  Hi Hari

  Tried that and it worked a treat - thanks very much for your help. Saved me from giving up!

  All the best

  John

  Report abuse
• • Reply

  13 years ago

  by khari6579

  Hari K

  Chennai, India

  Joined 15 years ago
  Hai John
   One more thing i need to say... Whenever you are going to Insert/Update/Delete in the table with DataType as Date, better to use triple M format (ex: "dd/MMM/yyyy").
  When you use this format this wont raise any Casting error whatever DB(SQL/Oracle) if your using...
  
  Take Care...Keep in touch...
  
  Hari K......
  
  
  
  
  
  
  
  
  
  
  Report abuse