Look up a week number

  • Thread starter Thread starter Gordon
  • Start date Start date
G

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
 
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)
Click to expand...

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
 
Gordon said:
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.
Click to expand...


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)
 
=DLookup("fldWeekNo", "tblDates", Format(fldVisitDate, "\#mm\/ddd\/yyyy\#")
& " Between fldStartDate And fldEndDate")
 
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,
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)
Click to expand...
..but alas...I keep getting an error message about an invalid string.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 
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 said:
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.
Click to expand...
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.
Click to expand...
When txtVisitDate is updated, I want a second unbound text box
(txtWeekNo) on the same form to be updated with the appropriate week
number.
Click to expand...
It's probably better to use an expression in the errl number
taxt box:
Click to expand...
=DLookup("fldWeekNo", "fldWeekNo", Format(txtVisitDate,
"\#m\/d\/yyyy\#") & " Between fldStartDate And fldEndDate)
Click to expand...
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)
Click to expand...
..but alas...I keep getting an error message about an invalid string.-
Click to expand...
Hide quoted text -

- Show quoted text -
Click to expand...

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
Click to expand...
 
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 said:
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 -
Click to expand...


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.
Click to expand...
Click to expand...
 
Gordon said:
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.
Click to expand...


#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.
 
Back
Top