mickferrari
asked on
Filling date fields in a single record based on a weekday selection
I would like to be able to fill date fields in a single record based on a weekday selection by a user.
for example if a user selected "Thursday" and then clicked a build Order button the resulting values in fields "Date01", "Date02" and "Date03" in the table would be "08/01/15".
I would like to use SQL but i am not restricted by it.
I would use a unique ID number to target the specific record to be updated.
Thanks in advance for any help or advice.
for example if a user selected "Thursday" and then clicked a build Order button the resulting values in fields "Date01", "Date02" and "Date03" in the table would be "08/01/15".
I would like to use SQL but i am not restricted by it.
I would use a unique ID number to target the specific record to be updated.
Thanks in advance for any help or advice.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use one of the gems of Access, a callback function (look up the on-line help on this):
to display weekday names while the value is the true date value
/gustav
Public Function ListDaysOfWeek( _
ctl As Control, _
lngId As Long, _
lngRow As Long, _
lngCol As Long, _
intCode As Integer) _
As Variant
' Length of week in days.
Const cbytDaysInWeek As Byte = 7
' Offset of first day in week relative to the first day of the week of today's date.
Const clngOffset As Long = cbytDaysInWeek * -1
' Choose first day in week to display.
Const cbytDayInWeek As Byte = vbMonday
' Example: Display days of next week starting with Monday.
' Const clngOffset As Long = cbytDaysInWeek * 1
' Const cbytDayInWeek As Byte = vbMonday
'
' 2002-02-04. Cactus Data ApS, CPH.
Static datFirstDate As Date
Static strFormat As String
Dim datDate As Date
Dim lngOffset As Long
Dim varValue As Variant
Select Case intCode
Case acLBInitialize
datDate = Date
lngOffset = clngOffset + 1 - Weekday(datDate, cbytDayInWeek)
datFirstDate = DateAdd("d", lngOffset, datDate)
strFormat = ctl.Format
varValue = True ' True to initialize.
Case acLBOpen
varValue = Timer ' Autogenerated unique ID.
Case acLBGetRowCount ' Get rows.
varValue = cbytDaysInWeek ' Set number of rows.
Case acLBGetColumnCount ' Get columns.
varValue = 1 ' Set number of columns.
Case acLBGetColumnWidth ' Get column width.
varValue = -1 ' Use default width.
Case acLBGetValue ' Get the data for each row.
varValue = DateAdd("d", lngRow, datFirstDate)
Case acLBGetFormat ' Format the data.
varValue = strFormat ' Use format of control.
Case acLBEnd
' Do something when form with listbox closes or
' listbox is requeried.
End Select
' Return Value.
ListDaysOfWeek = varValue
End Function
Simply, add the Format property to the combobox: ddddto display weekday names while the value is the true date value
/gustav
@gustav
'look up the on-line help on this'
I didn't really Google up anything much on callback functions, or why they were a gem.
But the idea behind it is really neat.
Instead of a static ListBox or ComboBox and code to calculate the date as proposed by @Dale Fye, you propose to build the control with today and the next six days and let the format "dddd" handle showing the weekdays.
Does your code display them in weekday order (Mon. Tues. Wed...) or Date order?
Nick67
'look up the on-line help on this'
I didn't really Google up anything much on callback functions, or why they were a gem.
But the idea behind it is really neat.
Instead of a static ListBox or ComboBox and code to calculate the date as proposed by @Dale Fye, you propose to build the control with today and the next six days and let the format "dddd" handle showing the weekdays.
Does your code display them in weekday order (Mon. Tues. Wed...) or Date order?
Nick67
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Imagine the situation user needs to select the Monday in two weeks but he has to wait one week because there is no option to extend today's selection over the next week...
Why don't you simply use any date picker control? It shows the Week day and also the date which makes the selection exact and unambiguous, you don't need to calculate additional results etc. etc.
Why don't you simply use any date picker control? It shows the Week day and also the date which makes the selection exact and unambiguous, you don't need to calculate additional results etc. etc.
ASKER
Excellent suggestions everyone.
I'll try a few things that have been suggested to see if I can get the result I want.
Now that I have generated a bit of interest in my question I will explain a bit further what I am doing.
I have a table called tbl_lkupSaleDays. This table stores the days of the week that sales deliveries usually take place during any given week. when my database builds a new sales week (one month in advanced of the current date) I want to look at the tbl_lkupSaleDays table to determine what dates to fill into this new week. By doing this I give the user the flexibility to change what weekday the delivery will be. It's all for future planning and building sales weeks before they happen. The start of any new week created is always a Monday so the start of the week date is easy to calculate.
I am using SQL at the moment to insert new records into tables to build my future weeks and that is why I would like to continue with SQL when inserting the conversion of weekdays into a date. But like I have said, to get a solution I don't need to use SQL.
Thanks!
I'll try a few things that have been suggested to see if I can get the result I want.
Now that I have generated a bit of interest in my question I will explain a bit further what I am doing.
I have a table called tbl_lkupSaleDays. This table stores the days of the week that sales deliveries usually take place during any given week. when my database builds a new sales week (one month in advanced of the current date) I want to look at the tbl_lkupSaleDays table to determine what dates to fill into this new week. By doing this I give the user the flexibility to change what weekday the delivery will be. It's all for future planning and building sales weeks before they happen. The start of any new week created is always a Monday so the start of the week date is easy to calculate.
I am using SQL at the moment to insert new records into tables to build my future weeks and that is why I would like to continue with SQL when inserting the conversion of weekdays into a date. But like I have said, to get a solution I don't need to use SQL.
Thanks!
@pcelba
Why don't you simply use any date picker control?
Well, because the Calendar control (MSCal.ocx) was deprecated, for one and replaced by the DatePicker.
So, if the asker is supporting multiple versions of Access that gets a bit hard. You need a multiple version date picker.
Now I have made the Calendar control work in all versions of Access since MS deprecated it (damn their eyes!) but that's not for everyone.
Mainly though, the DatePicker does not provide a nice event to hang logic off of -- and THAT is a pain (no real AfterUpdate -- and Change is very painful to use)
Which is why I make the Calendar control continue to work despite MS (again damn their eyes!)
Why don't you simply use any date picker control?
Well, because the Calendar control (MSCal.ocx) was deprecated, for one and replaced by the DatePicker.
So, if the asker is supporting multiple versions of Access that gets a bit hard. You need a multiple version date picker.
Now I have made the Calendar control work in all versions of Access since MS deprecated it (damn their eyes!) but that's not for everyone.
Mainly though, the DatePicker does not provide a nice event to hang logic off of -- and THAT is a pain (no real AfterUpdate -- and Change is very painful to use)
Which is why I make the Calendar control continue to work despite MS (again damn their eyes!)
My code could be expanded to put dates for a few weeks into the lookup table.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, but using the callback method is the solution to tasks like this.
> Does your code display them in weekday order (Mon. Tues. Wed...) or Date order?
As is, it does.
/gustav
> Does your code display them in weekday order (Mon. Tues. Wed...) or Date order?
As is, it does.
/gustav
"I would like to use SQL..."
How then are you going about adding the record?