Access - Currentdb.Execute UPDATE a General Date field in a table

Posted on 2012-09-11
Last Modified: 2012-09-12
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....?????
Question by:wlwebb
    LVL 29

    Accepted 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?

    Author Comment

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

    Author Comment

    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.

    Author Closing Comment

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

    .......oops and Gals... Sorry I use "guys" generically to mean people.
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now