We help IT Professionals succeed at work.

Update query to determine if date is a weekend

ktjamms2
ktjamms2 asked
on
225 Views
Last Modified: 2014-11-07
I want to update a field's value to the previous day's field value (if on a Saturday) or to the value of the day after (if on a Sunday). I'm not sure of the correct syntax.

I tried this:
UPDATE [Copy Of IMPORT_Current] SET [Copy Of IMPORT_Current].WATER = DateAdd("d",-1, [Copy Of IMPORT_Current]![DATE])
IIf (Weekday([Copy Of IMPORT_Current]![DATE],1) =7
 
but it's not the correct syntax and it doesn't utilize the "OR" criteria I'm aiming for.

Any help would be great!
Thanks,
KT
Comment
Watch Question

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Try this:

UPDATE [Copy Of IMPORT_Current] SET [Copy Of IMPORT_Current].WATER = DateAdd("d",
iif(Weekday([Copy Of IMPORT_Current]![DATE],1) =7,-1,iif(Weekday([Copy Of IMPORT_Current]![DATE],1) =1,1,0)), 
[Copy Of IMPORT_Current]![DATE])

Open in new window

Author

Commented:
Actually I need:

SET [Copy Of IMPORT_Current].WATER = [Copy Of IMPORT_Current].WATER of the day before
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Which is why there is a -1 in line 2.

Author

Commented:
I'm not sure what you have, but it looks like its setting the value to a date.  I need the value set to the number value of the corresponding date:

SET [Copy Of IMPORT_Current].WATER = [Copy Of IMPORT_Current].WATER of the day before if the [Copy Of IMPORT_Current]![DATE] is a Saturday

or

SET [Copy Of IMPORT_Current].WATER = [Copy Of IMPORT_Current].WATER of the day after if the [Copy Of IMPORT_Current]![DATE] is a Sunday
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
What do you mean, "number value"?

Author

Commented:
rephrased:
Set Saturday's water values to Friday's water values
and Set Sunday's water values to Monday's water values

Author

Commented:
Explanation:
I don't get numbers for weekends. So, I want to just use the numbers for the day before or from the day after depending  on whether it's Saturday or Sunday
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Now that you have the relevant date, in a second query, you do a Left Join with the original table to get the relevant date's value,
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
List 3 records, and show required output.

Author

Commented:
ID            DATE                  WATER

20            11/1/2014            0
20            11/2/2014            225
20            11/3/2014            100      
20            11/4/2014            200
20            11/5/2014            350
20            11/6/2014            500
20            11/7/2014            220
20            11/8/2014            0

11/1/2014 IS A SUNDAY. THE WATER NEEDS TO BE UPDATED TO THE NEXT DAY'S WATER VALUE (11/2/2014, MONDAY). THAT VALUE WOULD BE 225

11/8/2014 IS A SATURDAY. THE WATER NEEDS TO BE UPDATED TO THE PREVIOUS DAY'S WATER VALUE (11/7/2014, FRIDAY). THAT VALUE WOULD BE 220

THERE ARE MULTIPLE IDs AND DATES IN THE TABLE. I ONLY SHOWED AN EXAMPLE OF ONE CASE FOR A WEEK IN THIS EXAMPLE.
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Now that you have the relevant date, in a second query, you do a Left Join with the original table to get the relevant date's value,

Author

Commented:
I don't understand what you are saying----- "Now that you have the relevant date, in a second query, you do a Left Join with the original table to get the relevant date's value, "

Could you show the SQL code?
Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.