Lookup a Week number in a table via DLookup

[Guest]

Hi

I have a set of week/period codes in atable that I want to reference on a
form so that when a date is entered, the corresponding Period is displayed,
based on the start and end dates of the period in the period table.

The Syntax:

Me!myWeek = DLookup("[strPeriod]", "[tblPeriods]", "Me![myDate] between
[dtStart] And [dtFinish]")

doesn't return the correct value.

I have a feeling I'm way off the mark with this one and would appreciate any
pointers! In the meantime I think I'll have a play doing this via a SQL
statement instead.

Ta

 

[SurendranSNV via AccessMonster.com]

Hi,

Instead of DLookup try this code.
===================================================================
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

rst.Open "tblPeriods", cnn, adOpenKeyset, adLockOptimistic, adCmdTable

rst.MoveFirst
If Not rst.BOF Or Not rst.EOF Then
Do Until rst.EOF
If Me!myDate >= rst!dtStart And Me!myDate <= rst!dtFinish Then
Me!myWeek = rst!strPeriod
Exit Do
End If
rst.MoveNext
Loop
End If
Set rst = Nothing
===================================================================
Goodluck,
Surendran

surendransnv at hotmail dot com

Hi

I have a set of week/period codes in atable that I want to reference on a
form so that when a date is entered, the corresponding Period is displayed,
based on the start and end dates of the period in the period table.

The Syntax:

Me!myWeek = DLookup("[strPeriod]", "[tblPeriods]", "Me![myDate] between
[dtStart] And [dtFinish]")

doesn't return the correct value.

I have a feeling I'm way off the mark with this one and would appreciate any
pointers! In the meantime I think I'll have a play doing this via a SQL
statement instead.

Ta