Using the IF Function to assign an ID to a record

  • Thread starter Thread starter AvidMedia
  • Start date Start date
A

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.
 
What's the bigger picture that you're trying to accomplish?

If you're wanting to see records between certain dates you can use Query By
Form to select the records. Query By Form is an often used technique where
unbound fields are used to capture the criteria needed to display the
records. The fields can exist on an independent form or on the form
displaying the records itself.

http://support.microsoft.com/default.aspx/kb/209645
 
Thanks for reading...

I'm trying to display records from a database that fit a certain criteria in
a report. The report will display groups of records that were opened between
certain dates.

For Example:

Records open from Meeting held on 01/01/2008
<record 1>
<record 2>

Records open from Meeting held on 01/02/2009
<record 1>
<record 2>

I hope this example makes sense!
 
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.
Click to expand...

Try using the Switch() function in a calculated field. It takes pairs of
arguments, and evaluates them left to right; when it first encounters a pair
with a TRUE for the first value it returns the second:

ID: Switch([Open_Date] < [Meeting1], 0, [Open_Date] < [Meeting2], 1,
[Open_Date] < [Meeting3], 2, [Open_Date] < [Meeting4], 3, True, 4)
 
Thanks for reading...

I'm trying to display records from a database that fit a certain criteriain
a report. The report will display groups of records that were opened between
certain dates.

For Example:

Records open from Meeting held on 01/01/2008
<record 1>
<record 2>

Records open from Meeting held on 01/02/2009
<record 1>
<record 2>

I hope this example makes sense!






- Show quoted text -
Click to expand...

Perhaps a case statement.

Select Case Me.Open_date
Case Is < Me.Meeting1
ID = 0
Case Me.Meeting1 To Me.Meeting2
ID = 1
Case Me.Meeting2 To Me.Meeting3
ID = 2
Case Me.Meeting3 To Me.Meeting4
ID = 3
Case Else
ID = 4
End Select

Hope this helps,
Chris M.
 
Worked great! Thanks John

John W. Vinson said:
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.
Click to expand...

Try using the Switch() function in a calculated field. It takes pairs of
arguments, and evaluates them left to right; when it first encounters a pair
with a TRUE for the first value it returns the second:

ID: Switch([Open_Date] < [Meeting1], 0, [Open_Date] < [Meeting2], 1,
[Open_Date] < [Meeting3], 2, [Open_Date] < [Meeting4], 3, True, 4)
Click to expand...
 
Back
Top