Using Date Ranges in a table for a query

[Mark44stfc]

I am currently building a reporting database. The data that is in there will be global but time stamps on the reporting data itself is GMT.

I therefore have to convert, also bearing in mind that clock changes happen at different times I have created a Table which is as follows

Country|Start Date|End Date|OffsetFromGMT

USA|13/03/2016 06:00 | 06/11/2016 07:00 | -04:00
USA|06/11/2016 07:00 | 12/03/2017 06:00 | -05:00

I can then build this table out for 10 years to futureproof my date time conversion

My question, is how to use this type of data in a query to convert a column? or is there a better way of achieving this?

Any heklp is greatly appreciated....

 

[ranman256]

Use a query.
the query applies the GMT to both time so both fields read as GMT.
Use that query as the basis of all date queries.

DateAdd("h",[OffsetGmt],[StartDate])

 

[Mark44stfc]

Use a query.
the query applies the GMT to both time so both fields read as GMT.
Use that query as the basis of all date queries.

DateAdd("h",[OffsetGmt],[StartDate])

Thanks for this, I have looked up the DateAdd function and that definiteely seems to be the way but I cannot seem to get it to work.

My conversion table has various differing conversions dependant on the date. The full table is below.

How do i then convert whereby the query will look at where the current OrgUK date sits in this table and add or subtract as appropriate

ParentOrgName	Start Date	End Date	OffsetFromVerintGMT
Org UK	01/01/2016 00:00	27/03/2016 01:00	00:00
Org UK	27/03/2016 01:00	30/10/2016 02:00	+01:00
Org UK	30/10/2016 02:00	26/03/2017 01:00	00:00
Org UK	26/03/2017 01:00	29/10/2017 02:00	+01:00
Org UK	29/10/2017 02:00	25/03/2018 01:00	00:00
Org UK	25/03/2018 01:00	28/10/2018 02:00	+01:00
Org UK	28/10/2018 02:00	31/03/2019 01:00	00:00
Org UK	31/03/2019 01:00	27/10/2019 02:00	+01:00
Org UK FO	01/01/2016 00:00	27/03/2016 01:00	00:00
Org UK FO	27/03/2016 01:00	30/10/2016 02:00	+01:00
Org UK FO	30/10/2016 02:00	26/03/2017 01:00	00:00
Org UK FO	26/03/2017 01:00	29/10/2017 02:00	+01:00
Org UK FO	29/10/2017 02:00	25/03/2018 01:00	00:00
Org UK FO	25/03/2018 01:00	28/10/2018 02:00	+01:00
Org UK FO	28/10/2018 02:00	31/03/2019 01:00	00:00
Org UK FO	31/03/2019 01:00	27/10/2019 02:00	+01:00
Org USA	01/01/2016 00:00	13/03/2016 06:00	-05:00
Org USA	13/03/2016 06:00	06/11/2016 07:00	-04:00
Org USA	06/11/2016 07:00	12/03/2017 06:00	-05:00
Org USA	12/03/2017 06:00	05/11/2017 07:00	-04:00
Org USA	05/11/2017 07:00	11/03/2018 06:00	-05:00
Org USA	11/03/2018 06:00	04/11/2018 07:00	-04:00
Org USA	04/11/2018 07:00	10/03/2019 06:00	-05:00
Org USA	10/03/2019 06:00	03/11/2019 07:00	-04:00
Org USA FO	01/01/2016 00:00	13/03/2016 06:00	-05:00
Org USA FO	13/03/2016 06:00	06/11/2016 07:00	-04:00
Org USA FO	06/11/2016 07:00	12/03/2017 06:00	-05:00
Org USA FO	12/03/2017 06:00	05/11/2017 07:00	-04:00
Org USA FO	05/11/2017 07:00	11/03/2018 06:00	-05:00
Org USA FO	11/03/2018 06:00	04/11/2018 07:00	-04:00
Org USA FO	04/11/2018 07:00	10/03/2019 06:00	-05:00
Org USA FO	10/03/2019 06:00	03/11/2019 07:00	-04:00

<colgroup><col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;" span="2"> <col width="147" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5376;"> <tbody> </tbody>