Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009

    Question Unanswered: Append if else statement

    I'm not even sure if this is possible but here goes....

    I have a Training table which has a boolean (Field named "Add") yes/no field that I will check if people attended and I have an update query that puts all relevant info into my final table. I had this set up so that if I checked yes it would then move the relevant info from the table into my final table AgentRecords.

    I had everything working fine until I was told I needed to have dates missed entered into my final table so I've created two additional tables called Completed Date and Date MissedA and what i'm wondering is this: Can I do some sort of IF Else statement in the criteria field of the append that says if (the boolean field)add <>0 put under Date MissedA else Date Completed?

    This is my original append query in SQL view:

    INSERT INTO AgentRecords ( [Agent ID], [Course Number], [Completion Date], [Date MissedA] )

    SELECT TempAgntRec.Agent_ID, TempAgntRec.Course_Num, TempAgntRec.[Date Completed], TempAgntRec.Missed

    FROM TempAgntRec

    WHERE (((TempAgntRec.Add)<>0));

    Any help is appreciated and I did leave out why I went this route and could explain further if need be but this is my first database and every so often I'm hitting some barriers as some of this just isn't covered in my books or easy to find in google or should I say decipher.
    Last edited by jmantn; 03-17-09 at 19:09.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Why don't you simply create two queries, one for updating [Completion Date] and one for updating [Date MissedA], then call one query or another according to the condition (the boolean field)add <>0?

    Have a nice day!

  3. #3
    Join Date
    Jan 2007
    Provided Answers: 10
    What you're asking for is perfectly possible and I believe the syntax for use with Access / JET is this
    INSERT INTO AgentRecords ( [Agent ID], [Course Number], [Completion Date], [Date MissedA] )
    SELECT TempAgntRec.Agent_ID
           , TempAgntRec.Course_Num
           , Iif(TempAgntRec.Add =  0, TempAgntRec.[Date Completed])
           , Iif(TempAgntRec.Add <> 0, TempAgntRec.Missed)
    FROM   TempAgntRec
    Home | Blog

  4. #4
    Join Date
    Mar 2009
    that's completely over my head right now! Thank you, I've been teaching myself and put over $100 into books and have come a long way in a month but have a long way to go.

    Thanks again

    I should note this returned nothing at first but I inserted a null here:

    , Iif(TempAgntRec.Add = 0, Null,TempAgntRec.[Date Completed])
    , Iif(TempAgntRec.Add <> 0, Null,TempAgntRec.Missed)

    And I can't say in words how happy I am I've just got to the if then section and it gives me a headache :O
    Last edited by jmantn; 03-18-09 at 18:00.

Posting Permissions

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