Results 1 to 9 of 9
  1. #1
    Deutz is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    51

    Concat field names in Update SQL

    Hi and thanks in advance,

    I have an Access 2003 db with a table that I want to update from values in form textboxes that are unbound (via an update query).

    I need to update the MyFieldName1 field with values from textboxes with names that are derived by concatenating MyFieldName2 & MyFieldName3

    This SQL crashes as it does not like the Controls reference in there. Is there any way to concat these two fields so that the SQL will get the value from the textbox named and update MyFieldName1 ?



    UPDATE MyTableName SET MyFieldName1 = [Forms]![MyFormName].Controls([MyTableName].[MyFieldName2] & ([MyTableName].[MyFieldName3])


    Thanks

    Deutz

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Your parentheses are off, as is the form reference. Try

    UPDATE MyTableName SET MyFieldName1 = [Forms]![MyFormName].[MyFieldName2] & [Forms]![MyFormName].[MyFieldName3]
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Deutz is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    51
    Thanks for your reply.

    I think you might have misunderstood what it is I'm trying to do.

    I would like to update MyFieldName1 to the value in a form textbox that has a name that can be derived by concatenating the values in the two fields MyFieldName2 & MyFieldName3

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    That's exactly what pbaldy's suggestion does. He's saying the code for your SQL statement is off.

    Let's say your form name is MyFormName
    You have two fields on that form
    MyFieldName2
    MyFieldName3

    now let's say the contents of those two fields are text values and they are
    BLAH
    BLUE

    You would want your SQL statement to say

    UPDATE MyTableName SET MyFieldName1 = 'BLAHBLUE'

    so you'd want your statement (if you're using docmd.runsql)

    docmd.runsql "Update MyTableName SET MyFieldName1 = '" & [Forms]![MyFormName]![MyFieldName2] & [Forms]![MyFormName]![MyFieldName2] & "'"

    Though how this is working without including a unique key to know which record to update I don't know.

  5. #5
    Deutz is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    51
    Sorry, I have obviously not made it clear what I'm trying to do.

    This is what I'm trying to accomplish:

    I have a number of UNBOUND textboxes on a form and I want to save their value in a table but I need to identify which record to save it to.

    Each textbox has a name that is defined in the table by the values in two fields.

    For instance, one of my textboxes may be named ABCDEF.

    On a certain row, Field2 contains ABC (first part of textbox name) and Field3 contains DEF (second part of textbox name). That's the row where I want to save the value of my textbox named ABCDEF (save in Field1).

    Hope this is clearer than mud.


    Thanks for your replies

    Deutz

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If your concantenated field is always the same number of characters wide you can use the left(fieldname, length) or right(fieldname, length) functions to get portions of a string. If there's a dividing character (- or _ etc) and the portions of the concantenated field are irregular lengths you can still get to it using a combination of instr and left/right functions.

    However, I would like to know why you are storing your data this way. If it's an attempt to make a unique key field this is really not a great way to go about it. Your unique key field should have absolutely no relation to the data in the table so that it can never be changed.

    Let's say your original data fields are this

    Field1 = "ABC"
    Field2 = "DEF"
    Field3 = "XXX"

    If they are stored in your table as ABCDEF and XXX then you bring up this record in your form and split out the ABCDEF to ABC and DEF again, what happens if someone changes one of those fields before saving the record back to your table? Is it going to create a new record or is it going to update, if it's going to update how is it going to find the record you were working on if they change the DEF string to GHI? You're better off using an autonumber field as the unique key and doing lookups based on field contents (field1 = 'ABC' AND field2 = 'DEF' as criteria)

  7. #7
    Deutz is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    51
    Hi and thanks for your reply,

    Just a bit of background to help explain what is going on here:
    I started building this database with a few unbound text boxes on a form and this worked fine when it came to saving to the table. I set up an update query that referenced each unbound textbox on the form and the field to save to. So each field mapped directly to a textbox. I figured that I didn’t have to worry too much about normalisation as there were so few columns and not too many rows. However, over time the project grew and grew and before I knew it I had too many fields in the table/unbound textboxes and decided to normalise things a bit to speed up select queries underlying reports.

    This did speed up my select queries considerably but meant a more complicated method of saving textbox values to the table as now the textboxes do not map to a single field but rather had to be identified by the combination of three fields, if you get my meaning.

    For instance:

    In the original scenario before normalisation let’s say I had a textbox named txt111RevenueAAA and I would save the value to a field named 111RevenueAAA. So each textbox had a corresponding field in the table based on textbox name, minus the prefix txt.

    This was fine until I had more textboxes/fields than is practical.

    I realised that I could reduce the number of columns in the table by adding FirstCategory and SecondCategory fields which would store the categories referenced in the textbox name, in the case of txt111RevenueAAA this would be 111 and AAA

    FirstCategory could be any of: 111, 222, 333, 444, 555, 666
    SecondCategory could be any of: AAA, BBB, CCC

    So the total combinations of FirstCategory and SecondCategory would result in 18 rows. So for Revenue there were 18 textboxes defined by the two Categories. Hence, txt111RevenueAAA, txt111RevenueBBB, txt111RevenueCCC, txt222RevenueAAA etc etc. I deleted all 18 of the Revenue Fields in the table and ended up with just the one field named Revenue. When the user first saves a record in the parent table of this child table, my idea was to save all 18 rows in this child table and pre-populate the ForeignKey, FirstCategory and SecondCategory fields. Any future edits relating to that foreign key would just be an update of those 18 existing rows. So all 18 combinations are there in the table and the user will never get to update values in fields FirstCategory or SecondCategory, just the Revenue field and other similar fields.

    The table does have an auto number primary key and a foreign key to relate it to it’s parent table.

    There are also a couple of dozen fields in the table that are similar to Revenue in that they have both first and second categories.

    Here is an example of what the table looks like with all 18 rows populated for one foreign key:
    Code:
    PrimKey|ForeignKey|FirstCategory|SecondCategory|Revenue|Cases
    1 |265 |111 |AAA | |
    2 |265 |111 |BBB | | 
    3 |265 |111 |CCC | | 
    4 |265 |222 |AAA | |
    5 |265 |222 |BBB | | 
    6 |265 |222 |CCC | | 
    7 |265 |333 |AAA | |
    8 |265 |333 |BBB | | 
    9 |265 |333 |CCC | | 
    10|265 |444 |AAA | |
    11|265 |444 |BBB | | 
    12|265 |444 |CCC | | 
    13|265 |555 |AAA | |
    14|265 |555 |BBB | | 
    15|265 |555 |CCC | | 
    16|265 |666 |AAA | |
    17|265 |666 |BBB | | 
    18|265 |666 |CCC | | 
    So I was trying to find an easy way to update the correct column and row in the table for each textbox. Or should I be storing the data in a completely different way?


    Thanks

    Deutz
    Last edited by Deutz; 09-20-2011 at 12:58 AM.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    it looks to me like you're trying to create a data entry method for a matrix, but not knowing exactly what you intend to do with the data I can't really offer you a suggestion for a way to do it better.

    I wrote this example for a different forum but it's a method for entering data into a matrix where you can modify the row and column headers. You'd just have to adapt it to your situation.

  9. #9
    Deutz is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    51
    Thanks rpeare,

    I appreciate your assistance and will take a look at your matrix example to get some ideas.

    Regards
    Deutz

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

Similar Threads

  1. Group concat
    By iostream in forum Queries
    Replies: 1
    Last Post: 02-23-2011, 12:29 PM
  2. TransferSpreadsheet - Use first row as field names
    By jgelpi16 in forum Programming
    Replies: 1
    Last Post: 02-23-2011, 11:41 AM
  3. using like with field names
    By TheShabz in forum Queries
    Replies: 4
    Last Post: 10-07-2010, 05:11 PM
  4. concat problem
    By leahcim_32 in forum Access
    Replies: 1
    Last Post: 08-28-2009, 05:31 AM
  5. Quick way to stuff field names into text field
    By kfinpgh in forum Programming
    Replies: 0
    Last Post: 01-04-2007, 01:13 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