Results 1 to 14 of 14
  1. #1
    HenkiedeB is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2020
    Posts
    7

    Why are some Field Properties and the Description from a Table not transferred to a Query?

    I use Access only to enter data in a database, and subsequently process them elsewhere.
    Somebody was so kind to help me with designing the structure of the database. But then I was on my own and needed to add a number of new variables to one of the tables.

    I set Data Type, Description as well as Field Properties (such as Format, Decimal places, Validation Rule) for these new variables in the Design View. All works out fine and the Table shows all the Field Properties as I entered them.
    In the large Query that is used to bring data from all Tables together, these new variables are also present. However, the formatting and decimal places for the newly added variables are ignored. Also, in the bottom left of the screen that shows the Query, I do not see the Description of the variable, as I specified in the Table, but only the text "Datasheet View". This is in contrast with the variables that were already present in the table, they are all shown in the Query exactly in the same way as in the Table, including the Description.


    Interestingly, if I try to enter new data in the Query for those newly added variables that are outside the permitted range I set in the Field Properties of the Table, the Validation Rule comes in and reports this is not allowed.

    So my question is what I can do to make the Query show the appropriate formatting for the new variables, in the way as I have specified in the Table?

    Thanks,
    Henkie

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Open the query in design view and go to the properties of each of the newly added fields and set its desired format.
    Click image for larger version. 

Name:	query property.PNG 
Views:	20 
Size:	35.0 KB 
ID:	42198
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    HenkiedeB is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2020
    Posts
    7

    And what if there are >100 variables in the table

    Quote Originally Posted by Gicu View Post
    Open the query in design view and go to the properties of each of the newly added fields and set its desired format.
    Click image for larger version. 

Name:	query property.PNG 
Views:	20 
Size:	35.0 KB 
ID:	42198
    Cheers,
    Dear Vlad,

    Thanks for your suggestion! I see what you mean.
    Now it happens that there are >100 variables in this table, which are all imported into the query by a single reference to that table (tTraitData.* in the figure below).

    So is there also another way to have these Field Properties propagating to the Query, for all variables at once?

    Thanks,
    Henkie

    Click image for larger version. 

