title: Trouble formatting time subtractions in access while keeping data type
body: <p>Ok, I having been working on subtracting times in Access. It's a pain to say the least, however due to how the form is set up (someone set this up years before I started working at my job &amp; it would be much too time consuming to fix), I cannot update a field in my table through an update query after a text box event is triggered on the form. </p>
  
  <p>So I am however, able to do the calculations in VBA and place the values in the form's text box control to be updated into the table. I have searched online how to subtract the times and get the format displayed as I would like them to be...(hh:mm), however b doing this it converts the final product into a string and the table field linked to the text box is a number/double so I get a VBA error: "The value you entered isn't valid for this field"</p>
  
  <p>Which I know is because it's now a string. I need help with getting the conversion the way I need it, and preserving a double type. CDbl(variable) is not working.</p>
  
  <p>code: </p>
  
  <pre><code>    apptTime = DLookup("[Appt Time]", "T_PKMS", "[Load] = """ &amp; loadNum &amp; """")
      ArrvlTime = DLookup("[Arrival Time]", "T_PKMS", "[Load] = """ &amp; loadNum &amp; """")
  
      hoursPassedSinceAppt = ([Forms]![F_DriverLog].[Time Out].Value - apptTime)
      hoursPassedSinceArrvl = ([Forms]![F_DriverLog].[Time Out].Value - ArrvlTime)
  
      Me.Text51.Value = Format(hoursPassedSinceAppt, "hh:nn")
      Me.Text53.Value = Format(hoursPassedSinceArrvl, "hh:nn")
  </code></pre>
  
  <p>I have also tried:</p>
  
  <pre><code>    Debug.Print CDbl(Format(hoursPassedSinceAppt, "hh:nn"))
      Debug.Print CDbl(Format(hoursPassedSinceArrvl, "hh:nn"))
  </code></pre>
  
  <p>but I get a type mismatch error. Help I am pulling my hair out.</p>