MS access update query issue with date range-CodePudding

I'm using this update query in MS access 2016 Update sampledata set VALUE= '" & Me.value & "' where Day between '" & Me.dayfrom & "' and '" & Me.dayto & "'; The strangest problem m facing is- It is considering the form values for start and end date but however updates records only for the start date. Example. If dayfrom is 01-Nov-2021 and dayto is 30-Nov-2021, the query is updating records of only 01-Nov-2021. When I pass the day from as 30-Nov-2021, it is updating records for the whole month.

Note: This doesn't happen when I directly pass the values in the query, it happens only when i Pick data from FORM and apply it in query.

CodePudding user response：

   UPDATE table3 
   SET table3.status = "YES"
   WHERE (((table3.transactiondate)>=[FORM]![Form3]![startdate] And 
   (table3.transactiondate)<=[FORM]![Form3]![enddate]));

When you run the query, two pop up input box will open, in the first one, enter the value for start date, e.g 01/01/2022 , in the second one enter the end date e.g 02/28/2022. Do check the date format in use by your system so you can be sure you are entering the date parameter in the right format.

CodePudding user response：

As is, the date values will be casted to strings using your Windows settings.

So, force a format on the string expressions to have true string expressions for the date values:

Sql = "Update sampledata " & _
    "Set VALUE = '" & Me!value.Value & "' " & _
    "Where Day Between #" & Format(Me!dayfrom.Value, "yyyy\/mm\/dd") & "# And #" & Format(Me!dayto.Value, "yyyy\/mm\/dd") & "#;"