Records Not getting updated (1 Viewer)

[sbaud2003]

Hi Sir/Madam
I am usimg the forlloing SQL, but the records are not updating.


UPDATE MEMBERS SET MEMBERS.EL = [EL]+(DateDiff("d",[FORMS]![EMPLOYEE_LEAVE_MASTER]![TXTFROM],[FORMS]![EMPLOYEE_LEAVE_MASTER]![TXTTO])+1)
WHERE ((([FORMS]![EMPLOYEE_LEAVE_MASTER]![TXLV])="EL") AND ((MEMBERS.ACCOUNT_NO)=[FORMS]![EMPLOYEE_LEAVE_MASTER]![TXTAC]));


can you help me.

 

[Minty]

If you convert that to a select query does it select any records?

How are you running this query, is it a saved query run from a command?

 

[The_Doc_Man]

My initial question is the data type of the fields represented in [Forms]![EMPLOYEE_LEAVE_MASTER]![TXTFROM] and [Forms]!...![TXTTO], which you are using in a context where they MUST be of DATE data type.

Further, your WHERE clause has field [TXTAC] used with syntax that would make a difference if their data types were not the same. (Either both string or both numeric would work; mixed format - would not.)

Minty's question also is important, and his "text by converting it to a SELECT" question is related to my second comment.

 

[sbaud2003]

My initial question is the data type of the fields represented in [Forms]![EMPLOYEE_LEAVE_MASTER]![TXTFROM] and [Forms]!...![TXTTO], which you are using in a context where they MUST be of DATE data type.

Further, your WHERE clause has field [TXTAC] used with syntax that would make a difference if their data types were not the same. (Either both string or both numeric would work; mixed format - would not.)

Minty's question also is important, and his "text by converting it to a SELECT" question is related to my second comment.

[Forms]![EMPLOYEE_LEAVE_MASTER]![TXTFROM] and [Forms]!...![TXTTO] are date type and TXTAC is a numeric.

 

[arnelgp]

why do you need to add Form variable to your query:

[FORMS]![EMPLOYEE_LEAVE_MASTER]![TXLV]="EL"

use code to do this:

Dim diff As Integer, sql As String
If [FORMS]![EMPLOYEE_LEAVE_MASTER]![TXLV] & "" = "EL" Then
    diff = DateDiff("d", [FORMS]![EMPLOYEE_LEAVE_MASTER]![TXTFROM],[FORMS]![EMPLOYEE_LEAVE_MASTER]![TXTTO])+1" 
    sql = "UPDATE MEMBERS SET [EL] = Nz([EL], 0) + " & diff & " Where MEMBERS.ACCOUNT_NO='" & [FORMS]![EMPLOYEE_LEAVE_MASTER]![TXTAC] & "'"
    currentdb.Execute sql, dbFailOnError
End If
End If

 

[ebs17]

but the records are not updating

You use a filter. If the filter does not return true for any record, no record will be available.