Specify segment value based on criteria

[DavidMichaelangelo]

Is it possible to create a new field in an Access query that is filled in differently for different lines of criteria?

In the image, you can see that I have two lines of criteria, two segments that I would like identified in the query results.
Is there a way to create a new field in this query called "Segment" that populates with a different value based on which line of criteria returned the record?

[pbaldy]

Looks like the only difference is the date field, so this type of thing:

Segment: IIf(DateField >=..., 1, 2)

[DavidMichaelangelo]

Thanks. But I guess there's no way to specify a value in the interface since I already have the criteria laid out? I was just looking for a shortcut because I will be adding many more lines of criteria and I'm trying to avoid an enormous IIf statement . . .

[Perceptus]

My largest IIF statement so far was nested just over 10 times =\. You could create a custom function in a module to handle.

Function Segment(byval dtInput) as string

select case dtInput
   case #1/1/2015# to #2/1/2015#
     Segment = "Example 1"
   case else
     Segment = "nothing found"
end select
end function

in my experience these kind of functions can be slow, but it might get you out of a massive iif statement.

[ssanfu]

Your question is not very clear. It would help if you provided examples or the data and what the expected results should be.
Explain, in 4-5 sentences what you are trying to do.

Is there a way to create a new field in this query called "Segment" that populates with a different value based on which line of criteria returned the record?

Yes, probably.......
If you looked at the results of the query, what calculations would you do to fill in the "Segment" column? ie - How do you calculate a 'Segment"?

[Perceptus]

I believe the user is trying to avoid IIF(datevalue=#value#,"value found",iif(datevalue>#value#,"value less",iif(datevalue<#value#,"value more","")))

[pbaldy]

I think the goal is clear, I just don't think it can be done without some sort of function. There is no built-in function that will tell you which criteria a given record met to be included.