Type on a form - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Type on a form

John Muir

3 years ago

I use tabs on some forms. The forms are based on queries.

On one of the tabs this field, =([firstname or nick] & " " & [tcontacts.clastname]), works fine on the form.

However, above the tabs, I have the same string, =([firstname or nick] & " " & [tcontacts.clastname]), and it shows: #Type!

I've read and watched Richard's quick tips, checked with ChatGpt, but can't figure out how to fix.

I've had this for years and it's worked.

ChatGPT says: The #Type! error in Microsoft Access typically occurs when there is a data type mismatch or an expression is trying to perform an invalid operation. In your case, the expression you provided seems correct for concatenating the first and last names from the "tcontacts" table.

Thanks for any help.

John

	David Burns @Reply 3 years ago
As you are using the same string, have you checked that the format of both fields are the same i.e. they are both short or long text and not number or date/time, etc?

	Kevin Robertson @Reply 3 years ago
What do you mean by 'above the tabs'? Please post some screenshots.

John Muir OP @Reply

3 years ago

I'm pasting the sql view below thinking that may help...

David, yes, using the identical string. Has worked for years. Both fields are the same type.

I work on my db at home and copy same from work and paste to my server which i access via vpn. Then i copy it and the back end to my desk top. Each time i do that i created a folder on my desk top and date it. The DB i worked on from 11/29/2023 works fine. Also each time i copy and paste, I do a compact and repair to both the front end and the back end, separately...

Kevin, I have a set of tabs which takes up about 75% of the lower part of the detail page. The upper 25% are fields and they are situated above the tabs. This sort of serves as a snapshot of the most important data.

Details

SELECT tNewCases.ID, ([annuity (amount)]+[annuity (amount)2]+[annuity (amount)3]+[annuity (amount)4])*([Commission Percentage]*[income retained %]*[commission split]) AS Income, IIf(IsNull([tContacts_1.CNickName]),[tContacts_1.CFirstName],[tContacts_1.CNickname]) AS [FirstName or Nick1], IIf(IsNull([tContacts_2.CNickName]),[tContacts_2.CFirstName],[tContacts_2.CNickname]) AS [FirstName or Nick2], IIf(IsNull([tContacts.CNickName]),[tContacts.CFirstName],[tContacts.CNickname]) AS [FirstName or Nick], tNewCases.ProfAdminID, ProfAdminT.ProfAdminName, ProfAdminT.ProfAdminAddress, ProfAdminT.ProfAdminTIN, tNewCases.[Negotiations Diary], tNewCases.Referredbyid, tContacts_3.CFirstName, tContacts_3.CLastName, tContacts_3.CClient, tContacts_3.[Current or Previous Position], tNewCases.AssignorID, [ASSIGNOR-UNDERWRITING COMPANY LIST].[Underwriting Company], [ASSIGNOR-UNDERWRITING COMPANY LIST].[Parent Carrier], LifeCoT.LifeCoName, LifeCoT.[LifeCo FEIN], tNewCases.[Set-up Date], tNewCases.[Claim Number], tNewCases.[Payment Instructions Sent?], tNewCases.Insured, tNewCases.TTD, tNewCases.PPD, tNewCases.PTD, tNewCases.[Authority Includes], tNewCases.[Total File Authority], tNewCases.Demand, tNewCases.Offer, tNewCases.Claimantfirst, tNewCases.Claimantlast, tNewCases.claimantAddress, tNewCases.claimantCity, tNewCases.claimantState, tNewCases.claimantZip, tNewCases.[Date of Birth], tNewCases.[Date of Accident], tNewCases.Venue, tNewCases.[Life Expectancy], tNewCases.[Arbitrator/Judge], tNewCases.[Case Number], tNewCases.[Current Offer], tNewCases.[Current Demand], tNewCases.Budget, tNewCases.[Opening Position], tNewCases.[Annuity (Amount)], tNewCases.Comments, tNewCases.[Marital Status], tNewCases.Gender, tNewCases.[Social Security Number], tNewCases.[Life Company], tNewCases.[Purchase Date], tNewCases.[Settlement Terms], tNewCases.Premium, tNewCases.[Defendant Carrier], tNewCases.Upfront, tNewCases.[Type of Case], tNewCases.[Case Notes], tNewCases.Notes, tNewCases.[Case Status], tNewCases.[To Do], tNewCases.[Date Closed], tNewCases.Broker, tNewCases.PlaintiffAttyId, tNewCases.DefenseAttyId, tNewCases.ClientId, tNewCases.[Claimant Phone], tNewCases.[Claimant Email], tNewCases.[MSA Exhaustion Amount], tNewCases.[Seed Money], tNewCases.[Annual Payment], tNewCases.[Funding Request Sent?], tNewCases.[Settlement Documents?], tNewCases.Assignee, tNewCases.DefendantCarrierId, tNewCases.[Commission Percentage], tNewCases.[Commission Split], tNewCases.[PV Discount Rate], tNewCases.[MSA Terms], tNewCases.[Commission Received], tNewCases.[Custodial Account Terms], tNewCases.JudgeId, tNewCases.[Date Premium/App Sent], tNewCases.CoBrokerId, tNewCases.CoBroker, tNewCases.[Assignment Agreement], tNewCases.[CMS Approval], tNewCases.[WC Settlement Approval], tNewCases.[Settlement Agreement], tNewCases.Application, tNewCases.[Proof of Age], tNewCases.UQA, tNewCases.UQAR, tNewCases.[Reinsurance 2-Party Agreement], tNewCases.[Reinsurance 3-Party Agreement], tNewCases.[Annuity Contract Number], tNewCases.[Premium Check Number], tNewCases.[Attorney Fees Terms], tNewCases.[Hold Harmless (Attorney Fees)], tNewCases.[W-9/W-4], tNewCases.[Type of Disability], tNewCases.[Case Phase], tNewCases.[Settled in CRS?], tNewCases.[Income Retained %], tNewCases.[Non-MSA Total], tNewCases.[Documents Diary], tNewCases.[Rated Ages Date], tNewCases.[Rated Age Status], tNewCases.[Referred By], tNewCases.[Claim Number2], tNewCases.[Date of Accident2], tNewCases.mi, tNewCases.[Claimant Phone-Cell], tNewCases.DecedentFirst, tNewCases.DecedentLast, tNewCases.[PlaintiffFirm#], tNewCases.[DefenseFirm#], tContacts_2.CFirstName, tContacts_2.CNickname, tContacts_2.CLastName, tContacts_2.CClient, tContacts_2.CAddress, tContacts_2.CCity, tContacts_2.CState, tContacts_2.CPostalCode, tContacts_2.CWorkPhone, tContacts_2.CWorkPhone, tContacts_2.CEmailAddress, tContacts.CFirstName, tContacts.CLastName, tContacts.CClient, tContacts.CAddress, tContacts.CCity, tContacts.CState, tContacts.CPostalCode, tContacts.CWorkPhone, tContacts.CMobilePhone, tContacts.CFaxNumber, tContacts.CEmailAddress, tContacts.[Structured Settlement Policy], tContacts_1.CFirstName, tContacts_1.CLastName, tContacts_1.CClient, tContacts_1.CClient, tContacts_1.CAddress, tContacts_1.CCity, tContacts_1.CState, tContacts_1.CPostalCode, tContacts_1.CWorkPhone, tContacts_1.CMobilePhone, tContacts_1.CFaxNumber, tContacts_1.CEmailAddress, tContacts_4.[Structured Settlement Policy], tNewCases.[Guardianship for Minor], tNewCases.[Transaction Date1], tNewCases.[Transaction Date2], tNewCases.[Transaction Date3], tNewCases.[Transaction Date4], tNewCases.Premium1, tNewCases.Premium2, tNewCases.Premium3, tNewCases.Premium4, tNewCases.[Premium Check Number1], tNewCases.[Premium Check Number2], tNewCases.[Premium Check Number3], tNewCases.[Premium Check Number4], tNewCases.[Premium Transaction1], tNewCases.[Premium Transaction2], tNewCases.[Premium Transaction3], tNewCases.[Premium Transaction4], tNewCases.[Settled in CRS?2], tNewCases.[Settled in CRS?3], tNewCases.[Settled in CRS?4], tNewCases.[Riders Date], tNewCases.[Proof Age Date], tNewCases.[UQA Date], tNewCases.[UQR Date], tNewCases.[Reinsurance2 Date], tNewCases.Reinsurance3Date, tNewCases.[Annuity (Amount)2], tNewCases.[Annuity (Amount)3], ([Annuity (Amount)]+[Annuity (Amount)2]) AS [total indemnity annuity], ([Annuity (Amount)]+[Annuity (Amount)2]+[Annuity (Amount)3]+[annuity (amount)4]) AS [total ind-msa annuity], [tcontacts_5.cfirstName] & " " & [tcontacts_5.cLastName] AS CoBrokerFulll, tContacts_5.CFirstName, tContacts_5.CLastName, tContacts_5.CClient, [annual payment]*([life expectancy]-1) AS [Zurich Value], tNewCases.[PV Discount Rate5%], tNewCases.[PV Discount Rate6%], tNewCases.[Payment Status], tNewCases.[Payment Status Date], tContacts.[CWeb Site], tContacts_1.[CWeb Site], tContacts_2.[CWeb Site], tNewCases.[First Payment Date], tContacts.Photo, tContacts_1.Photo, tContacts_2.Photo, tContacts.[Assistant/Paralegal], tContacts.[Assistant/Paralegal Email], tNewCases.[Quote Follow-up], tNewCases.[Medical Authority], tNewCases.[Policy Number], tContacts_2.Project, tContacts_2.[Client Preferences], tNewCases.[Project Name], tNewCases.[Professional Administration?], tNewCases.[Professional Admin Cost], tNewCases.[Sedgwick Review Project], tNewCases.[Commutation Rider], tNewCases.[Locked-in Status], tContacts_1.CFirstName, tContacts_1.CLastName, tNewCases.AccidentLocation, tNewCases.ParentGuardianNextFriend, tNewCases.CRSnumber, tNewCases.LifeCoID, tNewCases.LifeCoAssigmentCoID, LifeCoAssigmentCo.LifeCoAssigmentCoName, LifeCoAssigmentCo.LifeCoAssigmentCoTIN, LifeCoAssigmentCo.LifeCoAssigmentCoAddress, LifeCoT.LifeCoAssignmentFee, tNewCases.SSFilePath, [ssFilePath] & "" & [claimantlast] & ", " & [claimantfirst] AS SSClaimantPath, tNewCases.FutureMedicalSeed, tNewCases.FutureMedicalTerms, tNewCases.[Annuity (Amount)4], tNewCases.CRS, tNewCases.CRS2, tNewCases.CRS3, tNewCases.CRS4, DefenseBroker2.CFirstName, DefenseBroker2.CLastName, PlaintiffBroker.CFirstName, PlaintiffBroker.CLastName, tNewCases.CoBrokerId2, tNewCases.TTBrokerId, tNewCases.[Commission SplitPlaintiff], tNewCases.[Commission SplitDefense2], tNewCases.TrustName, tNewCases.ClaimantLegalStatus, tContacts.CNickname, tContacts_3.CNickname

FROM (((((((((((tNewCases LEFT JOIN tContacts ON tNewCases.PlaintiffAttyId = tContacts.[Client ID]) LEFT JOIN tContacts AS tContacts_1 ON tNewCases.DefenseAttyId = tContacts_1.[Client ID]) LEFT JOIN tContacts AS tContacts_2 ON tNewCases.ClientId = tContacts_2.[Client ID]) LEFT JOIN tContacts AS tContacts_3 ON tNewCases.Referredbyid = tContacts_3.[Client ID]) LEFT JOIN tContacts AS tContacts_4 ON tNewCases.JudgeId = tContacts_4.[Client ID]) LEFT JOIN tContacts AS tContacts_5 ON tNewCases.CoBrokerId = tContacts_5.[Client ID]) LEFT JOIN [ASSIGNOR-UNDERWRITING COMPANY LIST] ON tNewCases.AssignorID = [ASSIGNOR-UNDERWRITING COMPANY LIST].assignorID) LEFT JOIN LifeCoT ON tNewCases.LifeCoID = LifeCoT.LifeCoID) LEFT JOIN ProfAdminT ON tNewCases.ProfAdminID = ProfAdminT.ProfAdminID) LEFT JOIN LifeCoAssigmentCo ON tNewCases.LifeCoAssigmentCoID = LifeCoAssigmentCo.LifeCoAssigmentCoID) LEFT JOIN tContacts AS DefenseBroker2 ON tNewCases.CoBrokerId2 = DefenseBroker2.[Client ID]) LEFT JOIN tContacts AS PlaintiffBroker ON tNewCases.TTBrokerId = PlaintiffBroker.[Client ID]

WHERE (((tNewCases.Claimantfirst) Like [FIRST NAME?] & "*") AND ((tNewCases.Claimantlast) Like [LAST NAME?] & "*"));

Scott Axton @Reply

3 years ago

Way to complicated for me to look at right now.  I have no idea what you are trying to accomplish.

That being said there are several things that are setting off big alarm bells for me.

1.) Spaces in you field names.
2.) Use of the '?' mark in a field name.  That is a special wild card character.
3.) Use of the '%' in a field name.
4.) The AS [FirstName or Nick2] - is that a field name or is it an or condition?

