Results 1 to 4 of 4
  1. #1
    kilgorq is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    3

    Strange issue with a query when using an expression.

    I have 2 Tables and I am combining the data from them into a text box on a form.

    Table 1 has a list of color
    Table 2 has another list of colors.

    When I create the query and combine the 2 colors everything works perfectly unless the name of the color in the first table is Blue.

    I have tried

    Color: [tbl_WireColors].[Color] & "/" & [tbl_WireColorsStripe].[StripeColor]
    Color: [tbl_WireColors]![Color] & "/" & [tbl_WireColorsStripe]![StripeColor]

    Color: [tbl_WireColors].[Color] & " - " & [tbl_WireColorsStripe].[StripeColor]


    Color: [tbl_WireColors]![Color] & " - " & [tbl_WireColorsStripe]![StripeColor]

    I copy it from access and paste it into excel it gets even stranger.

    Also when what is the difference between . and ! When specifying a field from a table.?

    From the database (The Primary and Stripe colors are swapped in the last 2 columns.)

    Click image for larger version. 

Name:	From DataBase.png 
Views:	14 
Size:	61.3 KB 
ID:	47029

    From Excel

    Click image for larger version. 

Name:	From Excel.png 
Views:	12 
Size:	16.0 KB 
ID:	47030

  2. #2
    kilgorq is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    3
    I just figured out that this only happens when it is the Primary Key WireColorID of 2. Anything I put on that line does this. I added Blue to the bottom of the table and changed all of the references from WireColorID of 2 to the new WireColorID and it works fine.

    Why????

    I still would like to know about the difference of . vs !

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Also when what is the difference between . and ! When specifying a field from a table.?
    the . means that whatever is after the . needs to already exist, otherwise you will get an error at compile time. It also has the benefit of providing a dropdown so you can select the required value. The ! means that whatever is after yhe ! does not have to exist at compile time, but must exist at runtime otherwise you will get a runtime error.

    Consider this code

    Code:
    dim rs as dao.recordset
    set rs=currentdb.openrecordset("select * from sometable")
    while not rs.EOF
        rs!fieldname1=something
        rs.movenext
    wend
    The recordset exists as an object (you have declared it with the dim statement) at compile time so you can use . - recordsets have a number of predefined properties such as EOF and movenext
    But the fields do not - that won't occur until runtime when the rs object gets populated - so you have to use ! when referring to the fields

    At the top of every module you should have Option Explicit and when you make changes, compile the code before running it. This will trap all compile errors such as not declaring an object, duplicate names, typo, etc. You can have this set automatically for new modules by going into the tools>options in the vba window and ticking 'require variable declaration'

    If you don't compile then all compile errors will occur at runtime - Access will compile then run the code.

    So use the . not the !. If you typed rs.fieldname1=something then this will fail at compile time.

    With regards your main question, really need to see the full query, but from the little provided I would guess that you have blank fields. Also trying to rationalise yo saying 'unless the name of the color in the first table is Blue.' But looks like Brown has the same issue

    I just figured out that this only happens when it is the Primary Key WireColorID of 2. Anything I put on that line does this. I added Blue to the bottom of the table and changed all of the references from WireColorID of 2 to the new WireColorID and it works fine.

    Why????
    again -would need to see your query and also perhaps your table. Best guess for why is you (or someone) had removed Blue from your table

  4. #4
    kilgorq is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    3
    Quote Originally Posted by Ajax View Post
    the . means that whatever is after the . needs to already exist, otherwise you will get an error at compile time. It also has the benefit of providing a dropdown so you can select the required value. The ! means that whatever is after yhe ! does not have to exist at compile time, but must exist at runtime otherwise you will get a runtime error.

    Consider this code

    Code:
    dim rs as dao.recordset
    set rs=currentdb.openrecordset("select * from sometable")
    while not rs.EOF
        rs!fieldname1=something
        rs.movenext
    wend
    The recordset exists as an object (you have declared it with the dim statement) at compile time so you can use . - recordsets have a number of predefined properties such as EOF and movenext
    But the fields do not - that won't occur until runtime when the rs object gets populated - so you have to use ! when referring to the fields

    At the top of every module you should have Option Explicit and when you make changes, compile the code before running it. This will trap all compile errors such as not declaring an object, duplicate names, typo, etc. You can have this set automatically for new modules by going into the tools>options in the vba window and ticking 'require variable declaration'

    If you don't compile then all compile errors will occur at runtime - Access will compile then run the code.

    So use the . not the !. If you typed rs.fieldname1=something then this will fail at compile time.

    With regards your main question, really need to see the full query, but from the little provided I would guess that you have blank fields. Also trying to rationalise yo saying 'unless the name of the color in the first table is Blue.' But looks like Brown has the same issue

    again -would need to see your query and also perhaps your table. Best guess for why is you (or someone) had removed Blue from your table
    Blue is in the table.

    The Brown does not have the same issue. I can see where it would look that way but the Brown that is Brown/ does not have a stripe color. So the expect result is "Brown/" Also the Blue does not pick up the / either.

    I always use option Explicit. Thanks for the information on . vs ! I will make sure to use .

    I found that it was something to do with any data that was in that record (row) in the table. I moved Blue to a new record and changed my references to the new record and now it is working.

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

Similar Threads

  1. #Error - Very Strange Issue
    By whollycow in forum Queries
    Replies: 1
    Last Post: 10-08-2020, 07:51 PM
  2. Expression issue using query design
    By osas in forum Queries
    Replies: 2
    Last Post: 10-27-2017, 06:25 AM
  3. Very Strange subform data entry issue
    By RayMilhon in forum Forms
    Replies: 3
    Last Post: 06-05-2015, 10:46 AM
  4. Very strange issue: form opens blank
    By spleewars in forum Programming
    Replies: 5
    Last Post: 06-01-2012, 12:47 PM
  5. Strange issue with Access 2002 - Any suggestions?
    By mlopezkimco in forum Access
    Replies: 0
    Last Post: 07-30-2008, 09:25 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