AutoID

[hithere]

I have a query in which I want to limit the results from a facilitytable. I want to show the results limited to the facility name the user puts in. In the facility column of the filed I have "Like [Enter Facility:]". Currently the only to get this query to work is to actually enter the AutoID associated with each facility and not the facility name itself. Is there anyway access can accept the name so users don't have to try to remember the ID assoicated with each facility?

[Robeen]

Can you post the SQL of your query here please?

[hithere]

See query below:

SELECT qry_Info_Pt.MRN, qry_Info_Pt.LastName, qry_Info_Pt.FirstName, tbl_Referral.DischargeHosp, tbl_Referral.AdmitFacility, tbl_Referral.Date, tbl_Referral.Insurance, tbl_Referral.Comments, qry_Info_Pt.Name, tbl_Referral.ReferralID
FROM tbl_Info_Fcy INNER JOIN (qry_Info_Pt INNER JOIN tbl_Referral ON qry_Info_Pt.MRN = tbl_Referral.MRN) ON tbl_Info_Fcy.ID = tbl_Referral.DischargeHosp;

[Robeen]

The SQL you posted does not have your parameter criteria. I wanted to see how you had the parameter criteria set up.
I just ran two quick queries - one on a table that has an AutoId field - and one on another table that has a Number field that is the Primary Key.
Both worked just fine.
Here's the SQL from one of the queries.

SELECT AutoID.ID, AutoID.FirstName
FROM AutoID
WHERE (((AutoID.FirstName) Like [Enter First Name:]));

Not sure if I've totally understood your problem.
Let me know if I'm off-track here.

[hithere]

Sorry. I accidentally posted the general query. Below is actually the criteria. For the admit facility criteria I would like to enter the actual name of the facility but access only recognizes the auto id associated with the facility name.

SELECT qry_Referral.Date, qry_Referral.DischargeHosp, qry_Referral.AdmitFacility, qry_Referral.Name, qry_Referral.MRN, qry_Referral.Insurance
FROM qry_Referral
WHERE (((qry_Referral.Date)>[Enter Beginning Referral Date:] And (qry_Referral.Date)<=[Enter Ending Referral Date:]) AND ((qry_Referral.AdmitFacility) Like [Enter Facility:]))
ORDER BY qry_Referral.Date;

[Robeen]

What is the SQL for 'qry_Referral'? Is it another Query? Or is qry_Referral querying a Table?
Do you have 'Lookup' fields in your table? I've run into issues similar to what you describe when trying to help others on this Forum.
Personally, I don't use Lookup fields in my tables.

[hithere]

Qry_Referral is another query and the SQL is:

SELECT qry_Info_Pt.MRN, qry_Info_Pt.LastName, qry_Info_Pt.FirstName, tbl_Referral.DischargeHosp, tbl_Referral.AdmitFacility, tbl_Referral.Date, tbl_Referral.Insurance, tbl_Referral.Comments, qry_Info_Pt.Name, tbl_Referral.ReferralID
FROM tbl_Info_Fcy INNER JOIN (qry_Info_Pt INNER JOIN tbl_Referral ON qry_Info_Pt.MRN = tbl_Referral.MRN) ON tbl_Info_Fcy.ID = tbl_Referral.DischargeHosp

I'm not sure how to go about not putting lookups in my tables. Each referral has to be made from a definite list of patients (from a patient table) to a definite list of faclities (from a facility table).

[Robeen]

So Qry_Referral is using another Query - 'qry_Info_Pt' as its source?
What is the SQL of qry_Info_Pt?

I'm just trying to get to the bottom of this. I have reports that are based on a query that is the result of a LOT of other queries.
Once I see the entire chain [all the way back to a Table - or more than one Table] that is getting you your data - I MIGHT be able to figure out why you're having the problem you are having.

Is there any chance you can post your Database?
You can take out data and other stuff that does not relate to your problem and just leave enough data for me to see your problem in action.

[hithere]

Sure. This query is a actually based off of another query and tables. I made a copy of the database and removed the lookups from the tables and just put them in the forms. This resulted in the AutoID showing in tables and queries wherever a lookup was initiated.

Attached is the database with the lookups in the tables.