Solved

Update query to determine if date is a weekend

Posted on 2014-11-06
13
177 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
0
Comment
Question by:ktjamms2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40426343
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

0
 

Author Comment

by:ktjamms2
ID: 40426369
Actually I need:

SET [Copy Of IMPORT_Current].WATER = [Copy Of IMPORT_Current].WATER of the day before
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40426390
Which is why there is a -1 in line 2.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:ktjamms2
ID: 40426409
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
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40426439
What do you mean, "number value"?
0
 

Author Comment

by:ktjamms2
ID: 40426454
rephrased:
Set Saturday's water values to Friday's water values
and Set Sunday's water values to Monday's water values
0
 

Author Comment

by:ktjamms2
ID: 40426465
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
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40427678
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,
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40428034
List 3 records, and show required output.
0
 

Author Comment

by:ktjamms2
ID: 40428469
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.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40428474
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,
0
 

Author Comment

by:ktjamms2
ID: 40428620
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?
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40428633
Your original table is [Copy Of IMPORT_Current]

You create a new query, Query1, with the following code:
Select *, DateAdd("d",iif(Weekday([DATE],1) =7,-1,iif(Weekday([DATE],1) =1,1,0)), [DATE]) as NewDate
From [Copy Of IMPORT_Current]

Open in new window


Then you have another query which does the join:
Select Query1.*, [Copy Of IMPORT_Current].WATER
From Query1
Left join [Copy Of IMPORT_Current]
ON Query1.NewDate = [Copy Of IMPORT_Current].[DATE]

Open in new window

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question