Query criteriea for even/odd numbers

[apartain]

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

Any help is appreciated.

Thanks,
ang

[Rabbit]

Well... what are these numbers and what are these conditions?

As a general answer, you can use If (Number Mod 2) = 1 Then/Else.

[apartain]

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.

[ADezii]

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:

Select Case Hourly Status
  Case "S"
      If Week Mod 2 = 0 Then       'even week
         'show Records for this & last week
      Else       'week is odd
         'show Records for this week only
      End If
  Case Else     'not S
      If Week Mod 2 = <> 0 Then       'odd week
         'show Records for this & last week
      Else      'even week
         'show Records for this week only
      End If
End Select

[Rabbit]

I almost have it working except for one problem:

SELECT Table1.[Hourly Status], Table1.Name, Table1.WeekWork
FROM Table1
WHERE (((Table1.[Hourly Status])="S") AND ((Table1.WeekWork)=IIf(DatePart("ww",Date()) Mod 2=1,DatePart("ww",Date()),"Value if False"))) 
OR (((Table1.[Hourly Status])<>"S") AND ((Table1.WeekWork)=IIf(DatePart("ww",Date()) Mod 2=1,DatePart("ww",Date()),"Value if False")));
 

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.

[apartain]

I almost have it working except for one problem:

SELECT Table1.[Hourly Status], Table1.Name, Table1.WeekWork
FROM Table1
WHERE (((Table1.[Hourly Status])="S") AND ((Table1.WeekWork)=IIf(DatePart("ww",Date()) Mod 2=1,DatePart("ww",Date()),"Value if False"))) 
OR (((Table1.[Hourly Status])<>"S") AND ((Table1.WeekWork)=IIf(DatePart("ww",Date()) Mod 2=1,DatePart("ww",Date()),"Value if False")));
 

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

[Rabbit]

Well, either way you have it working. Let us know if you have any more questions.