Update a datefield

[ivonsurf123]

Hello,


I am trying to update a date in the field AttendDate, that field contains the data for the whole year from 1/1/2018 till 12/31/2018, what I am trying to do is a query where can update is that field is missing a date per example date 9/24/2018 is not in that field. I was able to retrieve the whole data with a SELECT, but when I use an UPDATE query does not work, perhaps I am missing something, I run it and it comeback with 7/10/1905 dates in the whole field. Please, help.

 SELECT [AttendDate] FROM tbl_HolidayTracker
WHERE  Year([AttendDate]) = Year(Date());

UPDATE tbl_HolidayTracker
SET [AttendDate] = Year(Date())
WHERE [Location] = '" & Me.cboCountry &"';

 

[GinaWhipp]

Hmm, do you really have a field named *Year*? Since Access uses it, it's a reserved word, that could be the problem. Try enclosing it in brackets.

 

[pbaldy]

Also, the syntax is wrong. You have

SET Year [AttendDate] = Year(Date())

the correct syntax would be

SET FieldName = DesiredValue

Is the red Year in there by mistake?

 

[ivonsurf123]

Sorry, that was a typo (Mistake), I just removed it in the query provided, but same results, I was thinking to do a loop instead, looking on the AttendDate field each day for the year and if a date is missing to add it to the field.

 

[GinaWhipp]

Is Me.cboCountry TEXT or NUMERIC? Because if the first column is numeric it won't match Location.

 

[pbaldy]

Ah, if you understand how dates are stored you'll see your problem. Date/time values are stored as a double, where the integer portion is the number of days since the base date and the decimal portion is the fraction of 24 hours. You see a formatted version of that stored value.

?now
9/20/2018 3:11:16 PM
?cdbl(now)
43363.6328935185

So when you just put 2018 into the date field, you end up with your 1905 date because today is 43363 in the table. Not sure what you're really trying to update that field to, so not able to offer a solution. What do you expect to see before/after the update?

 

[ivonsurf123]

I see...makes sense now. Thank you, will do take it in consideration.

 

[pbaldy]

No problem. I should have noticed that at first, but got distracted by the syntax error.

 

[GinaWhipp]

No problem. I should have noticed that at first, but got distracted by the syntax error.

Me and you both!

 

[pbaldy]

It's funny (or sad) how sometimes you get locked into seeing one thing and miss anything else. :banghead: