• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 824
  • Last Modified:

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....?????
  • 3
1 Solution
CurrentDb.Execute "UPDATE ShiftReportingLVLCtl SET VerifyAllZero=True, VerifyDate= #" & d & "# WHERE ShiftRptgLVLCtlID=" & v, dbFailOnError

Open in new window

What error message are you getting anyway?
wlwebbAuthor Commented:
Run-time error 2465
MOA can't find the field "[" referred to in your expression.........
wlwebbAuthor Commented:
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.
wlwebbAuthor Commented:
THANK YOU!!! for all you help.... All you guys are appreciated

.......oops and Gals... Sorry I use "guys" generically to mean people.
Dale FyeCommented:
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

set db = nothing
Exit Sub
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now