SQL Expression to select one of two fields in same table?

[Glenn_UK]

In my table I have two date fields (accomStart and opStart).

In a select query I need to fill an expression field with the earliest of those two date fields.

Could anybody give some guidance on how to do this, please?

(I do also have 2 matching Enddate fields but am presuming any solution to the earliest StartDates could be adapted for the latest EndDates)?

my thanks

 

[MSAccessRookie]

I am not sure why you have two of each type, but you are correct about the fact that the solution for one is probably the solution for the other. In a standard SELECT Query, and IIf() Statement will probably do what you want. The format is as follows:

IIf({Test to Perform}, {Value if TRUE}, {Value if FALSE}

In your case, this would be something like:

IIf(accomStart <= opStart, accomStart, opStart)

I have assumned that if they were the same it did not matter, and chose the fiirst one.

 

[Glenn_UK]

That's grand of you to help out, and so speedily, thanks very much.

Simple perhaps, once it's pointed out... But, as a struggling novice, I do find it difficult to pull together all the strands of half-learnt, part-comprehended bits and pieces of SQL and VBA. IIf has been one such for me. Maybe now, applying it to something so specific, it will have sunk in!

Re your wondering: The fields are not duplicates... there's two aspects to a schedule - accommodation, and activity, and a separate beginning and ending date for each.
The 2 expression fields are for a Union Query where other tables might have no Accommodation element so only one Start and Finish date.
Your querying it did make think I might have been over-complicating, but I don't think so.
My thanks again for your help