Solved Update Record Using Form and Date (1 Viewer)

[Malcolm17]

Hello,

I'm having issues updating a record for a diary database. I have the following code which should update the record where the date selected in the form textbox called txtLocalSystemDate and in a column in the table selected from a list on the form called txtDiaryFeed. Its complicated slightly because of my subforms. When I hover over it on VBA it shows the correct column and row to go in to but when I run the code in the form it shows that it will update 0 records.

It works when I replace the ending looking for the textbox date, so if I put in #09/10/2021#, however this in American Date format, where my computer is in UK date format. This example I am looking to update 10th Sept 2021.

DoCmd.RunSQL "UPDATE tbl_Net_Diary SET " & [Forms]![frmBackground]![subfrmHome]![txtDiaryFeed] & " = [Forms]![frmBackground]![subfrmHome]![txtDiaryEdit] WHERE TheDate = # " & [Forms]![frmBackground]![txtLocalSystemDate] & " #"

This code did work for a time a few weeks ago, however it has stopped working.

Can anyone advise me what they think is wrong with it please?

Many thanks,

Malcolm

 

[theDBguy]

Hi Malcolm. When you use code with dates, you'll have to use American or ISO date format. Try it this way:

DoCmd.RunSQL "UPDATE tbl_Net_Diary SET " & [Forms]![frmBackground]![subfrmHome]![txtDiaryFeed] & " = [Forms]![frmBackground]![subfrmHome]![txtDiaryEdit] WHERE TheDate = #" & Format([Forms]![frmBackground]![txtLocalSystemDate], "yyyy-mm-dd") & "#"

Hope that helps...

Edit: I noticed you had extra spaces around the hash marks, so I took them out too.

 

[Malcolm17]

Thank you the DB guy, that worked brilliantly and helped me with a couple of other DLookup issues that I was having too!!

Malcolm

 

[theDBguy]

Thank you the DB guy, that worked brilliantly and helped me with a couple of other DLookup issues that I was having too!!

Malcolm

Hi. You're welcome. Good luck with your project.