How to use Dlookup function

[evlai]

I have a table named WeekLookUp, and the data is shown as below:

Date_List FiscalWk
31-Jan-05 WK1Q1FY06
1-Feb-05 WK1Q1FY06
2-Feb-05 WK1Q1FY06
3-Feb-05 WK1Q1FY06
4-Feb-05 WK1Q1FY06
5-Feb-05 WK1Q1FY06
6-Feb-05 WK1Q1FY06
7-Feb-05 WK2Q1FY06
8-Feb-05 WK2Q1FY06
9-Feb-05 WK2Q1FY06
10-Feb-05 WK2Q1FY06
11-Feb-05 WK2Q1FY06
12-Feb-05 WK2Q1FY06
13-Feb-05 WK2Q1FY06
14-Feb-05 WK3Q1FY06

i would like to know how to use Dloopup to find the matching data using
the above table.

For example, if i put in the current data in the textbox, the Fiscal
week textbox will show WK08FY06.
Do i need to create any relationship between both table?

 

[Mike Painter]

I have a table named WeekLookUp, and the data is shown as below:

Date_List FiscalWk
31-Jan-05 WK1Q1FY06
1-Feb-05 WK1Q1FY06
2-Feb-05 WK1Q1FY06
3-Feb-05 WK1Q1FY06
4-Feb-05 WK1Q1FY06
5-Feb-05 WK1Q1FY06
6-Feb-05 WK1Q1FY06
7-Feb-05 WK2Q1FY06
8-Feb-05 WK2Q1FY06
9-Feb-05 WK2Q1FY06
10-Feb-05 WK2Q1FY06
11-Feb-05 WK2Q1FY06
12-Feb-05 WK2Q1FY06
13-Feb-05 WK2Q1FY06
14-Feb-05 WK3Q1FY06

i would like to know how to use Dloopup to find the matching data
using the above table.

For example, if i put in the current data in the textbox, the Fiscal
week textbox will show WK08FY06.
Do i need to create any relationship between both table?

I'm not sure what you are doing but I'm guessing that everything after
31-jan-05 should be 06 and that "textbox will show WK08FY06." sould be
"textbox will show WK08Q3FY06."

If so and if "put in the current data in" should be " put in the current
date in " then this can be done with out the use of a table. Access can
format the date to show week number, quarter and year. A calculated field or
a function would work quite well.

 

[Todd Shillam]

I have a table named WeekLookUp, and the data is shown as below:

Date_List FiscalWk
31-Jan-05 WK1Q1FY06
1-Feb-05 WK1Q1FY06
2-Feb-05 WK1Q1FY06
3-Feb-05 WK1Q1FY06
4-Feb-05 WK1Q1FY06
5-Feb-05 WK1Q1FY06
6-Feb-05 WK1Q1FY06
7-Feb-05 WK2Q1FY06
8-Feb-05 WK2Q1FY06
9-Feb-05 WK2Q1FY06
10-Feb-05 WK2Q1FY06
11-Feb-05 WK2Q1FY06
12-Feb-05 WK2Q1FY06
13-Feb-05 WK2Q1FY06
14-Feb-05 WK3Q1FY06

i would like to know how to use Dloopup to find the matching data using
the above table.

For example, if i put in the current data in the textbox, the Fiscal
week textbox will show WK08FY06.
Do i need to create any relationship between both table?

Note: The DLookup() function finds the first matching value in a table or
query--it doesn't find 'all' matching values. You would need to build a
query for that.

Nonetheless, here's a VBA code illustration using the DLookup() function:

'DECLARING VARIABLES
Dim strFiscalWeek As String
Dim strWhere

'INITIALIZE WHERE CLAUSE FOR DLOOKUP FUNCTION
strWhere = "[Date_List]= #14-Feb-05# " '<--You Can Use a Variable Here

'INITIALIZE VARIABLE USING DLOOKUP FUNCTION
strFiscalWeek = DLookup("FiscalWk","WeekLookUp", strWhere)

In the above illustration, the where clause (strWhere) finds the 'FiscalWk'
value for '14-Feb-05' in your 'WeekLookUp' table.

Then you just set your textbox value to the variable strFiscalWeek's value.

Best regards,

Todd Shillam