INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Add 1 to date field if holiday

Add 1 to date field if holiday

(OP)
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.

CODE

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

Swi

RE: Add 1 to date field if holiday

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

CODE --> SQL

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

RE: Add 1 to date field if holiday

(OP)
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

RE: Add 1 to date field if holiday

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close