Results 1 to 5 of 5
  1. #1
    Zoroxeus is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2005
    Posts
    12

    [access 2007] Update Query And Function

    Hello,

    So I have an update query where for i am passing the value of field A to a functionx in field B and this returns a string in field B.
    So field A is processed and returns me some other string in field B though the function.



    What i have been trying to do without succes is when i run the update query depending of what happens in this function (in a if then else end if statement) i can update field c with for example Update or Not updated.

    What i have tried to do so far was to put a function in field C without any parameter and then call it in functionx ... but it does not seem ti work . maybe i used the wrong syntax... ?


    (As well i an update query do we always have to have existing fields... Or can we create a new field directly (without having to go in table designer)
    What i mean is when i type the name of a new field in the update query i am being told of course that it does not exist. so i was wondering if there is sone sort syntax shortcut to say that a new field has to be created.(


    Thank you

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Zoroxeus View Post
    Hello,

    So I have an update query where for i am passing the value of field A to a functionx in field B and this returns a string in field B.
    So field A is processed and returns me some other string in field B though the function.

    What i have been trying to do without succes is when i run the update query depending of what happens in this function (in a if then else end if statement) i can update field c with for example Update or Not updated.

    What i have tried to do so far was to put a function in field C without any parameter and then call it in functionx ... but it does not seem ti work . maybe i used the wrong syntax... ?
    Don't know. Probably.... Without seeing the query and function with a couple of examples it is hart to tell....



    (As well i an update query do we always have to have existing fields... Or can we create a new field directly (without having to go in table designer)
    What i mean is when i type the name of a new field in the update query i am being told of course that it does not exist. so i was wondering if there is sone sort syntax shortcut to say that a new field has to be created.(
    Yes, you can only run an update query against a table with existing fields.

    However, you can execute some code (DDL - data definition language) to modify the table, then execute the update query. Not the best idea... but possible.

  3. #3
    Zoroxeus is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2005
    Posts
    12
    Hi thanks
    this my the query:

    field A


    field B
    testfield([FieldA])

    field C



    To make it simple for example the code in VBA is:

    if FieldA = "" then

    else


    end if

    -----------

    What should I add in the if statement if i want to update field C as well ? for example if FieldA="" i want to add in field C ="ok" otherwise "not ok" ...

    thanks

  4. #4
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, well I was expecting a SQL string and a full function, but I'll try and work with what I was given.

    There are several ways to proceed:

    -- If you have a saved query, then use two function calls to update fields B & C.

    Functions :
    Code:
    Function FN1(TheField As Integer) As String
    
       If TheField = "" Then
          FN1 = "A"
       Else
          FN1 = "Z"
       End If
    End Function
    
    Function FN2(TheField As Integer) As String
    
       If TheField = "" Then
          FN2 = "OK"
       Else
          FN2 = "Not OK"
       End If
    End Function
    The update query would look like:

    Code:
    UPDATE Table1 SET Table1.FieldB = FN1([Custid]), Table1.FieldC = FN2([custid]);
    ------------------------------------
    You could combine the two functions into one:
    Code:
    Function FN12(TheField As Integer, Selector As String) As String
    
       If Selector = "B" Then
    
          If TheField = "" Then
             FN1 = "A"
          Else
             FN1 = "Z"
          End If
    
       ElseIf Selector = "C" Then
    
          If TheField = "" Then
             FN2 = "OK"
          Else
             FN2 = "Not OK"
          End If
       End If
    End Function
    and the SQL would look like:
    Code:
    UPDATE Table1 SET Table1.FieldB = FN12([Custid],"B"), Table1.FieldC = FN12([custid],"C");
    -----------------------------------------------------

    You could do everything in code, creating the SQL "on-the-fly".

  5. #5
    Zoroxeus is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2005
    Posts
    12
    Thank you thank you that was exactly what i was looking for !
    "how to update several field from within one function" Access 2007 VBA (in case anyone else looks for it

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-15-2012, 03:42 PM
  2. access 2007 update statment
    By jassie in forum Queries
    Replies: 1
    Last Post: 02-28-2012, 05:09 PM
  3. Stock Update using VBA in Access 2007
    By thanosgr in forum Access
    Replies: 19
    Last Post: 02-13-2012, 03:21 PM
  4. Replies: 0
    Last Post: 06-03-2011, 09:03 AM
  5. .Edit function in Access 2007
    By jgelpi16 in forum Programming
    Replies: 3
    Last Post: 11-24-2010, 10:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums