Problem changing the value of a table field!

[Paulo Gonçalves]

Hello,
I'm trying using sql to insert into a field a value in a
table, in this table i have 4 fields:
[Log_id],[User_Id],[Data_in] and [Data_out], when i enter
the db i fill fields [User_id] and [Data_in] and the
problem comes when i want to close db and set[Data_out] to
the last user.

I'm using this code in a module:

Function Sair()
On Error GoTo Sair_Err
Dim strSQL As String

'Faz o registo de Saida do utilizador

strSQL = "Update [tblLog] set [Data_out]='" & Now()
& "'" _
& " WHERE [tblLog].[Data_out]= '" & Null &"'"

DoCmd.RunSQL strSQL

DoCmd.Quit



Sair_Exit:
Exit Function

Sair_Err:
MsgBox Error$
Resume Sair_Exit

End Function

Can someone give me a help please!

 

[tina]

try this

strSQL = "Update tblLog set Data_out = Now WHERE Data_out
Is Null"

 

[Jeff Boyce]

Paulo

Your post mentioned "set [Data_out] to the last user", but your SQL seems to
be trying to set that field to a Date/Time value.

If you want to set a date/time value, I believe you need to bracket the
value with "#". If you want to set a text value, I believe you need to
bracket with apostrophe "'".

Something like: " ... set [Data_out]=#" & Now() & "#" & " WHERE ..."

Good luck

Jeff Boyce
<Access MVP>

 

[Paulo Gonçalves]

I apreciate your help, i manage to resolve the situation
This way:

Function Sair()
On Error GoTo Sair_Err

Dim strSQL As String
Dim strSQL2 As String
Dim strSQL3 As String

'Regista a data e hora de saida do user activo
strSQL = "Update [tblTmp] set [Data_out]='" & Now()
& "'"

'Copia os dados de entrada e saida do user activo para
tblLog

strSQL2 = "INSERT INTO tblLog (User_id, Data_In,
Data_out)" _
& "SELECT DISTINCTROW tblTmp.User_id, tblTmp.Data_In,
tblTmp.Data_out FROM tblTmp"

'Apaga os registos em tblTmp
strSQL3 = "DELETE tblTmp.* FROM tblTmp"

'Desativa as mensagens de aviso do sistema
DoCmd.SetWarnings WarningsOff

DoCmd.RunSQL strSQL
DoCmd.RunSQL strSQL2
DoCmd.RunSQL strSQL3


'Sai da Base de Dados
DoCmd.Quit


Sair_Exit:
Exit Function

I now it's a more complicated but it solves my problem!

Thank you all very much!!

-----Original Message-----
try this

strSQL = "Update tblLog set Data_out = Now WHERE Data_out
Is Null"

-----Original Message-----
Hello,
I'm trying using sql to insert into a field a value in a
table, in this table i have 4 fields:
[Log_id],[User_Id],[Data_in] and [Data_out], when i enter
the db i fill fields [User_id] and [Data_in] and the
problem comes when i want to close db and set[Data_out] to
the last user.

I'm using this code in a module:

Function Sair()
On Error GoTo Sair_Err
Dim strSQL As String

'Faz o registo de Saida do utilizador

strSQL = "Update [tblLog] set [Data_out]='" & Now()
& "'" _
& " WHERE [tblLog].[Data_out]= '" & Null &"'"

DoCmd.RunSQL strSQL

DoCmd.Quit



Sair_Exit:
Exit Function

Sair_Err:
MsgBox Error$
Resume Sair_Exit

End Function

Can someone give me a help please!
.

.