Form showing #Name? when search for a record

[perryc]

would this help?

SELECT Referrals.ID, Referrals.ReferralDate, Referrals.[A?P?R?], Referrals.Patient, Referrals.RefSource, Referrals.Zip, Referrals.Dx, Referrals.Payor, Referrals.[ACO?], Referrals.[SN?], Referrals.[PT?], Referrals.[OT?], Referrals.[ST?], Referrals.OtherServices, Referrals.ExpHHSOC, Referrals.ExpPTEval, Referrals.Comments, Referrals.[BSHSIDoc?], Referrals.[HighRefAcct?], Referrals.[OtherFactors?], Referrals.ID, Referrals.EnteredBy, Referrals.CaseComplete, Referrals.[RequestApproval?], [Zip&Team].City, [Zip&Team].Team, Referrals.T_Orange_Used, Referrals.T_Green_Used, Referrals.T_Blue_Used, Referrals.T_Red_Used, Referrals.T_Pink_Used, Referrals.[ToRequester:], Referrals.DOB, Referrals.M0102, Referrals.Attending, Referrals.InsSpecific, Referrals.DeclineReason, Referrals.Alert, Referrals.RcdUpdated, Referrals.ApprovalDT, Referrals.Approver, Referrals.IniEntryDT, Referrals.NN
FROM Referrals INNER JOIN [Zip&Team] ON Referrals.Zip = [Zip&Team].Zip
WHERE (((Referrals.[A?P?R?])<>"Cancelled" Or (Referrals.[A?P?R?]) Is Null) AND ((Referrals.CaseComplete)=False) AND ((Referrals.[RequestApproval?])=True))
ORDER BY Referrals.ID, Referrals.ID;

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

Advise not to use punctuation/special characters (underscore only exception) in naming convention.

Your code is changing the RecordSource to an SQL statement that has only one table, not a query that joins tables to retrieve related info.

Why set RecordSource? Why not just apply filter?

DoCmd.OpenForm "Form1", , , PCriteria

[perryc]

Ajax, First, I am so thankful that you took all this time to help me. I am so sorry that I am not a programmer, therefore, my explanation is not the best. I can not upload the DB because I broke it into front and backend and it is too complicated to upload. Let me just explain once more my problem and I know what you are saying about why not just filter things.

Form1 (recordsource is join between 2 tables with a common key "Zip"; Table1 is a patient referral info w/ a field called Zip and Table2 has 3 fields only: Zip, City and Team)
When Form1 is displayed, the form calls for fields in Table1 plus City and Team from Table2 where Zip = Zip)
Normally, display these info are fine. I can navigate from record to records in Form1 and Zip, City, and Team all displayed normally.
The problem comes when I do a record search, by the following command from a search form:
DoCmd.OpenForm "Form1"
Form_Form1.RecordSource = "select * from Referrals where " & PCriteria
All the data displays just fine except City and Team from Table2 will become #Name?

Well, I think I hear what you are saying, perhaps, is by doing the codes above (Form_Form1.RecordSource...) I may have messed up the record. Instead, do:
DoCmd.OpenForm "Form1", , , PCriteria to open the form directly?

I will give it a try and report back. Thank you so much again.

[perryc]

If you want to provide db for analysis, follow instructions at bottom of my post.

Advise not to use punctuation/special characters (underscore only exception) in naming convention.

Your code is changing the RecordSource to an SQL statement that has only one table, not a query that joins tables to retrieve related info.

Why set RecordSource? Why not just apply filter?

DoCmd.OpenForm "Form1", , , PCriteria

Ajax, You are a genius! Works great!! Not sure why it did not work if I search through the record source. Thanks a lot!!