Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Sep 2002
    Posts
    68

    Question Unanswered: Microsoft Access Question

    What I am trying to do is when I look in the MS Access
    Table and see:

    Class00 - add 0 days to the date column and have the
    new date appear in the PDD column

    Class01 - add 10 days to the date column and have the
    new date appear in the PDD column

    Class02 - add 20 days to the date column and have the
    new date appear in the PDD column

    Class03 - add 30 days to the date column and have the
    new date appear in the PDD column

    I am including my zipped database

    Thanks,
    John316
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    It's a simple UPDATE Query :

    Code:
    UPDATE Table1 SET PDD = IIF(Class_Assign = "Class01", Dates, IIF(Class_Assign = "Class02",...)
    OR you can separate it into 3 queries that looks like

    Code:
    UPDATE Table1 SET Table1.PDD = DateAdd("d",10,CDate([Dates]))
    WHERE (((Table1.Class_Assign)='Class02'));
    You can also use Format Function to set teh format of the date you use since you save your dates as TEXT

    JefB - hope it helps

  3. #3
    Join Date
    Sep 2002
    Posts
    68

    JefB Where would.......

    JefB,

    Where would i post the UPDATE and WHERE ststements at?

    John316

  4. #4
    Join Date
    Sep 2002
    Posts
    68

    ALSO THE UPDATE FUNCTION........

    also the update function does not work:

    UPDATE Table1
    SET Table1.PDD = DateAdd("d",10,CDate([Dates]))
    WHERE Table1.Class_Assig)='Class01';

    John316

  5. #5
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    Syntax error:

    Use

    Code:
    UPDATE Table1 
    SET Table1.PDD = DateAdd("d",10,CDate([Dates]))
    WHERE Table1.Class_Assign='Class01';
    And what do you mean by where to put that? Just create a new query, in Access, and copy and paste the code...

    JefB

  6. #6
    Join Date
    Sep 2002
    Posts
    68

    Smile THANK YOU

    Thank you for all your help,

    IT WORK

  7. #7
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: Microsoft Access Question

    Try an if statement
    if colum_class = 'class00'
    then pdd = DateAdd("d", 0, [Date])
    if colum_class = 'class01'
    then pdd = dateadd("d", 10, [date])
    end if


    See if that helps any


    Originally posted by John316
    What I am trying to do is when I look in the MS Access
    Table and see:

    Class00 - add 0 days to the date column and have the
    new date appear in the PDD column

    Class01 - add 10 days to the date column and have the
    new date appear in the PDD column

    Class02 - add 20 days to the date column and have the
    new date appear in the PDD column

    Class03 - add 30 days to the date column and have the
    new date appear in the PDD column

    I am including my zipped database

    Thanks,
    John316

  8. #8
    Join Date
    Sep 2002
    Posts
    68

    Smile Thanks It is now working

    Finally it is working

  9. #9
    Join Date
    Sep 2002
    Posts
    68

    Re: Microsoft Access Question

    JDionne,

    You told me to write a if statement for my Access Problem: Where would I put this if statement at?


    if colum_class = 'class00'
    then pdd = DateAdd("d", 0, [Date])
    if colum_class = 'class01'
    then pdd = dateadd("d", 10, [date])
    end if

    John316



    Originally posted by JDionne
    Try an if statement
    if colum_class = 'class00'
    then pdd = DateAdd("d", 0, [Date])
    if colum_class = 'class01'
    then pdd = dateadd("d", 10, [date])
    end if


    See if that helps any

  10. #10
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: Microsoft Access Question

    The if statment would go in vba code on a controle, say an update button
    the if statment would be the code that ran behind it.

    This may not be the aplication that you needed, I dont know that I totaly understood the problem. Let me know
    Jim



    Originally posted by John316
    JDionne,

    You told me to write a if statement for my Access Problem: Where would I put this if statement at?


    if colum_class = 'class00'
    then pdd = DateAdd("d", 0, [Date])
    if colum_class = 'class01'
    then pdd = dateadd("d", 10, [date])
    end if

    John316

  11. #11
    Join Date
    Sep 2002
    Posts
    68

    JDionne

    Jim,

    All i am trying to do is Update a table. Whenever I see Class01 10 days should be added from the date column and put into the PDD column.

    John316

  12. #12
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    you can write a similar iif statment in a query grid. It will have to be nested thought
    iif(class_id = 'class00',date= dateadd('d',10,classdate),iif(class_id = 'class10',date= dateadd('d',20,classdate),null))

    This is just an example. Let me know if you need more help
    Jim

  13. #13
    Join Date
    Sep 2002
    Posts
    68

    i NEED MORE HELP

    Jim,

    I need more help. I am way lost on what to do now. Can you please write direction for dumbies?

    Thanks,
    John316

  14. #14
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    Code:
    UPDATE Table1 
    SET Table1.PDD = DateAdd("d",10,CDate([Dates]))
    WHERE Table1.Class_Assign='Class01';

    Will become:

    Code:
    UPDATE Table1 
    SET Table1.PDD = DateAdd("d",IIF(Class_Assing = 'Class01', 10, IIF(Class_Assing = 'Class02', 20, IIF(Class_Assing = 'Class03', 30, IIF(Class_Assign = 'Class00',0)))),CDate([Dates]))
    IIF Statement:

    IIF(condition, true part, false part)

    You can also use CInt(RIGHT(Class_Assing,1)) * 10 instead of ALL the IIF, it will take the last number of the field and multiply it by 10, so for example, if it is 'Class01', it take the '1' and multiply it by 10, so it gives 10, but this could give bad results if the Class_Assign is 'Class'

    JefB
    Last edited by JefB; 09-27-02 at 21:49.

  15. #15
    Join Date
    Sep 2002
    Posts
    68

    NO Errors But............

    I have no errors with your IFF statement, but nothing happens no date is added. Why is that?

    John316

    Originally posted by JefB
    Code:
    UPDATE Table1 
    SET Table1.PDD = DateAdd("d",10,CDate([Dates]))
    WHERE Table1.Class_Assign='Class01';

    Will become:

    Code:
    UPDATE Table1 
    SET Table1.PDD = DateAdd("d",IIF(Class_Assing = 'Class01', 10, IIF(Class_Assing = 'Class02', 20, IIF(Class_Assing = 'Class03', 30, IIF(Class_Assign = 'Class00',0)))),CDate([Dates]))
    IIF Statement:

    IIF(condition, true part, false part)

    You can also use CInt(RIGHT(Class_Assing,1)) * 10 instead of ALL the IIF, it will take the last number of the field and multiply it by 10, so for example, if it is 'Class01', it take the '1' and multiply it by 10, so it gives 10, but this could give bad results if the Class_Assign is 'Class'

    JefB

Posting Permissions

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