Using a field value as criteria

[rockman]

I couldn't find an answer to this in the archives, but it seems fairly straight-foward to me:

How can I use a field value as criteria?

Example: Let's say I want a select query to return all records where the person's first name is the same as the person's last name. In a querydef, the following won't work:

Field: fldFirstName
Criteria: [fldLastName]

What happens is that MSAccess then performs a parameter prompt asking me to provide a value for "fldLastName".

Any way to do this?

Thanks in advance for any insight,
Jeff

 

[dcx693]

Let's say I want a select query to return all records where the person's first name is the same as the person's last name.

Is this an actual thing you want to do, or some wild example you threw out? (If you really want to know how to do it, it is possible by using a self-join query.)

What is your exact circumstance? Are you looking to find matches for entries in field a form?

 

[rockman]

This is just a succinct example.

My actual SQL string is: SELECT selPatients.fldPatientID AS [Patient ID], selPatients.fldFullName AS [Patient Name], tblClinics.fldName AS Clinic, selPatients.fldTypeOrReason AS [Follow-up Type], selPatients.fldFollowupDate AS [Follow-up Date], First([fldPhoneType] & " " & [fldPhoneNumber]) AS Phone, Last(tblAppointments.fldDate) AS [Last Appointment]
FROM ((tblClinics RIGHT JOIN selPatients ON tblClinics.fldClinicID = selPatients.fldClinicID) LEFT JOIN tblPatientsPhones ON selPatients.fldPatientID = tblPatientsPhones.fldPatientID) LEFT JOIN tblAppointments ON selPatients.fldPatientID = tblAppointments.fldPatientID
GROUP BY selPatients.fldPatientID, selPatients.fldFullName, tblClinics.fldName, selPatients.fldTypeOrReason, selPatients.fldFollowupDate, selPatients.fldFollowup, [Forms]![frmFollowUps]![chkUnspecifiedFollowUp].[value]
HAVING (((selPatients.fldFollowupDate)<DateAdd("m",1,Date())) AND ((selPatients.fldFollowup)=True)) OR (((selPatients.fldFollowupDate) Is Null) AND ((selPatients.fldFollowup)=True) AND (([Forms]![frmFollowUps]![chkUnspecifiedFollowUp].[value])=True))
ORDER BY selPatients.fldFollowupDate;

In my actual code I will be comparing the follow-up date with the last scheduled appointment date.

But if you can just give me advise concerning my hypothetical example above I'm sure I can fashion it to my specific problem. In the mean time I will look at self-join queries.

Thanks,
Jeff

 

[dgm]

I did a simple test in a query and this worked ok for me:
SELECT people.pFirstName
FROM people
WHERE (((people.pFirstName)=[people].[pLastName]));

That's pretty much what your example is isn't it?

I'm using Access 2000.

 

[rockman]

Thanks for your replies dcx693 and dgm.

I got the self-join query to work before I saw dgm's post.

Dgm, I thought that I had tried your way but obviously I had something screwed up because your code works flawlessly and is much more elegant than a self-join query.

Thanks again,
Jeff