[AccessD] Query Using field names

But it still doesn't solve the problem of how to identify the fields :-(

I'd probably use a function for this.

Function PainLocsPicked(PatientID As Long) As Long
Dim rs As DAO.Recordset
Dim fld As Field
Dim Locs As Long
Set rs = CurrentDb.OpenRecordset("select * from tblPatients where PatientID = " & 
PatientID)
For Each fld In rs.Fields
   If Left(fld.Name, 5) = "fldPL" Then
           Locs = Locs - fld.Value
   End If
Next
rs.Close
Set rs = Nothing
PainLocsPicked = Locs
End Function


On 13 Apr 2009 at 12:26, Rocky Smolin wrote:

> That's probably a better approach.  Thanks.
> 
> Rocky
>  
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
> [IT]
> Sent: Monday, April 13, 2009 11:59 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query Using field names
> 
> Hi, Rocky
> 
> Not sure that Dcount is what you want.  That returns the count of a specific
> field over several records.  Why not sum all the values for the fldPL*
> fields for the target record (True = -1) and if the result is a non-zero
> value, you know that at least one of them was answered true.
> 
> Hope this helps.
> 
> Don
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
> Sent: Monday, April 13, 2009 11:36 AM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Query Using field names
> 
> Dear List:
> 
> I want to write a DCount or do a stored query (amounts to the same thing I
> guess) which incorporates the field name as a criterion.
> 
> This is an app which diagnoses back problems.  In the patient record there
> are a number of fields which identify pain locations and all of those field
> names start with "fldPL".  So I want to look at all the fields whose name
> starts with fldPL for a specific Patient ID (I have the PK), and count the
> ones where the field value is True (this is to make sure the user has
> identified at least one pain location).
> 
> But I can't figure out how to use the field name as a criterion.  Can this
> be done?
> 
> 
> 
> MTIA
> 
> 
> 
> Rocky Smolin
> 
> Beach Access Software
> 
> 858-259-4334
> 
> www.e-z-mrp.com <http://www.e-z-mrp.com/>
> 
> www.bchacc.com <http://www.bchacc.com/>
> 
> 
> 
> 
> 
> 
> This e-mail may contain Sprint Nextel Company proprietary information
> intended for the sole use of the recipient(s). Any use by others is
> prohibited. If you are not the intended recipient, please contact the sender
> and delete all copies of the message.
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com