[AccessD] Query Using field names

Right now I'm doing something similar in the form where the pain locations
are identified by cycling through all the controls and bailing out when I
find the first pain location selected (all they need is one to pass the
test).

But I need to check this in another form, so I thought to make a public sub
to do it.  I'm a big DAO fan so that's probably the way I'll go - a few
minutes of coding versus a lot more trying to figure out a query.

Rocky
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Monday, April 13, 2009 2:48 PM
To: Access Developers discussion and problem solving
Subject: Re: [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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com