Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    ClariceT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2022
    Posts
    9

    If the column doesn't exist, then create a column with the same name

    Hello,



    In a query made based on a linked table, I need that, if a certain column does not exist in the source table, then a column with the same name is created in its place.

    My Sql:
    SELECT [TABLE-A].Code, [TABLE-A].Name, [TABLE-A].[Credit], [TABLE-B].[Debit]
    FROM [TABLE-A], [TABLE-B]

    The problem is precisely in the field in red. It doesn't always come in the source table.
    So I need to check if it comes and if it doesn't, create it in this query.


    Can someone help me?

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    While you can create a calculated field in a query (thus you could create that field/column), you can't get any data from a field that doesn't exist in a table. Assuming that this is only about one field and the table names don't change, consider using code to test if the field exists and run one query if so but another one if not.

    Not sure what you want to do with that field in a query if it's not in the linked table.
    Last edited by Micron; 05-02-2022 at 08:29 AM. Reason: added comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ClariceT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2022
    Posts
    9
    Hi Micron,

    I just need to generate the query, even if it comes empty. I have some forms dependent on the existence of this query, even empty.

    But when the field [Credit] is not present in [TABLE-A], it gives an error and the query is not generated.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    When you create it in the query and it's not in the table it has to be a calculated field. When it's not there it will return whatever the calculation result is, which will show up in the form. When it is there you'll still get the calculation, not the data, so I don't see the point. This is probably one of a very few cases where you should use 1 form for each situation, assuming your form is bound. Otherwise you're going to need a lot of code to prevent the control on the form from raising an error when it can't find its controlsource.

    I think I would test for the field and then open one form or the other, but maybe someone else will have a better idea. Perhaps setting the form control controlsource property on the fly after trapping the error in form load event.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ClariceT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2022
    Posts
    9
    I tried and I couldn't. Could you give me an example syntax?

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I've made several suggestions. Example to try what?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You can use a simple custom function like this to check if the field exists:
    Code:
    Function FieldExists(tablename As String, fieldname As String) As Boolean
    Dim exists As Boolean
    
    
       exists = False
       On Error Resume Next
       exists = CurrentDb.TableDefs(tablename).Fields(fieldname).Name = fieldname
    
    
       FieldExists = exists
    End Function
    In VBA when you open your form that expects the Credit field use that function to open a query which has it coming from TableA or another in which you create it as a calculated field: Credit:"MISSING IN TABLE"

    You can have a custom function returning the appropriate value to avoid that but it would get complicated as you would need to provide the unique Id for each record and its datatype to use to retrieve the right value when it does exist (see attached sample).

    Cheers,
    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    ClariceT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2022
    Posts
    9
    Thanks for the feedback, Gicu!

    I don't think I can make myself understood, but maybe I need something simpler...

    Do you know when you create an empty column with "" in the field label?
    Ex: Credit: ""

    Well, what I need is:
    If [TABLE-A] does not have the [Credit] field, then create an empty column called Credit instead.

    I tried with the field label like this, but I couldn't:
    Credit: SeImed([Credit] is Null;"";[Credit])

    That's why I think a simple SQL maybe should solve it - but I don't know how to write.


    PS: I don't need values in the field, just that the column is created in the query, in case it doesn't exist in the table.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You could open query in design view and enter Credit:"" or add "" AS Credit to your field list.
    Use IsNull(something) when using vba (IIF is a vba function AFAIK) E.G. IIF(isnull([dob]), "",[dob]) AS Credit
    where dob is the field being tested. Maybe that will help with how to do it in sql view but I doubt you can refer to a field that isn't there.
    Methinks I should step out and let Vlad take over as I didn't download that db sample and am getting out of touch here.
    Last edited by Micron; 05-02-2022 at 11:42 AM. Reason: added comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Clarice, I'm afraid you cannot do that using just SQL; if the field is missing the query will prompt you for it even if it is in the False part of the IIF(). So you don't check for [Credit] is Null (which implies the column is present but it is empty), you need to check if the field exists. And that is the reason for the VBA function...

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    ClariceT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2022
    Posts
    9
    Vlad,

    The first function in your example of the message number 7 is for this, right? I don't know how to apply. Forgive my stupidity. Can you help me?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You can do it with SQL alone if you want to deal with popup input prompt every time query is used and field does not exist - which I don't recommend.

    So use VBA to check if field exists and DAO QueryDefs to modify query object or use Gicu's functions. Or don't use query object. Dynamically build SQL statement to use as form RecordSource. Or maybe build query using wildcard instead of explicit field names: SELECT [Table-A].*, [Table-B].* FROM [Table-A], [Table-B];

    Are other queries dependent on this one?

    Gicu's solution should take care of issues of form or report with a textbox that could be bound to a field that doesn't exist or dependent queries.

    Why using a Cartesian relationship query as form RecordSource?

    Strongly advise not to use space nor punctuation/special characters in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The first function checks if the field exists, the second one is the one used in the query to return either an empty column (or in my sample filled with "MISSING") or the corresponding value if the field does exist. I included in the sample two TableA tables, one with the Credit field and one without; just switch them around and run Query1 to see how it works.

    EDIT: I just noticed that this is posted in the SQL Server forum; another solution to this would be to actually run the check if the field exists in one of the first events running when you open your front-end and run a pass-through query to add the Credit column to the SQL table and then refresh the link; that way you don't need to worry about modifying the query. Have a look at the code I use to do that - last link on this page:
    http://forestbyte.com/vba-code-samples/

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    ClariceT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2022
    Posts
    9
    Master Vlad,

    I tried it more calmly and code of the message 7 worked perfectly! You saved my day! Thank very much!

    However, when I tried to repeat the code in another situation, this time generating the query from two crosstab queries instead of tables, it didn't work.

    Do you can saved me again?

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to hear you got it working Clarice and please, save the accolades for the real masters...

    I think (without seeing your actual example)that you don't need this kind of code for crosstab queries. They have a Column Headings property that if you set it properly you can link two crosstabs (you will have to decide if to use equi joins vs. outer joins but that's another topic):
    https://www.quackit.com/microsoft_ac...ccess_2016.cfm
    http://access.mvps.org/access/queries/qry0017.htm
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-03-2021, 08:22 AM
  2. Create a field, if it doesn't already exist
    By jeffatwork in forum Access
    Replies: 3
    Last Post: 03-02-2016, 08:05 PM
  3. Replies: 6
    Last Post: 09-15-2015, 02:18 PM
  4. Replies: 9
    Last Post: 03-02-2015, 06:05 PM
  5. VBA to create PDF and folder if doesn't exist!
    By crxftw in forum Programming
    Replies: 2
    Last Post: 08-08-2011, 08:53 AM

Tags for this Thread

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