Need help with textbox calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello everyone,

I need your expert assistance in solving a problem I can't solve myself.

I'm in the process of building a db to replace multiple spreadsheets. One
of the tables will hold the weekly performance results of different
locations. Here's a portion the table structure:

tblPerformance
PerformanceID PK
FranchiseID FK
TheWeekID FK
LeadEnding

TheWeekID is a numeric field and on the form I have a textbox that displays
that number as a date (e.g. 38284 displays as 10/24/2004). The numbers are
in increments of 7 so the displayed date will always be a Sunday.

LeadEnding replaces one of two columns in the spreadsheet. The spreadsheet
has 'Leads From' and 'Leads To'. I designed the table with one field
figuring that 'Leads From' can be calculated. It is always the number from
the previous week's 'Leads To' plus 1.

My problem is writing the formula for the textbox that will display the
'Leads From' number. Basically, I want to lookup the LeadEnding entry in
tblPerformace where the FranchiseID matches the form's current record and the
TheWeekID is 7 less than the form's current record. This is what I have so
far.

=DLookUp(tblPerformance!LeadEnding,[tblPerformance],"[tblPerformance]![FranchiseID]="
& Forms!sfrmPerformance!txtFranchiseID And "[tblPerformance]![TheWeekID]=" &
Forms!sfrmPerformance!txtTheWeekID-7)+1

All help is appreciated.
 
Update: I've fixed some of the quotation mark placements so now I at least
get a number. Unfortunately, it's the same number even though the record
changes. Here's what I have now:

=DLookUp("[tblPerformance]![LeadEnding]","tblPerformance","[FranchiseID] = &
Forms!sfrmPerformance!txtFranchiseID" And "[TheWeekID] = &
Forms!sfrmPerformance!txtTheWeekID-7")+1
 
Update: I've fixed some of the quotation mark placements so now I at least
get a number. Unfortunately, it's the same number even though the record
changes.
Click to expand...

Pull the form referernces out of the quotes, so it searches for the
current value in the form:

=DLookUp("[LeadEnding]", "tblPerformance", "[FranchiseID] = " &
Forms!sfrmPerformance!txtFranchiseID & " And [TheWeekID] = " &
Forms!sfrmPerformance!txtTheWeekID-7)+1

If sfrmPerformance is a subform on a mainform (I'll call it frmMain)
then you also need to use a more convoluted form reference; you need
the name *of the Subform control* (which may or may not be the same as
the name of the form therein), e.g.

Forms!frmMain!sfrmPerforance.Form!txtFranchiseID


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thank you John,

You are correct that this is on a subform. I see also that I did not have
the AND word within quotation marks or an ampersand to link the two strings
together. Here is the formula that works:

=DLookUp("[LeadEnding]","[tblPerformance]","[FranchiseID]=" &
Forms!frmFranchise!sfrmPerformance.Form.txtFranchiseID & "And TheWeekID =" &
Forms!frmFranchise!sfrmPerformance.Form.txtTheWeekID-7)+1

Thanks again for your help!

John Vinson said:
Update: I've fixed some of the quotation mark placements so now I at least
get a number. Unfortunately, it's the same number even though the record
changes.
Click to expand...

Pull the form referernces out of the quotes, so it searches for the
current value in the form:

=DLookUp("[LeadEnding]", "tblPerformance", "[FranchiseID] = " &
Forms!sfrmPerformance!txtFranchiseID & " And [TheWeekID] = " &
Forms!sfrmPerformance!txtTheWeekID-7)+1

If sfrmPerformance is a subform on a mainform (I'll call it frmMain)
then you also need to use a more convoluted form reference; you need
the name *of the Subform control* (which may or may not be the same as
the name of the form therein), e.g.

Forms!frmMain!sfrmPerforance.Form!txtFranchiseID


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Back
Top