Update Query by VB issue with Date field

[ccg_0004]

To begin: All appropriate form controls and table fields are set as Time/Date fields and in the format of Short Date.

The code works fine except for this sticky issue: When the update is performed, rather than updating the date with DD/MM/YYYY (as it was originally stored), it gives me 12:00:17AM. When I run the code again, it updates the date with 12:00:18am. Then 12:00:19am, even though many MINUTES have passed since I ran the code again.

Am I missing something obvious? The data on the form is in the format DD/MM/YYYY so I don't know why the time is being inserted.

CurrentDb.Execute "UPDATE [Invoice Details] SET [End Date] = " & Forms![f_ChangeOutTools].[End date] & " WHERE [Invoice Details].ToolID = '" & .Fields!ToolID & "'" & " AND " & "[Invoice Details].InvoiceID = " & Forms![f_ChangeOutTools].[InvoiceID] & ";"

 

[RuralGuy]

I would use a string variable so you can print it out with a Debug.Print or maybe a MsgBox and see what the string looks like. Then you can use:
MsgBox "[" & strSQL & "]"
CurrentDb.Execute strSQL, dbFailOnError

 

[ccg_0004]

<BIG SHEEPISH GRIN> I don't actually know really what any of that is or what is to occur. I am searching for debug.print on the forum but I got like 20 pages to sort through. Might take a while... but thanks for the hint!!

I thought maybe since text boxes need single quotes around them dates might need something similar

Have you ever heard a issue like this before?

______________________________________________
Ok, I used the msg box method and the strSQL and when the message box popped up it showed the date as being in the correct format. Then when I went to the table to see if the updates were correct, it was still displaying time!! ARRGH!

 

[RuralGuy]

Try something like:

   Dim MySQL As String
   MySQL = "UPDATE [Invoice Details] SET [End Date] = " & _
           Forms![f_ChangeOutTools].[End date] & _
           " WHERE [Invoice Details].ToolID = '" & .Fields!ToolID & "'" & _
           " AND " & "[Invoice Details].InvoiceID = " & _
           Forms![f_ChangeOutTools].[InvoiceID] & ";"
   MsgBox "[" & MySQL & "]"
   CurrentDb.Execute MySQL, dbFailOnError

...and see what happens.

 

[ccg_0004]

HA!! I posted my update ONE MINUTE after you put your code in. I will check it out too but it is exactly what I entered in I believe. Here is what I updated before you posted your code:

Ok, I used the msg box method and the strSQL and when the message box popped up it showed the date as being in the correct format. Then when I went to the table to see if the updates were correct, it was still displaying time!! ARRGH!

 

[RuralGuy]

Maybe this link will help explain a DateTime field better.

 

[ccg_0004]

RG! Thanks for the link and the help. I went over that page and it was actually information that was discussed quite thoroughly in this forum (which basically has everything already!). I need some time (not at the end of the work day!) to process all that page says however.

But.!.!.!.! in the mean time I cheated with a quick fix. I changed the Date control on the form to be text. when I did this and entered in the date in the field- guess what? VIOLA! It worked.

So for now I just put an input mask on there and a date. I wonder if there is any way that I can get it to display a date picker next to it. Back to the forum search i go.....

 

[RuralGuy]

There are lots of date pickers in the link to pick from. You are still far better off keeping a DateTime field and simply displaying it any way you want.