Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    17

    Unanswered: Expression field in query won't show up in table

    Hello...again

    This is probably very simple, but I can't get this to work...

    I have a form (TRDXCal_frm) based on a query (TRDXCal_Qry), which holds a table called (TRDX_Calibration_tbl). In this query I have a field expression I use to calculate a date: (NextCalDate) which adds the fields (TRDXCalIntervalNumber) and (TRDXLastCalDate).(TRDXCalIntervalNumber) is a number that represents months, and (TRDXLastCalDate) is a date field.

    DateAdd("m",[TRDXCalIntervalNumber],[TRDXLastCalDate])

    The date value shows up fine on my form, but how can I get it to show up in the table in a field called (TRDXNextCalDate) ?


    Thanks,

    Owen

  2. #2
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59
    You can't "as is." Select queries don't update their underlying tables. And even though there are other types (Update, for example) the queries underlying forms are generally SELECT queries.

    You'll probably want to do something slightly different. Try adding an OnOpen or OnLoad event to your form that reads the two fields, concatenates them (the same way your query did) and then writes them to TRDXNextCalDate. You'll need to change the ControlSource property of the field on your form that showed the new date to point to the TRDXNextCalDate field (which is initially empty) instead of to your expression field from your query.

    That approach only makes sense if, by definition, opening the form is meant to initiate the creation of this next date. It does not make sense if this form is used for other reasons (managing data, viewing existing records, etc.). If that's the case, add a button and put the code for updating the field in its onClick event.
    Last edited by mageem; 03-12-04 at 21:48.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Instead of calculating the value in the query, add TRDXNextCalDate to your form and set the control's default to your formula.

    You may also need to create a subroutine that sets the value of this control, and then call the subroutine from the AfterUpdate events of TRDXCalIntervalNumber and TRDXLastCalDate.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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