Expert: Ahmed Sayed Aziz - 11/3/2009

Question
QUESTION: I am sending you an image of my present query. You will notice that I have rows for ZONE "5+7" and DAY "Mon" at the top and bottom. The time of top rows under TS column is earlier than bottom rows' time. Actually, the top rows are for "sunday" but in my data it is "Mon". I need a function to change the DAY or DATE of these top row to one day earlier i.e, I would like to change the DAY to "Sun". Could you please help me about this.

ANSWER: Run 2 update queries as follows :

To change date 1 day earlier:
=============================
UPDATE urtbl SET urtbl.[Date] = urtbl.Date-1;

To change day corresponding to the date as updated in above update query
=========================================================================
UPDATE urtbl INNER JOIN urtbl AS urtbl_1 ON urtbl.Id = urtbl_1.Id SET urtbl.[Day] = WeekdayName(Weekday(urtbl.Date))
WHERE (((urtbl.Id)=[urtbl_1].[id]));



---------- FOLLOW-UP ----------

QUESTION: I do not o change the date for all the dates- not even for all the Mondays. I only want to change the date where I have repeating values for Mondays. To see if it is repeating, I will have to compare the values from other columns from the previous rows. Do you think a solution is possible?

ANSWER: Add 1 more criteria like

UPDATE urtbl INNER JOIN urtbl AS urtbl_1 ON urtbl.Id = urtbl_1.Id SET urtbl.[Day] = WeekdayName(Weekday(urtbl.Date))
WHERE (((urtbl.Day)="Mon") AND ((urtbl.Id)=[urtbl_1].[id]));




---------- FOLLOW-UP ----------

QUESTION: Thanks for the answer. I do not want to change all the Mondays, but only those Mondays where ZONE and HOUSE value is same but TIME is different.I need to comapre the vales from the prevous row/s of Monday to the present row and then decide if the dates need to be changed.

Answer
Use Recordset then like :

Dim rs as DAO.Recordset,rsPrevVal1,rsPrevVal2,rsPrevVal3

Set rs= CurrentDB.openRecordset("SELECT * FROM urtbl WHERE Day='Mon'",DB_OPEN_DYNASET)

If rs.BOF Then
  STOP  'This will break the code here and you need to check the query statement
Else
  rs.moveFirst
  rsPrevVal1=rs!tableFieldName1TocheckValue
  rsPrevVal2=rs!tableFieldName2TocheckValue
  rsPrevVal3=rs!tableFieldName3TocheckValue

    rs.MoveNext
  Do While Not rs.EOF
    
     if rs!TableFieldName1=rsPrevVal1 And rs!TableFieldName2=rsPrevVal2 And _
        rs!TableFieldName3=rsPrevVal3 Then
        
        rs.Edit
        rs!Day = "Sun"
        rs.Update
     Endif
     
        rs.moveNext

  Loop