none
IIF statement in an Update query in Access 97

    Question

  • Good Day All.  Please Help.

    I built an update query that I would like to update a table's field based on the data within that table that will execute upon exit of the form of which the data is entered.

    I need to for the data to produce the answer "yes" if the Recored Added Time is before 3:30 (formatted as short date), otherwise it needs to display "no".

    The fields in my query are...

    Field:  Ticket #  
    Table:  Postage Table
    Update To:  (left blank)
    Criteria:  (left blank)
    or etc:  (left blank)

    Field:  [Mail Time By 15:30]:  IIf([Record Added Time]< 15:30,"Yes","No)               
    Table:  Postage Table
    Update To:  (left blank)
    Criteria:  (left blank)
    or etc:  (left blank)

    Field:  [Mail Time By 15:30]:  IIf([Record Added Time]< 15:30,"Yes","No)               
    Table:  Postage Table
    Update To:  (left blank)
    Criteria:  (left blank)
    or etc:  (left blank)

    Field:  Record Added Time
    Table:  Postage Table
    Update To:  (left blank)
    Criteria:  (left blank)
    or etc:  (left blank)

    When I click Run I received the Message, “The expression you entered contains invalid syntax.  You omitted and operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation”, giving me the option to click ok or help (my help option is not installed).

    Thank you for any assistance you can give me!!!!!

    Sunday, June 23, 2013 12:01 AM

Answers

  • Is 'Mail Time by 15:30' an existing field in the 'Postage Table' table?

    If so, use it like this:

    Field: Mail Time by 15:30
    Table: Postage Table
    Update To: IIf([Record Added Time]<#15:30#,"Yes","No")
    Criteria: (left blank)

    Please note the # characters that enclose the time value 15:30, and the closing quote " after No.

    If 'Mail Time by 15:30' is not a field in the table, you cannot set it in an update query. You can return it in a select query based on the table, which is a better idea anyway since it is derived information. You'd use

    [Mail Time by 15:30]: IIf([Record Added Time]<#15:30#,"Yes","No")

    in the Field: row of a select query based on Postage Table.


    Regards, Hans Vogelaar

    Sunday, June 23, 2013 12:13 AM

All replies

  • Is 'Mail Time by 15:30' an existing field in the 'Postage Table' table?

    If so, use it like this:

    Field: Mail Time by 15:30
    Table: Postage Table
    Update To: IIf([Record Added Time]<#15:30#,"Yes","No")
    Criteria: (left blank)

    Please note the # characters that enclose the time value 15:30, and the closing quote " after No.

    If 'Mail Time by 15:30' is not a field in the table, you cannot set it in an update query. You can return it in a select query based on the table, which is a better idea anyway since it is derived information. You'd use

    [Mail Time by 15:30]: IIf([Record Added Time]<#15:30#,"Yes","No")

    in the Field: row of a select query based on Postage Table.


    Regards, Hans Vogelaar

    Sunday, June 23, 2013 12:13 AM
  • Post the SQL of your query by opening in design view, click on VIEW, select SQL View, highlight all, copy, and paste in a post.

    Build a little, test a little

    Sunday, June 23, 2013 1:22 AM
  • Do you really need to store Yes/No? Why not just use a calculated field?

    SELECT Table1.mTime, mTime<#15:30:0# AS bInTime
    FROM Table1;

    Here's a query datasheet & visual presentation (form with conditional formatting on mTime field):



    Vladimir Cvajniga


    • Edited by Vladimir Cvajniga Sunday, June 23, 2013 5:01 AM changed image, added image comment
    Sunday, June 23, 2013 4:57 AM
  • SELECT Table1.mTime

    WHERE mTime <#15:30:0# 

    FROM Table1;


    Build a little, test a little


    • Edited by KARL DEWEY Sunday, June 23, 2013 5:10 AM error
    Sunday, June 23, 2013 5:08 AM
  • Generally speaking, you don't need any extras for a great visual presentation! All you need is a conditional formatting:


    Vladimir Cvajniga

    Sunday, June 23, 2013 5:22 AM
  • I don't remember but if you don't write directly in SQL editor you must use semicolon instand of ,:

    Field:  [Mail Time By 15:30]:  IIf([Record Added Time]< 15:30;"Yes";"No)   


    Michał

    Sunday, June 23, 2013 8:58 PM
  • I don't remember but if you don't write directly in SQL editor you must use semicolon instand of ,:

    Field:  [Mail Time By 15:30]:  IIf([Record Added Time]< 15:30;"Yes";"No)   


    Michał


    That depends on your system settings - you have to use the list separator specified in the Regional Settings control panel. For most English-speaking countries, this is the comma (,0. For countries that use comma as decimal separator, it's usually the semi-colon (;).

    Regards, Hans Vogelaar

    Sunday, June 23, 2013 9:03 PM
  • I never check why directly in sql I can use comma (,) but in design view I have to use ;. Thanks.

    Michał

    Sunday, June 23, 2013 9:16 PM
  • SQL is US-centric.

    If you edit a query in SQL view, you should use point (.) as decimal separator, comma (,) as list separator and either US date format mm/dd/yyyy or ISO date format yyyy-mm-dd.

    Access uses your Windows settings.

    If you edit a query in design view, you should use your local decimal separator, list separator and date format, all as specified in the Regional Settings control panel.


    Regards, Hans Vogelaar

    Sunday, June 23, 2013 9:20 PM
  • It worked! 

    Hans THANK YOU!!!!!!!  You are AWESOME!!!!!!!!!!

    I SINCERLY appreciate your help with this! 


    Thursday, June 27, 2013 12:44 AM
  • Thank you so much for your assistance!!!!!

    :-)

    Thursday, June 27, 2013 12:52 AM
  • Thank you so much for your assistance!!!!!

    :-)

    Thursday, June 27, 2013 12:52 AM
  • Thank you so much for your assistance!!!!!

    :-)

    Thursday, June 27, 2013 12:52 AM
  • Thank you so much for your assistance!!!!!

    :-)

    Thursday, June 27, 2013 12:52 AM
  • Thank you so much for your assistance!!!!!

    :-)

    Thursday, June 27, 2013 12:53 AM
  • Thank you so much for your assistance!!!!!

    :-)

    Thursday, June 27, 2013 12:53 AM
  • Thank you so much for your assistance!!!!!

    :-)

    Thursday, June 27, 2013 12:53 AM
  • Thank you so much for your assistance!!!!!

    :-)

    Thursday, June 27, 2013 12:53 AM
  • Thank you so much for your assistance!!!!!

    :-)

    Thursday, June 27, 2013 12:54 AM