Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2002
    Posts
    3

    Unanswered: slight problem in an update query

    hi all...

    I have to access a field called "Date" from "Individual Payments" and use it in this query which is as follows

    UPDATE Member SET Member.ExpirationDate =
    IIF( (((DatePart("m",[Individual Payments].Date) > DateAdd("m",-6,[Member].DateJoined)) AND (DatePart("m",[Individual Payments].Date)<DatePart("m",[Member].DateJoined)))),
    (IIF(DatePart("m",[DateJoined]) =12,
    "1/1/" & DatePart("yyyy",Date())+2,
    DatePart("m",[DateJoined])+1 & "/1/ " & DatePart("yyyy",Date())+1)),
    (IIF(DatePart("m",[DateJoined]) =12,
    "1/1/" & DatePart("yyyy",Date())+1,
    DatePart("m",[DateJoined])+1 & "/1/ " & DatePart("yyyy",Date()))))

    WHERE

    (Member.PersonID=[Individual Payments].PersonID) AND
    ([Individual Payments].PaymentCat="Dues") AND
    ;


    Let me explain this update query.... I have to set the expiration dates for members in a company...If they have paid their dues within 6 months from the month part of the DateJoined ie the [Individual Payments].Date should be in the range of the DateJoined(month) and DateJoined(month-6)....whenever I run this query it opens this dialog and asks for the [Individual Payments].Date or [Individual Payments].PaymentCat or [Individual Payments].PersonID values to be feeded for these parameters...I guess it doesnot recognise them as already existing fields in the Individual Payments...What do I do..Should I use an INNER JOIN for Individual Payments and how do I do it .....Please if someone can help me...Thanx
    Regards
    Sidhartha

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Yes, you'll need a join looking like this:

    UPDATE Member INNER JOIN [Individual Payments]
    ON Member.PersonID = [Individual Payments].PersonID
    SET Member.ExpirationDate =
    IIF( (((DatePart("m",[Individual Payments].Date) > DateAdd("m",-6,[Member].DateJoined)) AND (DatePart("m",[Individual Payments].Date)<DatePart("m",[Member].DateJoined)))),
    (IIF(DatePart("m",[Member].[DateJoined]) =12,
    "1/1/" & DatePart("yyyy",Date())+2,
    DatePart("m",[Member].[DateJoined])+1 & "/1/ " & DatePart("yyyy",Date())+1)),
    (IIF(DatePart("m",[Member].[DateJoined]) =12,
    "1/1/" & DatePart("yyyy",Date())+1,
    DatePart("m",[Member].[DateJoined])+1 & "/1/ " & DatePart("yyyy",Date()))))
    WHERE
    ([Individual Payments].PaymentCat="Dues");

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •