CODEWITHSUNDEEP
sqlms-accessvba
Nicholas Menzel
Nicholas Menzel

Reputation: 53

Comparing dates doesn't work if month is different

I am trying to compare 4 date fields in my access Database and set the date difference between the latest value and adatum into parkdauer. It works fine for any rows in which the dates are during the same month. 

strSQL = "UPDATE Steuerungsboard 
          SET parkdauer = DateDiff('d', [kspausgang], [adatum]) 
          WHERE FORMAT(kspausgang, 'dd/MM/yyyy') >= FORMAT( iausgang, 'dd/MM/yyyy') 
             And FORMAT( kspausgang, 'dd/MM/yyyy') >= FORMAT( aausgang, 'dd/MM/yyyy') 
             And FORMAT( kspausgang, 'dd/MM/yyyy') >= FORMAT( gausgang, 'dd/MM/yyyy');"
CurrentDb.Execute strSQL, FailonError

Thanks for any Help!

Upvotes: 1

Views: 92

Answers (1)

Erik A
Erik A

Reputation: 32682

Just remove all those FORMAT things and this will run fine.

The problem with FORMAT is you're actually comparing dates alphabetically, and that's wrong for numerous reasons.

"UPDATE Steuerungsboard 
          SET parkdauer = DateDiff('d', [kspausgang], [adatum]) 
          WHERE kspausgang >=  Nz(iausgang) 
             And  kspausgang >=  Nz(aausgang) 
             And  kspausgang >=  Nz(gausgang);"

Upvotes: 5

Related Questions