Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Formulae help (Access2000)

    Hi guys!
    What I

  2. #2
    New Lounger
    Join Date
    Nov 2001
    Location
    Hokitika, West Coast, New Zealand
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Formulae help (Access2000)

    I haven't worked out the actual code needed, but my suggestion would be to create a new function in a module.

    Either use Select Case or If..Then..Else construction to customise the result depending on what day of the week your date represents.

    I am sure others will have other ideas but hope this helps a little.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Formulae help (Access2000)

    Try this switch instead:

    Switch([Wk]=4 and [Diff]=4,"SPOT, [wk]=5 and [Diff]=5, "SPOT",[Wk]<=3 and [Diff]>5,"FWD)

    You don't need separate Switch calls for each expressions and there's no point I see in using both Switch and Iif. Switch simply evaluates the expressions and returns the first one that evaulates to true. I also changed your DateDiff to Diff in this expression for clarity. DateDiff is the name of a built-in function and it is never a good idea to use anything like that as a field or expression name.

    Why are you using an NZ function in your expression? Is it possible for [settles]-<td> to return a Null or for [wk] to be Null? If so, I think you have a larger problem.
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Formulae help (Access2000)

    Hi Charlotte,
    Thanx for the reply.
    You forgot one thing which is that If Wk is between 3-5 & Diff is between 3-5 then it " "
    so there'll be 3 conditions and thats why I have had problems with the formula in the first place.
    SF: Switch([Wk]=4 And [Diff]=4,"SPOT",[wk]=5 And [Diff]=5,"SPOT",[Wk]>=3 And [Diff]>5,"FWD")
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Query Formulae help (Access2000)

    Hi,
    Try using the following to calculate the weekdays between the two dates:
    WdaysBetween: datediff("ww",[ td ],[settles])*5+weekday([settles])-weekday([ td ])
    You can then use your original SWITCH function to determine whether it's SPOT or FWD.
    (Note: I've put spaces in the td field as the Lounge doesn't like that name!)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Formulae help (Access2000)

    Oh THANX A LOT Rory!
    That works exactly how I wanted it to work.
    <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15> <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

Posting Permissions

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