• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 742
  • 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....?????
0
wlwebb
Asked:
wlwebb
  • 3
1 Solution
 
IrogSintaCommented:
Try
CurrentDb.Execute "UPDATE ShiftReportingLVLCtl SET VerifyAllZero=True, VerifyDate= #" & d & "# WHERE ShiftRptgLVLCtlID=" & v, dbFailOnError

Open in new window

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

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

ProcExit:
set db = nothing
Exit Sub
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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