Reference and evaluate a control name in a variable

S

Steve Latino

strFilter = "qryweekcurrentinfo." & CStr(dayname) & ".Value"

DoCmd.RunSQL "INSERT INTO tblReconcile ( RouteNum, CustNum, Name, Contact,
Phone, PONumber, Taxable, Reddy, Charge, Notes, Late, Delivered,
DeclineReason ) " & _
"SELECT Customer.RouteNum, tblWeekCurrent.CustNum, Customer.Name,
Customer.Contact, Customer.Phone, Customer.PONumber, Customer.Taxable,
Customer.Reddy, Customer.Charge, Customer.Notes, tblWeekCurrent.Late,
tblWeekCurrent.Delivered, tblWeekCurrent.DeclineReason " & _
"FROM Employee RIGHT JOIN (Customer RIGHT JOIN tblWeekCurrent ON
Customer.CustNum = tblWeekCurrent.CustNum) ON Employee.EmpNum =
Customer.RouteNum " & _
"WHERE (((val(strFilter) = True ))) " & _
"ORDER BY Customer.RouteNum"

I started this program many years ago in Access 95 and have been limping it
along ever since. I'm trying to condense a huge amount of objects (arrays,
macros, queries, and such) and in my early implementation of the main driving
table I have 7 boolean fields representing each day of the week. What I'm
trying to do now is filter base on the value of the fieldname corresponding
to today. In other words, select records if tblTable!variable evaluating to
dow today.value = true. So if the module is run on Monday, Monday yeses get
picked, on Tuesday - Tuesday = True, etc.

I'd bet there's an easy proc call or something to get a useable version of
the actual fieldname to show up in a WHERE clause, but I've been searching
pretty hard for a couple of weeks and have tried just about every
concantenation and cstr,cbool, val, etc. with no success. Suggestions
greatly appreciated.
 
M

Marshall Barton

Steve Latino said:
strFilter = "qryweekcurrentinfo." & CStr(dayname) & ".Value"

DoCmd.RunSQL "INSERT INTO tblReconcile ( RouteNum, CustNum, Name, Contact,
Phone, PONumber, Taxable, Reddy, Charge, Notes, Late, Delivered,
DeclineReason ) " & _
"SELECT Customer.RouteNum, tblWeekCurrent.CustNum, Customer.Name,
Customer.Contact, Customer.Phone, Customer.PONumber, Customer.Taxable,
Customer.Reddy, Customer.Charge, Customer.Notes, tblWeekCurrent.Late,
tblWeekCurrent.Delivered, tblWeekCurrent.DeclineReason " & _
"FROM Employee RIGHT JOIN (Customer RIGHT JOIN tblWeekCurrent ON
Customer.CustNum = tblWeekCurrent.CustNum) ON Employee.EmpNum =
Customer.RouteNum " & _
"WHERE (((val(strFilter) = True ))) " & _
"ORDER BY Customer.RouteNum"

I started this program many years ago in Access 95 and have been limping it
along ever since. I'm trying to condense a huge amount of objects (arrays,
macros, queries, and such) and in my early implementation of the main driving
table I have 7 boolean fields representing each day of the week. What I'm
trying to do now is filter base on the value of the fieldname corresponding
to today. In other words, select records if tblTable!variable evaluating to
dow today.value = true. So if the module is run on Monday, Monday yeses get
picked, on Tuesday - Tuesday = True, etc.

I'd bet there's an easy proc call or something to get a useable version of
the actual fieldname to show up in a WHERE clause, but I've been searching
pretty hard for a couple of weeks and have tried just about every
concantenation and cstr,cbool, val, etc. with no success. Suggestions
greatly appreciated.
Click to expand...


Ain't un-normalized tables fun ;-)

Try this:

"WHERE " & strFilter & " = True " & _
 
S

Steve Latino

This was the answer for which I've been questing!!! Many thanks from the
person who knew nothing of normalization so many years ago and barely has the
faintest concept of it today. I am thrilled to have found this site and
extend GRATEFUL appreciation to you Marshall.
 
Top