Solved

Filling date fields in a single record based on a weekday selection

Posted on 2015-01-04
12
254 Views
Last Modified: 2015-01-05
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.
0
Comment
Question by:mickferrari
  • 3
  • 2
  • 2
  • +4
12 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 167 total points
ID: 40531365
i'm assuming that the "weekday selection" relates to the next day of that type, so that today, selecting "Thursday" would result in 8 Jan '15, but selection of Monday would result in 12 Jan '15?

Although I don't normally use value lists in my combo boxes, this is one occassion where I think that would work OK.  Normally, I would use a combo box that has a numeric value representing the day of the week (the bound column), and then the day, something like

2, Mon, 3, Tue, 4, Wed, 5, Thur, 6, Fri

Then, you could use a combination of several date functions:

This would return the date associated with the selected weekday in the current week.
DateAdd("d", -Weekday(Date()) + me.cbo_WeekDay, Date())

If you want to do this so that the if the weekday selected is > todays date, I would set this value to a variable, then compare it todays date and add 7 if it is less than or equal to todays date.  Something like:

Dim dtDate as Date

dtDate = DateAdd("d", -Weekday(Date()) + me.cbo_Weekday, Date())
if dtDate <= Date() then dtDate = DateAdd("d", 7, dtDate)
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40531630
I can't really see anything wrong with @Dale Fye's solution
"I would like to use SQL..."
How then are you going about adding the record?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40531642
You can use one of the gems of Access, a callback function (look up the on-line help on this):
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

Open in new window

Simply, add the Format property to the combobox: dddd
to display weekday names while the value is the true date value

/gustav
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40531684
@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
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 167 total points
ID: 40532162
Callback functions date way, way back, so maybe the Help topics have disappeared.  In cases like this, I usually run some code to fill a temp table with current dates, using a procedure that runs from the Load event of the appropriate form.

Public Sub FillDateList()
'Created by Helen Feddema 18-Apr-2010
'Last modified by Helen Feddema 18-Apr-2011
'Table version

On Error GoTo ErrorHandler

   Dim dteSelect As Date
   Dim intAdd As Integer
   Dim intDay As Integer
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim strWeekday As String
   
   strSQL = "DELETE * FROM tlkpWeekdays"
   DoCmd.SetWarnings False
   DoCmd.RunSQL strSQL
   
   Set rst = CurrentDb.OpenRecordset("tlkpWeekdays")
   
   'Enter current dates into table
   For intAdd = 0 To 6
      dteSelect = DateAdd("d", intAdd, Date)
      intDay = Weekday(dteSelect)
      strWeekday = WeekdayName(intDay)
      rst.AddNew
      rst![SelectDate] = dteSelect
      rst![DayNo] = intDay
      rst![WeekdayName] = strWeekday
      rst.Update
   Next intAdd
      
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in FillDateList procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

Here is the filled table; you can use the fields in various ways, most likely as the row source of a combo box selector.
Weekdays table
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 41

Expert Comment

by:pcelba
ID: 40532178
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.
0
 

Author Comment

by:mickferrari
ID: 40532224
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!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40532252
@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!)
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40532296
My code could be expanded to put dates for a few weeks into the lookup table.
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 166 total points
ID: 40532374
If you have a tally table (numbers table) or query, you could easily populate a table with dates.  That is one of the examples in Tally Tables article: http:A_5410.html
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40532979
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now