Link to home
Start Free TrialLog in
Avatar of wlwebb
wlwebbFlag for United States of America

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....

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

Open in new window


I thought the # signs were what you used for dates....?????
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wlwebb

ASKER

Run-time error 2465
MOA can't find the field "[" referred to in your expression.........
Avatar of wlwebb

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.
Avatar of wlwebb

ASKER

THANK YOU!!! for all you help.... All you guys are appreciated

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