Add 1 to date field if holiday 1

[Swi]

I have a table that has dates. I have another table of holidays.

I want to update the table with dates by 1 day if the day is a holiday.

Below is a query that is giving me a "Operation must use an updateable query" error.

UPDATE Dates SET Dates.OutputDate = Dates.InputDate + (SELECT count(*) FROM Holiday WHERE Dates.InputDate BETWEEN Dates.InputDate AND Dates.InputDate);

Swi

 

[dhookom]

Your query where condition makes no sense because it will always return true. You can use DCount() in place of the subquery:

UPDATE Dates 
SET Dates.OutputDate = Dates.InputDate + DCount("*", "Holiday","[WHERE CONDITION HERE]");

You really need to correct your logic prior to running the update query.

Duane
Hook'D on Access
MS Access MVP

 

[Swi]

Ok, thanks you are correct about the WHERE statement, my apologies. Looks like it is working however what if I would want to convert this to an ADO connection string in VB? I don't believe DCOUNT will work.

Thanks.

Swi

 

[dhookom]

I'm not aware of how this can be done with an ADO connection to an Access table. I'm also not sure what your object might be or how often this needs to be performed.

Duane
Hook'D on Access
MS Access MVP