Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2006
    Posts
    178

    Unanswered: null value error that i cant undestand... any ideas?

    Hi guys

    I have a table called TblWeeks that has several fields the primary key is the date which is in the first column. I also have a field called WEMW (is for weekend or midweek) this is a checkbox column yes/no.

    On a form i have a combo box with the rowsource TblWeeks and 2 check boxes which indicate if the day is midweek or weekend. the check boxes are called chk2 and chk3

    The afterupdate event i have put the following code.

    Code:
    Private Sub DateofWorkSt_AfterUpdate()
    Dim CWE As Boolean
    
    CWE = DLookup("[WEMW]", "TblWeeks", "[Date] =" & [Forms]![frminitial]![DateofWorkSt])
    Debug.Print CWE
    If CWE = True Then
    [Forms]![frminitial]![chk2] = True
    Else
    [Forms]![frminitial]![chk3] = True
    End If
    
    
    End Sub
    But when this is run i get this error message

    RUNTIME ERRROR '94'
    invalid use of NULL


    Can someone please help

    Thanks

    aboo
    Last edited by aboo; 05-03-09 at 16:56.

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    DLookup() returns a NUll if the record is not located. Are all of these controls on the frminitial form?
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    May 2006
    Posts
    178
    Hi,

    Dlookup returns a false not a null. (when i use debug) ... because i am using a combo box all teh dates have to be in there. it is impossible for the date not to be in the table.

    yes all the controls are in frminitial.

    Any ideas?


    Aboo
    Last edited by aboo; 05-03-09 at 17:06.

  4. #4
    Join Date
    May 2006
    Posts
    178
    Hi,

    im uploading a sample, if anyone has time to take a look, id appreaciate the help.

    I have changed the code to read


    Code:
    CWE = DLookup("[WEMW]", "TblWeeks", "[date] = #" & [Forms]![frmInitial]![DateofWorkSt] & "#")
    this seemed to work, how ever i notcied that some weekends it will tick the weekday box.

    again, looking for help.

    aboo
    Last edited by aboo; 05-08-09 at 16:59.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or alternatively why store this flag in the first place you can easily enough find what day a specific date is. If I remeber correctly its a function called dayofweek
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2006
    Posts
    178
    hi healdem,

    The table also has planning weeks by which we work aswell as fiscal weeks.

    I created the table to so i can extract the week number aswell. Thing is i cant even get the first part of it working properly.

    I have solved teh null error problem by using the # on the dates. however when i choose some weekends although the table shows as true, in the debug.print the value extracted shows as false.



    aboo

  7. #7
    Join Date
    May 2006
    Posts
    178
    Hi

    I have modified it now to return week and day values, but it still picks up wrong weekdays and still picks up false info regarding weekends and weekdays.

    Im pulling my hair out! ..lol


    aboo
    Last edited by aboo; 05-08-09 at 16:59.

  8. #8
    Join Date
    Mar 2007
    Posts
    277
    Does this version perform correctly?
    Attached Files Attached Files
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  9. #9
    Join Date
    Mar 2007
    Posts
    277
    BTW, is there a particular day you pick that comes back incorrectly? It seems to work fine for me right out of the box.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  10. #10
    Join Date
    May 2006
    Posts
    178
    Hey Rural guy,

    It doesnt when you select 04/01/2009 it selects midweek. it should show weekend as the flag is True.

    any ideas? check my last post with attachment. When i pck 04/01/2009 it shows a wednesday for some reason.

    aboo
    Last edited by aboo; 05-03-09 at 19:35.

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    DUH. The first of April WAS a Wednesday!

    Bit of a cryptic help, but I think you will find the answer to your problem now
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by aboo
    Hey Rural guy,

    It doesnt when you select 04/01/2009 it selects midweek. it should show weekend as the flag is True.

    any ideas? check my last post with attachment. When i pck 04/01/2009 it shows a wednesday for some reason.

    aboo
    probably becuase you haven't been explicit in your use of dates
    Are you using US/Canadian dates (mm/dd/yy) or the style of dates used in the rest of the English speaking world (dd/mm/yy). becuase 01/04/09 in the usa & canada may well have been a Sunday, but elsewhere its was a Wednesday
    04 Jan was a sunday
    01 April 2009 was a wednesday.

    if you persist with your current design you are leaving your application wide open to data errors.

    incidentally your user interface (form design) is screwy.. you don't use check boxes for mutually exclusive choices.. you use radio buttons.
    you current user interface suggest a user could select both, which frankly is daft.

    as mentioned before you are storing derived data, you don't need to do it, so don't. a redesign of the table (basing the identification of whether its a working day or not on the dayoftheweek function will stop data errors compromising your application and stop your application beeing undermined by users loosing confidence in your design.
    you may then also redesing your whole table (afaik all you need is a simple translation from true calendar dates into accoiunting calendar dates

    thats as simple as
    dates
    periodenddate
    accountingperiod

    eg
    09/jan/09 41
    16/jan/09 42


    you don't need from year to year.... arguably you need an accounting year
    thats as simple as
    dates
    periodenddate
    accounnting year
    accountingperiod

    eg
    09/jan/09 2008 41
    16/jan/09 2008 42

    making the pk of that table the accounting year and period no means that you cannot double define a row, so that reduces the risk of a data error
    making the periodenddate unique likewise
    whether you choose period enddate or period start date is irrelevant (one is one day different to the other.

    truswt me
    the route you are going down with this design is flawed, it will bite you in the backside at some stage
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    May 2006
    Posts
    178
    Healdem,

    The database i am designing has the check boxes and week number locked, the only choice the user has are the dates.

    Previous to this i used combobox.column(x) to take from the combo box and fill the week and check boxes. i dont want to use this because when i compile the code it shows error.

    Again the check boxes are locked. It just begs the question why doesnt my Dlookup Work?

    ok, if i used dayofweek, how can i calculate week number? when a new week starts on a saturday and soem years we have a week 53. and fiscal week we may ave a 10 day week at the end of the year just so week 1 begins at the 1st of april.


    Hey Trekky,

    04/01/2009 in uk was sunday. you think i have formatted somthing wrong?


    Aboo

  14. #14
    Join Date
    May 2006
    Posts
    178
    ok, im still stuck.. i have used this code...

    Code:
    Private Sub DateofWorkSt_AfterUpdate()
    Dim CWE As Boolean
    
    CWE = DLookup("[WEMW]", "TblWeeks", "[Date] = #" & Format([Forms]![frmInitial]![DateofWorkSt], "DD/MM/YYYY") & "#")
    
    Debug.Print CWE
    
    If CWE = True Then
    [Forms]![frmInitial]![chk2] = True
    [Forms]![frmInitial]![chk3] = False
    Else
    [Forms]![frmInitial]![chk3] = True
    [Forms]![frmInitial]![chk2] = False
    End If
    
    [txtweek] = DLookup("[PlanWeek]", "TblWeeks", "[date] = #" & Format([Forms]![frmInitial]![DateofWorkSt], "DD/MM/YYYY") & "#")
    [txtDay] = DLookup("[Day]", "TblWeeks", "[date] = #" & Format([Forms]![frmInitial]![DateofWorkSt], "DD/MM/YYYY") & "#")
    
    
    End Sub

    ok Trekky what are you not telling me? in my tblWeeks design i have the format as short date and it "looks" fine... where am i going wrong?


    aboo
    Last edited by aboo; 05-04-09 at 04:41.

  15. #15
    Join Date
    May 2006
    Posts
    178
    ive done it!!!

    cryptic clue! Thaks Trekky...

    Code:
    CWE = DLookup("[WEMW]", "TblWeeks", "[Date] = #" & Format([Forms]![frmInitial]![DateofWorkSt], "dd-mmm-yyyy") & "#")
    Healdem, after you making me feel more of an amateur than i am, Tell me more about the table design and how i should go about it.

    The week numbers in a year are variable.
    the system needs to plan jobs aound 3 years ahead or even more. reference to the financial week aswell as a planning week have to be made.
    looking at my table how should i re design it? or should i scrap it and just use functions?

    If i use functions i think im going to need your help on how i will extract the week number.

    im all ears!


    Thanks again guys.. expecially for giving me cryptic clues!!!!!!!!! it just gave me greater satisfaction at working it out!


    aboo

Posting Permissions

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