Using the IF Function to assign an ID to a record.

[AvidMedia]

I am currently trying to find a way to assign an ID to a record depending on
whether or not it lies between certain dates. So for example if the record in
question was opened between 2 dates it would be given the ID 1, and if it
lied between 2 other dates it would be given the ID 2.

The code i have come up with is as follows:

If (Me.Open_date) < (Me.Meeting1) Then
ID=0
ElseIf (Me.Open_date) >= (Me.Meeting1) And (Me.Open_date) < (Me.Meeting2) Then
ID=1
ElseIf (Me.Open_date) >= (Me.Meeting2) And (Me.Open_date) < (Me.Meeting3) Then
ID=2
ElseIf (Me.Open_date) >= (Me.Meeting3) And (Me.Open_date) < (Me.Meeting4) Then
ID=3
Else
ID=4
End If

I have tried using this code in the Default Value of the ID control on both
a Form and the original table with no luck.

Does anyone have any ideas on how i could go about doing this?

The idea is that i can then group the records using this ID, as there seems
to be no way to group the records that lie between certain dates.

 

[Clifford Bass]

Hello,

If you are trying to do this for new records, use the form's Before
Update event and place your code there.

To change existing records create an update query and use the Switch()
function:

update sometable set ID = Switch(Open_date < Meeting1, 0, Open_date >=
Meeting1 and Open_date < Meeting2, 1, Open_date >= Meeting2 and Open_date <
Meeting3, 2, Open_date >= Meeting3 and Open_date < Meeting4, 3, True, 4)

Clifford Bass

 

[papazar]

Hello,

If you are trying to do this for new records, use the form's Before
Update event and place your code there.

To change existing records create an update query and use the Switch()
function:

update sometable set ID = Switch(Open_date < Meeting1, 0, Open_date >=
Meeting1 and Open_date < Meeting2, 1, Open_date >= Meeting2 and Open_date
<
Meeting3, 2, Open_date >= Meeting3 and Open_date < Meeting4, 3, True, 4)

Clifford Bass