Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2013
    Posts
    70

    Unanswered: function with weekday not working

    hi, so here is another puzzle. the following are two pieces of code. the first is a public function to check for closed days or weekend days. the second is a piece of code to check if the date entered into a text box is valid (i.e. not in table of closed days or weekends) that it gets from the function.

    Code:
    Public Function KitchenClosed(theDate) As Integer
        
        KitchenClosed = False
        
        ' Test for Saturday or Sunday.
        If Weekday(theDate) = 1 Or Weekday(theDate) = 7 Then
           KitchenClosed = True
        ' Test for Holiday.
        ElseIf Not IsNull(DLookup("closedDate", "tblClosedDates", "[closedDate]=#" _
            & theDate & "#")) Then
            KitchenClosed = True
        End If
    End Function
    Code:
    Private Sub mealDate_BeforeUpdate(Cancel As Integer)
        'Dim myReply
        If KitchenClosed(mealDate) = True Then
            MsgBox ("You Entered a Weekend or Closed Date")
            Exit Sub
        End If
    End Sub
    the function (the first piece of code) has a problem. only the ElseIF. . .DLookup part works. the weekday part return true (0) no matter what date i put in. I have tested it in the immediate window and in the form.

    The second piece (mealDate_BeforeUpdate) works but it leaves the cursor in the second field. I want it to land back in the first field (where the before update code is).

    can anyone tell me what i have done wrong. i cannot see it. thanks, scott

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    First off the functions return datatype should be boolean not integer. Although boolean values can be coerced into integer its not a good idea

    Define the parameter in the function (theDate) to be of type datetime. If not defined it will be type variant. That could well be the problem. If the variable is defined as date then you dont need the # delimiter.


    Whn using the weekday function compare to te inbuilt constants vbsunday and vbsaturday instead of 1 or 7

    Use the intrinsic constants true / false or vbtrue / vbfalse instead of numbers to set a value. Plainly its daft to set numbers in one function yet compare to constants in another function
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2013
    Posts
    70

    function with weekday not working

    Hi, thanks for the answer. I made the following changes based on what I understood from your recommendations. It compiles, and DLookup still works when i use a date from the closed table, but it still returns False when I use a Saturday or Sunday date in the immediate window. Obviously I did not completely understand your comments. Could you please clarify them a little for a newby? Below is my code as changed. Your help is greatly appreciated. Thanks, Scott

    Code:
    Public Function KitchenClosed(theDate) As Boolean
        
        Dim closedDate As Date
        KitchenClosed = False
        
        ' Test for Saturday or Sunday.
        If Weekday(theDate) = vbSunday Or Weekday(theDate) = vbSaturday Then
           KitchenClosed = True
    
        ' Test for Holiday.
    ElseIf Not IsNull(DLookup("closedDate", "tblClosedDates", "[closedDate]=#" _
           & theDate & "#")) Then
            KitchenClosed = True
    
        End If
    
    End Function

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Not withstanding healdem remarks which I agree with, except I still need the # date delimiter??, your function seem to work OK.

    However I think maybe you could be falling foul of the date format with ambiguous dates (ie is this 04/01/2013 the first of 1st April or the 4th of Junuary?).

    Without knowing where you are in the world, or what you computer date format setting is, I suggest you could try this.

    [BankHoliday]=#" & Format(theDate, "mm/dd/yyyy") & "#"


    MTB

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    Public Function KitchenClosed(theDate as date) As Boolean
      Dim closedDate As Date
      KitchenClosed = False
      ' Test for Saturday or Sunday.
      If Weekday(theDate) = vbSunday Or Weekday(theDate) = vbSaturday Then
        KitchenClosed = True
       ' Test for Holiday.
      ElseIf Not IsNull(DLookup("closedDate", "tblClosedDates", "[closedDate]=" & theDate)) Then
        KitchenClosed = True
      End If
    End Function
    if that doesnt' work then try
    Code:
      ElseIf Not IsNull(DLookup("closedDate", "tblClosedDates", "[closedDate]=" & format(theDate,"#mm/dd/yyyy#"))) Then
    failing that step through the code and make certain you understand what it is doing
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2013
    Posts
    70

    function with weekday not working

    Hi as I said the DLookup works, but the changes here helped it a little. What DOES NOT work is the weekday part:

    Code:
      If Weekday(theDate) = vbSunday Or Weekday(theDate) = vbSaturday Then
        KitchenClosed = True
    I do not know how to step through code to debug. Before you take the time to type "check help", I have and I find it less than helpful. Sorry, but Microsoft's help is awful for VB. I am really frustrated. This cannot be this hard. Thanks in advance, Scott

    ps: I am posting this because it appears that the first time I replied it did not post. If I am missing it please accept my applogy.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    Public Function KitchenClosed(theDate As Date) As Boolean
      KitchenClosed = False
      If Weekday(theDate) = vbSunday Or Weekday(theDate) = vbSaturday Then
        KitchenClosed = True
      Else
        If Not IsNull(DLookup("mycolumn", "mytable", "[mycolumn] = #" & Format(theDate, "yyyy/mm/dd") & "#")) Then
    
          KitchenClosed = True
        End If
      End If
    End Function
    works

    if it doesn't for you then its down to the way you are calling the function or the parameter you are passing the function
    what steps have you taken to prove mealdate is a date variable
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2013
    Posts
    70
    nealDate is a field in:

    tblMeals
    mealID - autonumber PK
    mealDate - Date/Time short date
    mealTypeFKID - to tblMealTypeLU
    mealNumber - number long integer
    closedFKID - to tblClosedDates

    on the form mealDate is a text field that has an event procedure in the before update line. here is the code for mealDate:
    Code:
    Private Sub mealDate_BeforeUpdate(Cancel As Integer)
        If KitchenClosed(mealDate) = True Then
            MsgBox ("You Entered a Weekend or Closed Date. Please Reneter a Valid Weekday")
            Exit Sub
        End If
    End Sub
    is this what you are asking and do these answers make sense? thanks, scott

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no
    the function posted (after replacing the date & table names with valid ones at my end works plain and simple. so if its not working at your end its how you sending the value to be tested to the function.
    what value in mealdate are you testing it with?
    where is mealdate being set?
    is it a valid date

    if you are not up to speed with setting breakpoints and debugging VBA code then either do a google search and bone up on it (its an invaluable tool on developing code)
    OR as a worst case

    in the before update code BEFORE you call the function
    if not isdate(mealdate) then msgbox 'My data is crap, Mealdate (" & mealdate ") is not a date'
    OR
    if not isdate("#" & format(mealdate,"yyyy/mm/dd" & "#") then msgbox 'My data is crap, Mealdate (" & mealdate ") is not a date'

    you may need to coerce it to be a date

    eg
    dim Mydate as date
    mydate = cdate(mealdate)
    KitchenClosed(mealDate) = True Then....

    incidentally I don't know what you think you are doing with the exit sub statement. that will not do anything
    if you do not want the date to be accepted then you need to set the cancel flag = vbtrue. if its false or unset then the row will be saved
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2013
    Posts
    70

    function with weekday not working

    I guess that we should just end this as I do not know how to answer your last questions. I have no idea what you are asking.
    what value in mealdate are you testing it with? where is mealdate being set? is it a valid date?
    It is obvious that I do not have the knowledge needed to converse about this. I am sorry to have wasted your time. I am very frustrated. I do not do this as a profession or or regularly. I do it to for my own use. I build houses for a living. I have spent years learning little bits of VB so that I can work in Access. But not enough for this subject. Again, thanks for the help. Scott

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    fault finding in code is about identfying what is wrong, then proving what works till you find what doesn't work.
    its always tricky when you don't have the whole project in front of you and you are tryi8ng to provide poitners to others.

    the reality is the function works
    so if you are still experiencing problems its how you are using (calling) the code. As said above having proven the code works then its something upstream of that. My suspicion is that you are not sending the value you think you are to the function call.

    thats fairly easy to prove hence why I asked the questions I did
    in the kitchenclosed function display the value received
    try:-

    Code:
    msgBox "the date is:" & format(theDate,"dd mmm yyyy")
    KitchenClosed = False
    run the code and make certain the value dispalyed is the value you think you ares endign to the function

    when you view the code scroll up to the top and insert a line
    Code:
    option explicit
    then compile the code. this forces Access to ensure that all variables are declared before first use. that will trap any subtle errors caused by typo's

    if you have spent years learning little bits of Access AND you intend continuing AND using code then you owe it to yourself to get to grips with debugging. the reason:- well you cna actually see what your code is doing, what values exist where. you can precisely see what is happening, you can see what lines of code are running, what are not.

    in your case you are stating that
    only the ElseIF. . .DLookup part works. the weekday part return true (0) no matter what date i put in. I have tested it in the immediate window and in the form.
    were you using the debugging tools you could
    set a breakpoint on that function call
    step through each line of code
    examine what each element of the code is set to
    you can make changes then reset the code to run form a point.

    TBH I can't work out why your code isn't working I can only make guesses. I don't have your table, I don't have your form I only have a fragment of code which I have tested and it works.

    if the worst comes tot he worst I'd suggest posting your db here. to do so its best to take a copy
    strip out anything sensitive (real names, addresses phone numbers or anything that might hurt the organisation if it became public (posting here makes it public)
    strip out anything irrelevant to the problem in hand (any other tables, forms & reports)
    then compact and repair the db to reduce its size
    then compress it as a zip file and attach it to a post

    then soemone will look at the problem and see what is going on
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Mar 2013
    Posts
    70

    function with weekday not working

    so, i have buckled down and spent the night reading about debugging. i now think i have a handle on how this works. i can set a breakpoint and step through the code to see what is happening. i am not sure i understand what to do about what does not work, but now i think i can explain it to you.

    i set a breakpoint at:
    Code:
    If Weekday(theDate) = vbSunday Or Weekday(theDate) = vbSaturday Then
    since that is the thing that seems not to work. then i went back to the form and entered a date that is in the closed table (9/2/2013 is exactly as i entered it) in the text box txtMealDate. the code stopped and returned me to the line above. i then stepped through the code with F8. each line went by and in the end it put me back at the form and the msgbox that i expected came up. i cleared the msgbox and it returned me to the code and i hit F8 again until it came to the end of the code at end sub.

    then i went to the form and entered the saturday date 9/5/2013 (exactly like that) in txtMealDate just like i did with the 9/2/2013 date. it also put me at the line above in the code. so i again used F8 to step through the code. this time it went through the code until the final line at "end sub". it did not return me to the form and fire the msgbox as it did with the 9/2/2013 date.

    when i entered the 9/2/2013 date and hovered over the (theDate) in the code the bubble text showed 9/2/2013. when i entered the 9/5/2013 date the bubble text showed 9/5/2013. if i hover over vbSunday or vbSaturday it displays vbSunday=1 and vbSaturday=7 respectively.

    that is the result of my experiment. does this tell you anything? it seems to me that there is a problem with the code recognizing the weekend days. is that access or win7? is it possible that i do not a necessary reference chosed in my copy of access to make weekday work? does this give you anything you can work with? thank you for your patience. best, scott

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ok, so what does that tell you?
    In debug the value in the function is not the same as the value in the control on the form....

    You are calling the function using and passing the valye from mealdate
    Yet you are entering the date you want to validatein a control called txtmealdate.
    So no matter what you put in txtmealdate it is not being passed to the function.

    Im also confused. Which form of date are you using
    Us mm/dd/yyyy
    Or vittually everywhere else dd/mm/yyyy
    ...for some reason i thought you were us based.
    2nd sep 2013 is a monday 9/2/13
    5th sept is A thursday 9/5/13
    9th feb is sat 2/9/13
    9th may is a thursday 5/9/13
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Mar 2013
    Posts
    70
    well i am embarrased. i was fixated on the problem and could get it through my thick head that october is the 10th month. so i have been testing weekends with a weekday. i am so sorry, your patience is astounding. it works fine. i am now researching how to do the cancel. it think it is cancel = vbTrue. that seems to not post the date to the table after i clear the msgbox and enter a new date. i have tried it several times and the bad date seems to not end up in the table. again, thanks for all our help in this. i really have learned a lot in this conversation. best, scott

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no problems
    so you fixated on one part of code, we've all done that.....
    often you can obsess that something looks right and assume its right, whereas you shoudl always prove its right before movving on.

    what I'd suggest comes out of it is a more round problem solving approach.
    so what could you have done
    well you could prove the input to the function is valid with a mesage box such as:-
    msgbox "the date supplied is:" & format(thedate,"dddd, dd mmm yyyy").


    when a statements doesn't do what you know it should should then check the values being supplied to it

    the debugger in Access is a fantastic tool that will help you find errors of logic and data.
    you can output values tot he debug window using debug.print
    you can change values and code once the program has halted (you can't create new variables)
    you can examine expressions eg:-
    ?weekday(thedate)
    or
    ?format(thedate,"dddd, dd mmm yyyy")
    you can set values
    thedate = "12/10/2013"

    and the re run you code
    you can move the current pointer of the code to another line and say rerun that code block with known values or corrected code or both.

    but ultimately who cares, you got a fix for your (current) problem, time to move on to the next one..
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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