UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Populate text box(es) from popup calendar date selection    
 
   
Haystakk
post Dec 18 2005, 06:50 PM
Post#1



Posts: 4
Joined: 18-December 05



I have searched the forums for similar threads but cannot quite find the situation I am in:
I have a form which contains a date selector via the popup calendar control available in Access. Once a user selects the date from the popup calendar I would like it to populate a text box on the form with the day of the week based on the date selected.
It would also be good if the selected date could also populate another text box a week number that the date relates to. I have a seperate database table with all the dates in 2006 in one column along with the week number that the range of dates apply to in the next column - I assume this is what is needed.
Are there any ways to do this, and if so, please let me know?!
Go to the top of the page
 
dannyseager
post Dec 18 2005, 06:59 PM
Post#2


UtterAccess VIP
Posts: 13,031
Joined: 2-March 04
From: Leicester, UK


Welcome to UA!!
o get the week number of a date try
=Format([YourDate],"ww")
I've never used the calendar you are using but I would imagine that there is an on click event... some code like
Forms!yourform!TheTextBoxToPopulate = me.TheCalendarControlsName
Go to the top of the page
 
Haystakk
post Dec 18 2005, 07:10 PM
Post#3



Posts: 4
Joined: 18-December 05



Hi - that works a treat, however the form I am designing is for a student absence tracker for a college in the UK and as such the week numbers are not as simple as that!
That if I were to tell you that the first week in January (according to the college calendar) is actually week 22, the second week is 23 and so-on? What can I do to allow for this?
Thanks for the help so far - it's greatly appreciated.
Go to the top of the page
 
dannyseager
post Dec 18 2005, 07:17 PM
Post#4


UtterAccess VIP
Posts: 13,031
Joined: 2-March 04
From: Leicester, UK


How About...

=Cint(Format([YourDate],"ww")) + 21
Go to the top of the page
 
Haystakk
post Dec 18 2005, 07:31 PM
Post#5



Posts: 4
Joined: 18-December 05



I have just realised that I should've mentioned that as like there are 52 weeks in the year, when you get to the first week in August the week pattern needs to reset to week #1:
.g. 1st week Jan to last week July -> weeks 23 to 52
1st week August to last week Dec-> weeks 1 to 22
Therefore the offset, yes, needs to be against access's common week numbers, but with the above method when you get into a certain week in December you get week #72 for example, because it's adding 21 to 51!
BTW, where should the "=Format...." method you mention be put? I just want to make sure I get this right!
Go to the top of the page
 
dannyseager
post Dec 18 2005, 08:01 PM
Post#6


UtterAccess VIP
Posts: 13,031
Joined: 2-March 04
From: Leicester, UK


You can put it in a query, form or report... You should not store this value though as it can easily be calculated at any time and storing the data would be redundant and possibly cause loss of data integrity later on if changes are made...
k... what you want to do is a little more complicated but these 2 functions should help....
CODE
Public Function SchoolWeekNo(dtDate As Date) As Integer
'Make sure a date was passed
If Len(dtDate & "") = 0 Then
Exit Function
Else
End If
If CInt(Format(dtDate, "ww") > CInt(Format(FirstMonday(8, Year(Now())), "ww"))) Then
    SchoolWeekNo = CInt(Format(dtDate, "ww") - CInt(Format(FirstMonday(8, Year(Now())), "ww")))
Else
    SchoolWeekNo = CInt(Format(dtDate, "ww")) + 21
End If
End Function
Public Function FirstMonday(intMonth As Integer, intYear As Integer) As Date
Dim i As Long
Do While i < 31
    If Weekday(DateSerial(intYear, intMonth, i), vbMonday) = 1 Then
        'It's a monday
        FirstMonday = DateSerial(intYear, intMonth, i)
        Exit Function
    Else
    End If
O= i + 1
Loop
End Function

Paste them into a new Module and then you can call the SchoolWeekNo from a query/form/report.
Practice in a query by adding the table with the dates in and then adding into a blank column..
CODE
WeekNo:SchoolWeekNo([YourDateField])

Just change the [YourDateField] for your actual date field.
Go to the top of the page
 
Haystakk
post Dec 19 2005, 06:32 PM
Post#7



Posts: 4
Joined: 18-December 05



Just going back to this point, where would the ' =Format([YourDate],"ww") ' bit go in terms of the field properties? I just want to experiment with the methods you've talked about - thanks in advance - you have been very helpful already!
Edited by: Haystakk on Mon Dec 19 18:52:47 EST 2005.
Go to the top of the page
 
dannyseager
post Dec 21 2005, 07:47 AM
Post#8


UtterAccess VIP
Posts: 13,031
Joined: 2-March 04
From: Leicester, UK


it would go in the control source of a text box control
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd May 2018 - 03:37 AM