wlwebb
asked on
Access - Currentdb.Execute UPDATE a General Date field in a table
Hello all
Have a form where I have an unbound textbox to receive a date/time Formatted General Date when a user "verifies" some info. Once everything is done I want to Update the table with that Verification Date. Both the Unbound field and the Field in the table are both formatted General Date.
The Code I tried included the # signs but......didn't work....
I thought the # signs were what you used for dates....?????
Have a form where I have an unbound textbox to receive a date/time Formatted General Date when a user "verifies" some info. Once everything is done I want to Update the table with that Verification Date. Both the Unbound field and the Field in the table are both formatted General Date.
The Code I tried included the # signs but......didn't work....
Dim d As Date
d = [frm_DataReporting]![WVLVLControlTotals].Form![txtVerificationDate]
If IsNull([frm_DataReporting]![WVLVLControlTotals].Form![txtVerificationYN]) = False Then
CurrentDb.Execute "UPDATE ShiftReportingLVLCtl SET VerifyAllZero=" & True & ", " & "VerifyDate= #" & d & "#" & " WHERE ShiftRptgLVLCtlID=" & v, dbFailOnError
End If
I thought the # signs were what you used for dates....?????
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Missed the Forms! in front of [frm_... in the d variable......
originally this all wasn't on tabbed forms... all were separate.... I'm trying to fix all these forms to tabbed since they're all interrelated.
originally this all wasn't on tabbed forms... all were separate.... I'm trying to fix all these forms to tabbed since they're all interrelated.
ASKER
THANK YOU!!! for all you help.... All you guys are appreciated
Edited
.......oops and Gals... Sorry I use "guys" generically to mean people.
Edited
.......oops and Gals... Sorry I use "guys" generically to mean people.
just my 2 cents,
1. Whenever I'm going to build a SQL string in code and then execute it, I break that into two steps
strSQL = "UPDATE ShiftReportingLVLCtl SET VerifyAllZero=True, VerifyDate= #" & d & "# " _
& "WHERE ShiftRptgLVLCtlID=" & v
currentdb.execute strsql, dbfailonerror
This allows me to insert a line:
debug.print strSQL in the code to actually see what is being executed or to simply but a breakpoint on the Currentdb.Execute line so I can mouseover the strSQL value and make sure it is formatted properly before attempting to execute it.
2. If you are going to have a series of Execute statements in the same code module, then you should declare a database object at the top of that module and use that instead of using currentdb in every instance. Your code will run faster when you do so.
Dim db as DAO.Database
strSQL = ....
db.Execute strsql, dbfailonerror
strSQL = ...
db.Execute strSQL, dbfailonerror
ProcExit:
set db = nothing
Exit Sub
1. Whenever I'm going to build a SQL string in code and then execute it, I break that into two steps
strSQL = "UPDATE ShiftReportingLVLCtl SET VerifyAllZero=True, VerifyDate= #" & d & "# " _
& "WHERE ShiftRptgLVLCtlID=" & v
currentdb.execute strsql, dbfailonerror
This allows me to insert a line:
debug.print strSQL in the code to actually see what is being executed or to simply but a breakpoint on the Currentdb.Execute line so I can mouseover the strSQL value and make sure it is formatted properly before attempting to execute it.
2. If you are going to have a series of Execute statements in the same code module, then you should declare a database object at the top of that module and use that instead of using currentdb in every instance. Your code will run faster when you do so.
Dim db as DAO.Database
strSQL = ....
db.Execute strsql, dbfailonerror
strSQL = ...
db.Execute strSQL, dbfailonerror
ProcExit:
set db = nothing
Exit Sub
ASKER
MOA can't find the field "[" referred to in your expression.........