Name:	ForAccessForum-Query.jpg 
Views:	20 
Size:	122.7 KB 
ID:	42199

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I believe when you say "variables" you actually mean fields, right? They should "transfer" automatically when you bring the fields into the query. Is the table in the same db as the query or is it linked? If linked I suggest you use the Linked Table Manager to refresh all links. Try to recreate the query now that you have all the fields you need and see what happens.

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

  5. #5
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    generally speaking, the properties of fields in tables are transferred to the tablesheet in queries, so there should not be any need to amend or edit these individually.

    however, I can think of at least two exceptions and there may be others.

    dates: dates in ACCESS are displayed by default using the settings of your computer operating system. if you are a windows user, and have the date and time showing in the bottom right of your screen, dates in ACCESS queries will appear by default in that format. if you want the query to display the date in another format, then you will have to edit the query. you can also change your computer's default date setting to one you prefer.

    currency: if you have a field set to currency, the currency symbol displayed by ACCESS in queries defaults to the one determined from your computer regional settings. again, if you want it to be different, then you will have to edit the query.

    I never use time in ACCESS but I would not be at all surprised if that does the same.


    good luck with your project,



    Cottonshirt

  6. #6
    HenkiedeB is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2020
    Posts
    7
    Quote Originally Posted by Gicu View Post
    I believe when you say "variables" you actually mean fields, right? They should "transfer" automatically when you bring the fields into the query. Is the table in the same db as the query or is it linked? If linked I suggest you use the Linked Table Manager to refresh all links. Try to recreate the query now that you have all the fields you need and see what happens.

    Cheers,
    Hi Vlad,

    Yes, correct, I mean fields.
    The table is and always has been in the same db as the query... I guess in that case each time I open the database the query accesses the data and field properties from each table again?

    Best wishes,
    Henkie

  7. #7
    HenkiedeB is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2020
    Posts
    7
    Hi Cottonshirt,

    Yes, I could imagine that dates and currency could interfere with computer settings.
    However, all my data are simple integers or reals.

    Best,
    Henkie

  8. #8
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    does this answer your question?


    Good luck with your project,


    Cottonshirt

  9. #9
    HenkiedeB is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2020
    Posts
    7

    Update an inheritable property

    Quote Originally Posted by Cottonshirt View Post
    does this answer your question?


    Good luck with your project,


    Cottonshirt

    Hi Cottonshirt,

    Great, that MUST be the solution! ( I have always ignored these little lightning bolt sign when it popped up, now I know I'd better not ignore it).

    However, it works only for the fields where properties were already propagated. In those cases, if I press the 'lightning bolt sign" it tells me a list of the Forms where this field is used and asks which of those Forms I would like to update. It does not ask anything about Queries, but changes are propagated there as well, (and also when I do not click the lightning bolt sign).

    For the fields where it did NOT work up to now, if I change something and subsequently press the lightning bolt sign, I get the message: "No objects needed to be updated". And no updates are done in the Query...
    Does that mean I have to do something else as well?

    Best,
    Henkie

  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
    I would still try to recreate the query now that the tables are finalized...
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    HenkiedeB is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2020
    Posts
    7

    Recreating the Query

    Quote Originally Posted by Gicu View Post
    I would still try to recreate the query now that the tables are finalized...
    Cheers,
    Hi Vlad,

    I followed your advice, on 2 different computers with either Access 2007 or 2010. The results are very consistent:

    - If I create a new Query with the Query wizard where I combine fields from different tables, it works as a charm! Exactly what you had in mind, I guess.

    - But if I copy/paste the SQL statement from the original Query and run it as a new Query, I get the same problem as before!

    The difference as I can see is that in the SQL statement of the old Query, in the SELECT section, the whole table with the "non-behaving fields" is referred to as "tTraitData.*", whereas in the SQL of new Query all the fields in this table are individually specified. However, if I copy/paste all the field names of this table into the SQL statement of the original Query, the problem remains.

    I guess it now gets challenging, but if you have a suggestion...

    CHeers,
    Henkie

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Henkie,
    Not sure I understand the problem. Is it more than one query that you have problems with? Why not re-create it the way you know it works (I think in a brand-new query built from scratch tTraitData.* would also work) and be done with? Access stores the query's details in various system tables (https://www.got-it.ai/solutions/sqlq...system-tables/) so you might have difficulties to refresh them for that particular old query. You could try to import all your objects in a new Access file (make sure you import the tables before the queries) and see what you get.

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

  13. #13
    HenkiedeB is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2020
    Posts
    7
    Quote Originally Posted by Gicu View Post
    Hi Henkie,
    Not sure I understand the problem. Is it more than one query that you have problems with? Why not re-create it the way you know it works (I think in a brand-new query built from scratch tTraitData.* would also work) and be done with? Access stores the query's details in various system tables (https://www.got-it.ai/solutions/sqlq...system-tables/) so you might have difficulties to refresh them for that particular old query. You could try to import all your objects in a new Access file (make sure you import the tables before the queries) and see what you get.

    Cheers,
    Vlad
    Dear Vlad,

    I have read the link you mentioned and now understand a bit better what happens behind the curtains of MsAccess. That is probably also the place where the order of the fields in the Queries is stored, I already wandered where this info would be.
    So I followed your suggestion and rebuilt the Query step by step with the "Create new Query". This made me loose half of the data in this Query, probably because of some Inner Join. So then I started with a Query with the fields from one Table, and then adding information from additional tables step by step. This went fine for the 1st 4 tables, but low and behold, after adding the 5th table, a range of fields in the resulting Query lost its format again! It is apparently not easy to get rid of the past....

    Best wishes,
    Henkie

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Henkie,
    Could you just try to import the 5 tables in a brand new Access file and try building the query in there and see what happens? Are the fields that "lost" their formatting from the 5th table or were they from the previous 4? It seems like a weird behaviour, maybe try to decompile the db.

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

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

Similar Threads

  1. Replies: 2
    Last Post: 01-31-2015, 09:29 PM
  2. Replies: 1
    Last Post: 11-18-2014, 02:16 PM
  3. Replies: 4
    Last Post: 04-06-2014, 12:56 PM
  4. Replies: 1
    Last Post: 02-16-2013, 09:05 AM
  5. Replies: 8
    Last Post: 03-24-2012, 11:03 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