none
help with date criteria RRS feed

  • Question

  • hi:

    I have a table with 2 fields

    I want to set that if field1 get a specific value field2 Will show the current date

    i got this

    iif([field1]="x",now()) (dont work)

    Thursday, February 11, 2016 7:59 PM

Answers

  • Why are the columns of OLE Object data type?  For Status one would expect the column to be Short Text data type, and the Finish Date to be DateTime data type.  In which case:

    In the Status control's AfterUpdate event procedure you'd put:

    If Me.[Status] = "x" Then
        Me.[Finish Date] = VBA.Date()
    End If

    Or do the equivalent in a macro.

    Ken Sheridan, Stafford, England

    • Marked as answer by eshay1 Tuesday, February 16, 2016 2:18 PM
    Tuesday, February 16, 2016 11:58 AM

All replies

  • i got this

    iif([field1]="x",now()) (dont work)

    Where do you have it? Also, I hope you don't actually have it as a "field." It's better in a query. Just my 2 cents...
    Thursday, February 11, 2016 8:27 PM
  • yes its in a query 

    in a field called date 

    Thursday, February 11, 2016 9:26 PM
  • Is it a SELECT query or an UPDATE query?

    When you say don't work do you get nothing, an error message, wrong response, or what?

    Post the SQL of the query.


    Build a little, test a little

    P.S.
    'Date' is a reserved word and can cause you problems.
    Thursday, February 11, 2016 9:45 PM
  • i got this

    iif([field1]="x",now()) (dont work)

    Where do you have it? Also, I hope you don't actually have it as a "field." It's better in a query. Just my 2 cents...

    in the query i have field called "finish date" i need to put a code in the criteria that when field called "status" get the 

    value finish  the field "finish date" will get the cornet date of the system 

    Friday, February 12, 2016 12:28 PM
  • in the query i have field called "finish date" i need to put a code in the criteria that when field called "status" get the 

    value finish  the field "finish date" will get the cornet date of the system 

    Hi. It doesn't sound right. A criteria filters the records returned by the query. It does not "assign" values to a field. If you're saying you want the "finish date" field to automatically have today's date as soon as the user change the status field to "finish," then you're probably better off doing it at the form level.

    Just my 2 cents...

    Friday, February 12, 2016 3:36 PM

  • Hi. It doesn't sound right. A criteria filters the records returned by the query. It does not "assign" values to a field. If you're saying you want the "finish date" field to automatically have today's date as soon as the user change the status field to "finish," then you're probably better off doing it at the form level.

    Just my 2 cents...

    you can do expression on a table or a query and use "iff"

    but my field was ole data so its not working on a ole field

    i did it on a form by a macro

    thanx 

     
    Tuesday, February 16, 2016 1:12 AM
  • Hi Eshay1,

    We are not able to compare the OLE filed with the string directly. The OLE filed is used to enable uses attch the files and graphics instead of store the text directly.

    So the comparison is based on the real file you were inserted into the OLE filed. For example, we can get the text via the code below if we insert the Word document in the OLE filed:

    Private Sub Form_Load()
    FieldText = Me.Field1.Object.range.Text
    'last character is the spcial code from Word
    FieldText_RemoveSpacialCode = Left(FieldText, Len(FieldText) - 1)
    If FieldText_RemoveSpacialCode = "Test" Then
    Text5.SetFocus
    Text5.Text = Now()
    End If
    End Sub

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, February 16, 2016 3:37 AM
    Moderator
  • Why are the columns of OLE Object data type?  For Status one would expect the column to be Short Text data type, and the Finish Date to be DateTime data type.  In which case:

    In the Status control's AfterUpdate event procedure you'd put:

    If Me.[Status] = "x" Then
        Me.[Finish Date] = VBA.Date()
    End If

    Or do the equivalent in a macro.

    Ken Sheridan, Stafford, England

    • Marked as answer by eshay1 Tuesday, February 16, 2016 2:18 PM
    Tuesday, February 16, 2016 11:58 AM
  • thanx for the help gyes
    Tuesday, February 16, 2016 2:21 PM