Look up a week number

[Gordon]

I have a table (tblDates) which contains a list of week numbers
(fldWeekNo) and corresponding start (fldStartDate)and end dates
(fldEndDate) for each week no - used to calculate financial week
numbers.

What I am trying to do is work out what the current financial week
number is
from a given date that is inserted by the user into a bound text box
(txtVisitDate), for which the control source = fldVisitDate.

When txtVisitDate is updated, I want a second unbound text box
(txtWeekNo) on the same form to be updated with the appropriate week
number.

Can someone suggest the code I need to do this.

Thanks
Gordon

 

[Gordon]

It's probably better to use an expression in the errl number
taxt box:

=DLookup("fldWeekNo", "fldWeekNo", Format(txtVisitDate,
"\#m\/d\/yyyy\#") & " Between fldStartDate And fldEndDate)

Thanks for coming back so quickly. Correcting your air code, I put
the following as the control source of the Week No text box:
=DLookup("fldWeekNo", "tblDates", Format(fldVisitDate, "\#mm\/ddd\/yyyy
\#") & " Between fldStartDate And fldEndDate)

...but alas...I keep getting an error message about an invalid string.

Where am I going wrong?


Gordon

 

[Marshall Barton]

I have a table (tblDates) which contains a list of week numbers
(fldWeekNo) and corresponding start (fldStartDate)and end dates
(fldEndDate) for each week no - used to calculate financial week
numbers.

What I am trying to do is work out what the current financial week
number is
from a given date that is inserted by the user into a bound text box
(txtVisitDate), for which the control source = fldVisitDate.

When txtVisitDate is updated, I want a second unbound text box
(txtWeekNo) on the same form to be updated with the appropriate week
number.

It's probably better to use an expression in the errl number
taxt box:

=DLookup("fldWeekNo", "fldWeekNo", Format(txtVisitDate,
"\#m\/d\/yyyy\#") & " Between fldStartDate And fldEndDate)

 

[Powderfinger]

=DLookup("fldWeekNo", "tblDates", Format(fldVisitDate, "\#mm\/ddd\/yyyy\#")
& " Between fldStartDate And fldEndDate")

 

[Marshall Barton]

Gordon,
Sorry about my lack of typing skills. Good of you to find
and fix one of them.

Powderfinger(?),
Thanks for catching the other one.

I hope that between the two of you the expression now does
what we want it to do.

 

[Gordon]

Gordon,
Sorry about my lack of typing skills. Good of you to find
and fix one of them.

Powderfinger(?),
Thanks for catching the other one.

I hope that between the two of you the expression now does
what we want it to do.
--
Marsh
MVP [MS Access]

Thanks for coming back so quickly. Correcting your air code, I put
the following as the control source of the Week No text box:
=DLookup("fldWeekNo", "tblDates", Format(fldVisitDate, "\#mm\/ddd\/yyyy
\#") & " Between fldStartDate And fldEndDate)

..but alas...I keep getting an error message about an invalid string.- Hide quoted text -

- Show quoted text -

Hey guys,

Thanks for your help. The correction to the expression still doesn't
do what I want. I'm sure you've got the expression right so the
problem must lie elsewhere. I've checked the lookup table (tblDates),
yes there are fields called fldWeekNo, fldStartDate, fldEndDate. My
form (simplified) contains 2 text boxes. The first is bound to a
field called fldVisitDate from a data table (the text box is also
called fldVisitDate).

When the user enters a date in text box fldVisitDate, I want the week
no to appear in a second unbound text box, called txtWeekNo and whose
data source is the DLookup expression:

=DLookUp("fldWeekNo","tblDates",Format([txtVisitDate],"\#mm\/ddd\/yyyy
\#") & " Between fldStartDate And fldEndDate")

All I get is "#Name?" in the txtWeekNo box.

Help !

Gordon

 

[Gordon]

Works a treat now. Thanks Powderfinger.

Gordon

 

[Powderfinger]

Sorry we wern't clearer. The expression must be used in the afterUpdate
event of your fldVisitDate text box. Also, I saw another error, the "ddd"
should be "dd" in the format function. Try this:

Private Sub fldVisitDate _AfterUpdate()
Dim strCriteria As String
strCriteria = Format(Me.fldVisitDate, "\#mm\/dd\/yyyy\#") & " Between
[FDate] And [PDate]"
Me.txtWeekNo = DLookup("fldWeekNo","tblDates",strCriteria )
End Sub

Gordon,
Sorry about my lack of typing skills. Good of you to find
and fix one of them.

Powderfinger(?),
Thanks for catching the other one.

I hope that between the two of you the expression now does
what we want it to do.
--
Marsh
MVP [MS Access]

Gordon wrote:
I have a table (tblDates) which contains a list of week numbers
(fldWeekNo) and corresponding start (fldStartDate)and end dates
(fldEndDate) for each week no - used to calculate financial week
numbers.

What I am trying to do is work out what the current financial week
number is
from a given date that is inserted by the user into a bound text box
(txtVisitDate), for which the control source = fldVisitDate.

When txtVisitDate is updated, I want a second unbound text box
(txtWeekNo) on the same form to be updated with the appropriate week
number.

It's probably better to use an expression in the errl number
taxt box:

=DLookup("fldWeekNo", "fldWeekNo", Format(txtVisitDate,
"\#m\/d\/yyyy\#") & " Between fldStartDate And fldEndDate)

Thanks for coming back so quickly. Correcting your air code, I put
the following as the control source of the Week No text box:
=DLookup("fldWeekNo", "tblDates", Format(fldVisitDate, "\#mm\/ddd\/yyyy
\#") & " Between fldStartDate And fldEndDate)

..but alas...I keep getting an error message about an invalid string.-

Hide quoted text -

- Show quoted text -

Hey guys,

Thanks for your help. The correction to the expression still doesn't
do what I want. I'm sure you've got the expression right so the
problem must lie elsewhere. I've checked the lookup table (tblDates),
yes there are fields called fldWeekNo, fldStartDate, fldEndDate. My
form (simplified) contains 2 text boxes. The first is bound to a
field called fldVisitDate from a data table (the text box is also
called fldVisitDate).

When the user enters a date in text box fldVisitDate, I want the week
no to appear in a second unbound text box, called txtWeekNo and whose
data source is the DLookup expression:

=DLookUp("fldWeekNo","tblDates",Format([txtVisitDate],"\#mm\/ddd\/yyyy
\#") & " Between fldStartDate And fldEndDate")

All I get is "#Name?" in the txtWeekNo box.

Help !

Gordon

 

[Marshall Barton]

While that should work in single form view, it would not be
effective in a continuous form. As long as the expression
refers to another text box, it should be recalculated
automatically whenever the other text box's value changes.
--
Marsh
MVP [MS Access]

Sorry we wern't clearer. The expression must be used in the afterUpdate
event of your fldVisitDate text box. Also, I saw another error, the "ddd"
should be "dd" in the format function. Try this:

Private Sub fldVisitDate _AfterUpdate()
Dim strCriteria As String
strCriteria = Format(Me.fldVisitDate, "\#mm\/dd\/yyyy\#") & " Between
[FDate] And [PDate]"
Me.txtWeekNo = DLookup("fldWeekNo","tblDates",strCriteria )
End Sub

Gordon,
Sorry about my lack of typing skills. Good of you to find
and fix one of them.

Powderfinger(?),
Thanks for catching the other one.

I hope that between the two of you the expression now does
what we want it to do.


Gordon wrote:
Gordon wrote:
I have a table (tblDates) which contains a list of week numbers
(fldWeekNo) and corresponding start (fldStartDate)and end dates
(fldEndDate) for each week no - used to calculate financial week
numbers.

What I am trying to do is work out what the current financial week
number is
from a given date that is inserted by the user into a bound text box
(txtVisitDate), for which the control source = fldVisitDate.

When txtVisitDate is updated, I want a second unbound text box
(txtWeekNo) on the same form to be updated with the appropriate week
number.

It's probably better to use an expression in the errl number
taxt box:

=DLookup("fldWeekNo", "fldWeekNo", Format(txtVisitDate,
"\#m\/d\/yyyy\#") & " Between fldStartDate And fldEndDate)

Thanks for coming back so quickly. Correcting your air code, I put
the following as the control source of the Week No text box:
=DLookup("fldWeekNo", "tblDates", Format(fldVisitDate, "\#mm\/ddd\/yyyy
\#") & " Between fldStartDate And fldEndDate)

..but alas...I keep getting an error message about an invalid string.- Hide quoted text -

- Show quoted text -

Thanks for your help. The correction to the expression still doesn't
do what I want. I'm sure you've got the expression right so the
problem must lie elsewhere. I've checked the lookup table (tblDates),
yes there are fields called fldWeekNo, fldStartDate, fldEndDate. My
form (simplified) contains 2 text boxes. The first is bound to a
field called fldVisitDate from a data table (the text box is also
called fldVisitDate).

When the user enters a date in text box fldVisitDate, I want the week
no to appear in a second unbound text box, called txtWeekNo and whose
data source is the DLookup expression:

=DLookUp("fldWeekNo","tblDates",Format([txtVisitDate],"\#mm\/ddd\/yyyy
\#") & " Between fldStartDate And fldEndDate")

All I get is "#Name?" in the txtWeekNo box.

 

[Marshall Barton]

Thanks for your help. The correction to the expression still doesn't
do what I want. I'm sure you've got the expression right so the
problem must lie elsewhere. I've checked the lookup table (tblDates),
yes there are fields called fldWeekNo, fldStartDate, fldEndDate. My
form (simplified) contains 2 text boxes. The first is bound to a
field called fldVisitDate from a data table (the text box is also
called fldVisitDate).

When the user enters a date in text box fldVisitDate, I want the week
no to appear in a second unbound text box, called txtWeekNo and whose
data source is the DLookup expression:

=DLookUp("fldWeekNo","tblDates",Format([txtVisitDate],"\#mm\/ddd\/yyyy
\#") & " Between fldStartDate And fldEndDate")

All I get is "#Name?" in the txtWeekNo box.

#Name indicates that the name of the other text box is
wrong. We're using txtVisitDate nut you keep talking about
fldVisitDate. Double check the names and their spelling.