Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2013
    Posts
    70

    Unanswered: date entering quandry

    Hi, I have a table that collects the date and the number of meals served in a community kitchen. I have a form so that the person entering the data can type in the date and then choose the type of meal and then enter the number of meals served. I need a way to qualify the date. The days need to be a weekday and not in a table of closed weekdays (i.e. 4th of July or Christmas). Anyone have a thought on how to accomplish this? Thanks, Scott

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Use the before update event of either the textbox or form, which can be cancelled, like here:

    Before update

    In your case, you can use the Weekday to test the day of the week, and a DCount() to see if the date is in your table of closed weekdays:

    General: DLookup Usage Samples
    Paul

  3. #3
    Join Date
    Mar 2013
    Posts
    70

    date entering quandry

    hi, thanks for the response. i cannot for the life of me figure out how to use the weekday function to validate the date entered. I have a date/time field and i want to enter say 9/16/2013 and have it accept it if it is a weekday and say no or some error message if it is a weekend. I am sorry if i did not understand your reply and the answer is there. but i just don't get it. i am currently using a table with the dates in it but it is getting unwieldy keeping ahead and not making any mistakes. i am still working on the DLookup for the closed dates. Thanks. Scott

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    personally I'd apprach things a bit differently
    Id' use either
    a datepicker control and filter out bad dates after the event
    or
    two combo linked boxes, forst one shows months, second one shows available days in that month)

    of the two Id prefer the combo box approach as you constrain the users to enter alid dates. I prefer letting users choose from valid data rather than validating after the vent and requesting they pick a valid date. dates can be tricky they can be a minefield to validate especially if your application is multi cultural ie uese proper date formats dd/mm/yyyy and the abomination that is us format mm/dd/yyyy. of course you can get round that by always using ISO dates yyyy/mm/dd

    in either event you need some form of storage that identifies what are invalid dates, probably a table listing invlalid dates. AS every weekend is invalid in your case y'don't need to store the weekends just the public holidays

    if you aren't up to speed on combo boxes then Id suggest using a datepicker control

    so create a table for public holidays, called say PublicHolidays (who said system people can't be creative )
    table PublicHolidays
    Holiday datetime PK
    define you public holidays there
    eg
    25/12/13
    01/01/14
    .......
    the advantage of definign such things in a table is that the maintainance of that becomes the property of the users of the data,not you as developer. its part of the users year end processes.. to set up the holiday dates

    you need a couple of functions
    one should look up to see if a specified date is in the public holidays table

    Code:
    private function IsPublicHoliday(ThisDay as date) as boolean
      'this function tests if a supplied date is in the public holidays table and returns true if so
      if not isnull(dlookup("Holiday", "PublicHoliday", "Holiday = " & ThisDay)) then
        'we found the date in the table
         IsPublicHoliday = true
      else
        IsPublicHoliday=false
      endif
    end function
    if you use the linked combo boxes you need something which finds all the valid dates for a specified period
    Code:
    public function GetFreeDatesInMonth(FirstOfTheMonth, optional IncludeWeekends as boolean = false, optional separator as string = ";") as string
    'this function takes a date and returns all free dates to the end of that month
    'natch you could use any valid date but its designed to be the first of the month. if thats a problem then alter the code to check for first of the month
    'there are two optional parameters:-
    'IncludeWeekends is a boolean that indicatesif weekends shoudl be returned
    'Separator is a string that you use to delimit dates returned. the default is the right one for list/combo boxes
    GetFreeDatesInMonth = "" 'set up our defualt return value
    if isnull(FirstOfTheMonth) then ' user pulled a fast one with a null date so return an empty string
        exit function
    else
      'ok so we must have been supplied with a valid date
      Dim ThisDay as date
      thisday = firstofthemonth
      while month(thisday) = month(firstofthemonth)
        if (IncludeWeekends = true  OR (weekday(thisday) > vbsunday AND weekday(thisday) < vbsaturday)) then
          if IsPublicHoliday(thisday) = false then
              GetFreeDatesInMonth = GetFreeDatesInMonth & format(thisdate,"dd mmm yyyy") & ";"
          endif      
        endif
        thisday = dateadd( "d", 1, Thisday)
      wend
      'chop off the trailing semi colon
      if len(GetFreeDatesInMonth) > 1 then
        GetFreeDatesInMonth = left(GetFreeDatesInMonth, len(GetFreeDatesInMonth)-1)
      endif
    endif

    the above code comes with the equivalent of a tailpipe warranrty. its not been tested, its not been validated you need to make certain it meets your requirements before using. you may/will need to correct typos, investigate the code and look for errors. its
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2013
    Posts
    70

    date entering quandry

    hi, first, thanks for the time and patience. let me ask you a more basic question that will inform my decision on which avenue to take. i (think i) need to store the date, type of meal served and number of each type of meal served. each date has four types of meals. so i would have a record like:
    Table: tblMealList
    Field: mealID autonumber PK (my personal choice)
    Field: mealTypeFKID foreign key to lookup table with 4 meal types
    Field: mealDate date a particular type of meal is served
    Field: mealNumbers number of a type of meal served on a particular day

    this would mean that i will be storing the same date 4 times for a day's meal record. not very efficient. but it would allow me to validate the dates without creating and maintaining a gooddates table.

    right now i have a gooddates table of weekdays. i store the index number from the dates table in tblMealList. so still 4 times but a much smaller footprint.

    maybe there is a better way? thanks, scott

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I could see you have an entity which defined mealtypes, say breakfast, lunch, dinner and something else. you could then have a composite PK of date and mealtype

    however that leads on to other problems (such as is there only on dish per meal type..prob OK for Breakfast but doubtful for whatever is the main meal of the day)).

    those are design decisions that are down to you as you know the cope of the actual requirement

    your original question wanted a way to handle date data entry.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2013
    Posts
    70

    date entering quandry

    hi, i am not quite getting what you are referring to. how do i use the composite key to normalize? i can only see it working with a dates table. i guess i need more direction.

    as far as the meal type is concerned, it is a term we use to describe whether a child , adult or senior is eating. we have reporting requirements and they include breaking down our meals to who eats. so child or adult or senior. the data needs: how many children, how many adults and how many seniors eat each day (m-f, less closed holidays). my original question started because i realized that i was not normalizing my data. and i was afraid that another person entering data other than me would probably make mistakes and i needed to make it idiot proof. so i got on this track of structure and design and validating. i think this is more important. i need to normalize the data. thanks, scott

  8. #8
    Join Date
    Mar 2013
    Posts
    70

    date entering quandry

    a little more clarification. lunch only, m-f. interested only in just meals served by type. hope that helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •