Results 1 to 5 of 5
  1. #1
    accessnewbee is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    2

    Question Does a plus sign (+) in "Update to:" have any significance?

    Hello all,

    I'm kinda new to Access, and I've started a new job wherein I've inherited an Access database (with a bunch of tables, queries etc.) from the person who retired. This said person probably didn't know Excel well enough, and therefore decided to use Access to do something that could very easily have been done in Excel. My intention obviously is to scrap Access from the equation, and move everything to Excel (where it belongs).

    So there's this one query (SQL statement listed below) which has a plus sign (+) after the "SET [TableName].[FieldName] =" portion of the SQL statement, and I have no idea what that + sign is doing/used for.

    Would appreciate if someone could explain its use/logic to me...since I've never used/seen something like that used before.



    Code:
    UPDATE [TableName] 
    
    SET [TableName].[FieldName] = +[Employee Information]![Do Not Use Business Group], [Employee Information].[Roll Up Org Level 2] = 
    
    IIf([Employee Information]![Do Not Use Business Unit]<>"unassigned",[Employee Information]![Do Not Use Business Unit],(IIf([Employee Information]![Do Not Use Division]<>"unassigned",[Employee Information]![Do Not Use Division],(IIf([Employee Information]![Do Not Use Sub Division]<>"unassigned",[Employee Information]![Do Not Use Sub Division],IIf([Employee Information]![Department]<>"unassigned",[Employee Information ]![Department],IIf([Employee Information]![Section]<>"Unassigned",[Employee Information]![Section],"")))))));
    Thanks.

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Where is that code coming from? As an update query, its syntax is wrong - an update query looks like this:

    Update Tablename set field1 = value1, field2 = value2,..... (optional where clause)

    I have no idea what the "+" means - I have never seen anything like that.

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You obviously have a background in Excel in order to make the decision you have. It is my experience that many people prefer to solve issues in a program they understand and are comfortable with rather than jump into something new. If what I've said is true, you may wish to get a second opinion as there are things that MS Access can accomplish on which Excel would fall far short. Just a gentle suggestion. As for the Update query, does it run?

  4. #4
    accessnewbee is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    2
    Okay...what I've figured out (in analyzing the data) (I believe) is that the query is setting the value of two fields (i.e. killing two birds with one stone) with the result of the IIF condition.

    Now, that still doesn't answer my question as to whether that + sign is required to be there (or whether it was a "typo"). Yes, I can remove it and try running the query to see if I get the same/desired results.

    The way the query is currently, it does run (with no errors per se).

    @RuralGuy, whilst I fully agree with you, I too often see people using the wrong tool for a job. In this case, Excel is first being used to import some raw data (tab-delimited flat file), then that Excel file is imported into Access (where queries are run to set the values of certain fields, based on some logic), and then the data is exported out to an Excel file again (where the report is produced). IMHO, too complex, too many applications, too many steps, too many chances for errors/inaccuracies.

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Given your description of the process, I don't see a need for Excel. A tab-delimited text file is easily imported directly in to MS Access (to a table), and Access has a good report-generating facility. Is there anything that you are aware of that Excel can do that Access cannot (for your application)?

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

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  2. Replies: 1
    Last Post: 07-18-2015, 07:59 PM
  3. Replies: 5
    Last Post: 05-28-2015, 03:24 PM
  4. Replies: 1
    Last Post: 09-03-2014, 03:27 AM
  5. Replies: 11
    Last Post: 03-29-2012, 02:32 PM

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