# Thread: Query Formulae help (Access2000)

Hi guys!
What I

2. ## 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. ## Re: Query Formulae help (Access2000)

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.

4. ## Re: Query Formulae help (Access2000)

Hi Charlotte,
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. ## 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.

6. ## 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
•