DLookup Help

[ScrmingWhisprs]

I am working on a volunteer tracking system for a haunted house I work at.

I have a table of Shifts, with fields ShiftID, Shift Name, Date, and Hours. On my sign in form, I have a subform that records who works, what spot they are in, and I want to have the number of hours that is linked to that date transfer to a textbox on this subform.

Need some help with the DLookup function.

Thanks!

ScrmingWhisprs

 

[allan57]

Note that the same logic applies to most Domain Aggregate Functions (DMax, DMin etc.)

Normal usage

For numerical values:
DLookup("FieldName" , "TableName" , "Criteria = n")

For strings: (note the apostrophe before and after the value)
DLookup("FieldName" , "TableName" , "Criteria= 'string'")

For dates:
DLookup("FieldName" , "TableName" , "Criteria= #date#")


Refering to a form control

For numerical values:
DLookup("FieldName", "TableName", "Criteria = " & forms!FormName!ControlName)

For strings: (note the apostrophe before and after the value)
DLookup("FieldName", "TableName", "Criteria = '" & forms!FormName!ControlName & "'")

For dates:
DLookup("FieldName", "TableName", "Criteria = #" & forms!FormName!ControlName & "#")


Mix-n-Match

DLookup("FieldName", "TableName", "Criteria1 = " & Forms!FormName!Control1 _
& " AND Criteria2 = '" & Forms!FormName!Control2 & "'" _
& " AND Criteria3 =#" & Forms!FormName!Control3 & "#")

 

[ScrmingWhisprs]

I used the following DLookup function as the control source for the Hours field on the Shifts subform.

=DLookUp("Hours","Shifts","Date = " & Forms!Shifts!Date)

Nothing happens though. How does that data transfer over?

Where I have "Date = " as criteria, is that refering to the subform date?

Note that the same logic applies to most Domain Aggregate Functions (DMax, DMin etc.)

Maybe I just don't have the logic.


ScrmingWhisprs

 

[RV]

Syntaxis for control sources is a bit different:

=DLookUp("Hours","Shifts","Date = Date")

To distinguish between object names, put txt in front of your form field.
Don't use Date as object name, it's a reserved word and will cause mayhem

RV