Do yourself a favor on go back now and clean those up first.  Watch the  Access Beginner 1 again if you need to.  In that Richard teaches NO SPACES and only occasional numbers in a field name.  There is a reason for that.
Definitely not the special characters.

	Richard Rost @Reply 3 years ago
That would take me an hour just to read. Lol

Kevin Yip @Reply

3 years ago

Hi John, check the name of the textbox with the #Type! error. If its control source is an *expression* (one that has an equal sign), the expression cannot have a field with the same name as the textbox's. If it does, then the textbox is referencing itself, and that will cause a #Type! error. See the picture below. If the control source is a bound field, it can have the same name as the textbox. But if it's an expression, it can't.

	Kevin Yip @Reply 3 years ago

	John Muir OP @Reply 3 years ago
Hi Scott, I know i have spaces in field names. Created this db 15 years ago. Have more recently followed Richard's rules. Would love to correct all of my errors but it would take weeks/months to fix/rebuild... There are no ?s in the problematic fields. But there are in the query/form for parameter querries. Thanks

John Muir OP @Reply

3 years ago

Thanks Kevin,

the expression is: =([firstname or nick] & " " & [tcontacts.clastname]). The textbox name is: Text1797

The other field has the same expression and is called: Text445.

I don't know if the problem is my form or the query or the canenated strings on my form.

For what it's worth, The field in my query is: FirstName or Nick: IIf(IsNull([tContacts.CNickName]),[tContacts.CFirstName],[tContacts.CNickname])

iIs any of this helpful?

Thanks,

John

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View