	AllExperts > Using MS Access
	Search

About Sayedaziz
Expertise
database designing,event driven programming,capturing/updating data programmitically using ms access 2000 and ms access 2002

Experience
12 yrs. as a vb programmer

Organizations
Print Media

Publications
Math Skill Test VB6 Game Source Code published at Planet-Source --------------------------------------------------------------- http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=72322&lngWId=1 Holy Quran Source Code published at Planet-Source ------------------------------------------------- http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=1&txtCodeId=72343&txtForceRefresh=82020091635140766 Right to Left Treeview & Listview Source Code published at Planet-Source ------------------------------------------------------------------------ http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=72376&lngWId=1

Education/Credentials
B.Com, AICWA (Associate member of Cost & Works Accountants Of India)

You are here: Experts > Computing/Technology > Business Software > Using MS Access > function for my query

Using MS Access - function for my query

Expert: Sayedaziz - 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.
IMAGE: my query result

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

Add to this Answer Ask a Question