Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 54
  1. #31
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Update: I changed the SQL statement that was there with the one you suggested, and I'm getting the error message "Type mismatch in expression"

  2. #32
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, try the suggested SQL statement as lboReferral RowSource. As previously noted, have to change Reference to a number type field first. And the requery code in form Current event.

    Revised SQL statement:

    SELECT tblClient.Reference, tblEvent.EventID, tblEvent.EventNatureID, tblClient.ClientID, IIf([EntityName] Is Null,[PrimaryFirstName],[EntityName]) AS EntName
    FROM tblContact RIGHT JOIN (tblEvent RIGHT JOIN tblClient ON tblEvent.ClientID = tblClient.Reference) ON tblContact.EntityID = tblClient.Reference
    WHERE (((tblClient.ClientID)=[Text177]));

    Name is a reserved word. Don't use reserved words as names.
    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.

  3. #33
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Where am I using "Name" incorrectly? I don't want to use reserved words as names if I can help it.

    I'm trying to change Reference to number type field, but when I try to go to datasheet view, I keep getting the error message of "Type mismatch in expression."

    Also, I am still confused about the requery code in form Current Event. Don't even understand enough about it to know what to ask.

    Is that something to be typed into VBA code?
    Private Sub Form_Current(event)
    Me.list183.requery
    End Sub

    ??

  4. #34
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You used Name as an alias field name in query shown in post 30.

    You already have tons of VBA code, including frmClient Current event. You did not build this?

    Search VBA for Me.lboEvent.Requery and add a new line just below it Me.lboReferral.Requery
    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.

  5. #35
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    I did not build this. I inherited this about a month ago.

    I'm still getting "Type mismatch in expression" whenever I try to go from design view into form view.

  6. #36
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Reference field must be changed to number type in tblClient.

    Possibly this will cause some issues in other areas.
    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.

  7. #37
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    I do see how that would cause issues. The reference is only a number if they were referred by another client. If they were referred by say, Yellow Pages, then it won't have a number in the reference field, but Yellow Pages.

    Does that blow this whole thing out of the water?

  8. #38
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes it does.

    Need a number code for Yellow Pages, like 99999. Or leave it empty (Null).
    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.

  9. #39
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Also, is this all taking into account that the individuals on tblClient have no name (Only businesses have EntityName), and therefore their name has to be taken from tblContact?

  10. #40
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Quote Originally Posted by June7 View Post
    Yes it does.

    Need a number code for Yellow Pages, like 99999. Or leave it empty (Null).
    I just had a flash of inspiration (I think). Everyone who has ClientReferralTypeID of 1 has a number in the Reference field.

    Is there a way to make this whole query only look at the referrals that have "1" as ClientReferralTypeID?

  11. #41
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Filtering doesn't resolve issue of how to link tables when field data types are not the same. If you want to see information that pertains to the Reference then need to be able to link this field to tblContact and tblEvent. Otherwise might be accomplished with a bunch of DLookup() expressions but that can severely impact performance. And this will surely rear its ugly head again in another situation.

    Why is EntityID field empty for every record in tblContact?

    Also advise no spaces or special characters/punctuation (underscore is exception) in naming convention. There are many fields with # in the name. Correcting this flaw would be a significant effort. I use Rick Fisher's Find & Replace for Access to accomplish edits like that.

    It would help analysis if data was somewhat valid. None of the ContactID values in tblContact are reflected in the ContractingPartyContactID field of tblEvent. tblBusinessType has 16 records for type "dtf".

    This does not appear to be a fully functional db but is still in development and none of this data is real.
    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.

  12. #42
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    On my end, EntityID is completely full; no empty spaces.
    I agree that it would help analysis if the data were valid. Unfortunately, the vast majority of data is sensitive, so I had to delete all of that, and then some more to bring it under the 2 Mb limit in order to upload it.

    The database is fully functional when all the data is in place (88.45Mb of it). The frustrating bit is that it was built so many years ago piecemeal, and I've recently inherited it with no Access background and am having to learn everything on the fly with a sloppily built DB.

    Thankfully, this forum exists.

    I may have to give up on my dream of having the clients and number of events shown in a listbox. However, there is a different option: the textbox that has the functioning DCount expression in it could have an "on double click" expression built in that would, when double clicked, open an amended table that shows the clients and events reflected in the number shown.

    Would that be easier than what I've been trying to accomplish?

  13. #43
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    By valid, I mean the data makes sense - doesn't have to be 'real' for you but has integrity and is relatable. None of the records in tblContact and tblEvent can be related on ContactID and ContractingPartyContactID fields.

    I am working with backend as you posted it and EntityID field has no values.

    As for your new idea - that would be showing the events associated with a selected Reference? What do you mean by 'amended table'? Maybe open a form or report:

    DoCmd.OpenReport "report name", acViewPreview, , "ClientID=" & Me.Referral

    But I suppose listbox RowSource could do that as well:

    SELECT tblClient.ClientID, tblEvent.EventID, tblEvent.EventNatureID, tblClient.ClientID, IIf([EntityName] Is Null,[PrimaryFirstName],[EntityName]) AS EntName
    FROM tblContact RIGHT JOIN (tblEvent RIGHT JOIN tblClient ON tblEvent.ClientID = tblClient.ClientID) ON tblContact.EntityID = tblClient.ClientID
    WHERE (((tblClient.ClientID)=Me.Referral));

    But if what you want is to show ALL the referrals associated with a ClientID, back to the issue of linking on Reference field.
    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.

  14. #44
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    I thinking opening a report would be best, but I'm afraid I don't know how to create it, and make it show what I am looking for.

    I'm sure it's possible, but basically I just want the report to show me every client or entity that has the current client's ID as their reference.

  15. #45
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    As already described, need to link related info tables on Reference field if you want to show info related to the Reference.

    If you don't want to show info related to the Reference, then is a simple filter on tblClient.
    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.

Page 3 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 07-13-2015, 12:47 PM
  2. Replies: 8
    Last Post: 10-03-2013, 08:11 AM
  3. Transform Column values in Table
    By Mnelson in forum Access
    Replies: 1
    Last Post: 06-11-2012, 03:06 PM
  4. 3 values in 1 Column - Get Count of Each Value
    By Ghoztrider in forum Queries
    Replies: 1
    Last Post: 01-09-2012, 03:26 PM
  5. Replies: 2
    Last Post: 11-18-2009, 06:49 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