469,267 Members | 1,031 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

Query criteriea for even/odd numbers

I need the syntax for limiting query results to either even or odd numbers, depending on certain conditions.

Any help is appreciated.

Thanks,
ang
Feb 22 '07 #1
6 21537
Rabbit
12,516 Expert Mod 8TB
Well... what are these numbers and what are these conditions?

As a general answer, you can use If (Number Mod 2) = 1 Then/Else.
Feb 22 '07 #2
Well... what are these numbers and what are these conditions?

As a general answer, you can use If (Number Mod 2) = 1 Then/Else.
I have used DatePart to number the weeks of the year. This is in a field named Week. The other field used is Hourly Status.

If a person is a Salaried employee, their pay period ending is an even week. If hourly, an odd week. In non-code terms:

=If Hourly Status = "S" and if the current week is even, show records for this week and last week. But if Hourly Status = "S" and the current week is odd, show records for this week only.

The opposite for all other entries into the Hourly Status field:

=If Hourly Status IsNot "S" and if the current week is odd, show records for this week and last week. But if Hourly Status IsNot "S" and the current week is even, show records for this week only.


Can this all be done with one conditional statement?

Thanks.
Feb 22 '07 #3
ADezii
8,800 Expert 8TB
I have used DatePart to number the weeks of the year. This is in a field named Week. The other field used is Hourly Status.

If a person is a Salaried employee, their pay period ending is an even week. If hourly, an odd week. In non-code terms:

=If Hourly Status = "S" and if the current week is even, show records for this week and last week. But if Hourly Status = "S" and the current week is odd, show records for this week only.

The opposite for all other entries into the Hourly Status field:

=If Hourly Status IsNot "S" and if the current week is odd, show records for this week and last week. But if Hourly Status IsNot "S" and the current week is even, show records for this week only.


Can this all be done with one conditional statement?

Thanks.
I think it is asking a little too much for a single conditional statement, try this logic:
Expand|Select|Wrap|Line Numbers
  1. Select Case Hourly Status
  2.   Case "S"
  3.       If Week Mod 2 = 0 Then       'even week
  4.          'show Records for this & last week
  5.       Else       'week is odd
  6.          'show Records for this week only
  7.       End If
  8.   Case Else     'not S
  9.       If Week Mod 2 = <> 0 Then       'odd week
  10.          'show Records for this & last week
  11.       Else      'even week
  12.          'show Records for this week only
  13.       End If
  14. End Select
Feb 22 '07 #4
Rabbit
12,516 Expert Mod 8TB
I almost have it working except for one problem:
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.[Hourly Status], Table1.Name, Table1.WeekWork
  2. FROM Table1
  3. WHERE (((Table1.[Hourly Status])="S") AND ((Table1.WeekWork)=IIf(DatePart("ww",Date()) Mod 2=1,DatePart("ww",Date()),"Value if False"))) 
  4. OR (((Table1.[Hourly Status])<>"S") AND ((Table1.WeekWork)=IIf(DatePart("ww",Date()) Mod 2=1,DatePart("ww",Date()),"Value if False")));
  5.  
The problem is what to put in Value if False.
I've tried "Between (DatePart("ww",Date()) - 1) And DatePart("ww",Date())
I've tried ">= DatePart("ww",Date())-1)"
But nothing works because it see's it as a string rather than as a conditional statement.
Feb 22 '07 #5
I almost have it working except for one problem:
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.[Hourly Status], Table1.Name, Table1.WeekWork
  2. FROM Table1
  3. WHERE (((Table1.[Hourly Status])="S") AND ((Table1.WeekWork)=IIf(DatePart("ww",Date()) Mod 2=1,DatePart("ww",Date()),"Value if False"))) 
  4. OR (((Table1.[Hourly Status])<>"S") AND ((Table1.WeekWork)=IIf(DatePart("ww",Date()) Mod 2=1,DatePart("ww",Date()),"Value if False")));
  5.  
The problem is what to put in Value if False.
I've tried "Between (DatePart("ww",Date()) - 1) And DatePart("ww",Date())
I've tried ">= DatePart("ww",Date())-1)"
But nothing works because it see's it as a string rather than as a conditional statement.
I ended up doing it this way:

I created a new table named WkNo with two fields WeekNumber and Status. I then populated the table with week numbers from 1 to 52 for the year, and the status is either even or odd, manually input by me.

I then used a DLookUp function in a form to look up the record from the WkNo table where the WeekNumber equals the DatePart of the current week, returning the even or odd in the Status field.

It's a work-around for sure. I wish I knew code better so I wouldn't have to do things like this. If I knew conditional statements better, I considered using If/OR kind of thing to say if wk=2 or 4 or 6 or 8...,even, odd. but I didn't know how that syntax looks.

Thanks anyway for all of your help.
ang
Feb 23 '07 #6
Rabbit
12,516 Expert Mod 8TB
Well, either way you have it working. Let us know if you have any more questions.
Feb 23 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Jeff Uchtman | last post: by
96 posts views Thread by Karen Hill | last post: by
1 post views Thread by Warren Thai | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.