Query that finds the next upcoming date among multiple date fields

[Jacqoli4]

Good Day,

I am quite new to access, and hoping for some assistance from anyone willing to help.

I have two tables, Table A contains registration information and dates whilst Table B contains general information about welders that are being registered, both tables have a common unique field relating one to the other(Stamp #).

(Table A) contains multiple dates: Registration Date,Renewal 1,Renewal 2,Renewal 3 and Expiry Date(Re registration).These dates are in 6 month intervals.

(Table B) contains a text-box on which I wish to display the next upcoming date from the dates in Table A, regardless of the date field to which it belongs.

I would be extremely grateful to anyone who could offer some advice.
Thank you!

[CJ_London]

The implication from your data structure is that a registration only has a life of 2 years - is this correct?

basically, it looks like your table design is wrong - you need to investigate normalisation. You have an excel style of storing data (short and wide), not a db style (tall and narrow). Assuming the 2 years is correct then as far as I can see you only need the registration date - the rest is covered by rules, e.g. registrationdate+6months etc

Because of your structure, you will need a complex formula as follows

NextDate=switch(renewal1>date(),renewal1,renewal2> date(),renewal2,renewal3>date(),renewal3,expirydat e>date(),expirydate)

[Jacqoli4]

Thank you so much man it works perfectly, another question, how do I get the data returned by the query you've just helped me with to display in a text-box in a form?

[CJ_London]

put the calculation in the recordsource to your form then bind it to your textbox

[Jacqoli4]

Thanks a lot, you've been very helpful!