Check to seee if date between last saturday of March and last saturday of October

[rustynails]

I'm trying to create a query to take Winter time in Europe into consideration. Based on the dates, it will add 2 hours (if summer) or 1 hour (if winter) to time field.



I have a code to find the first Saturday of March

DateAdd("d",DatePart("w",DateSerial(Year(Flight_Da te),"3",0),2),DateSerial(Year(Flight_Date),"3",0))

How do I change it to find last Saturday of March? How do I say check dates between last sat of march and last sat of October? something like

iif ([flight_date] between last sat of march and last sat of october, 1, 0)

Thanks.

[ssanfu]

Your formula (not code) didn't work consistently. It was correct for 2012, bu not for 2011 or 2013.

Here are formulas for the last Saturdays in March and October:

last sat of march
=DateAdd("d",7-Weekday(DateSerial(Year([Flight_Date]),4,1)),DateSerial(Year([Flight_Date]),4,1))

last sat of october
=DateAdd("d",7-Weekday(DateSerial(Year([Flight_Date]),11,1)),DateSerial(Year([Flight_Date]),11,1))

I created a form and query - easier than trying to describe what to do.
See attached dB (A2000 format)

[rustynails]

Your formula (not code) didn't work consistently. It was correct for 2012, bu not for 2011 or 2013.

Here are formulas for the last Saturdays in March and October:

last sat of march
=DateAdd("d",7-Weekday(DateSerial(Year([Flight_Date]),4,1)),DateSerial(Year([Flight_Date]),4,1))

last sat of october
=DateAdd("d",7-Weekday(DateSerial(Year([Flight_Date]),11,1)),DateSerial(Year([Flight_Date]),11,1))

I created a form and query - easier than trying to describe what to do.
See attached dB (A2000 format)

Wow this is awesome. You went one step ahead and created a form to test the dates!! Thanks!

But I noticed that the months in your query are looking at first saturdays in April and November. So If I replaced 4 with 3 and 11 with 10 (in query), it works perfectly. How do I incorporate this change in the form?

[ssanfu]

My apologies!!
I had created so many formulas in the query and a text file, I ended up deleting the correct formulas and kept the wrong formulas.
The correct formulas are:

last sat of march
=DateAdd("d",-Weekday(DateSerial(Year([Flight_Date]),4,1)),DateSerial(Year([Flight_Date]),4,1))

last sat of october
=DateAdd("d",-Weekday(DateSerial(Year([Flight_Date]),11,1)),DateSerial(Year([Flight_Date]),11,1))

So If I replaced 4 with 3 and 11 with 10 (in query)

The 4 and 11 are the correct months.
Attached it a corrected db (still A2000 format)

How do I incorporate this change in the form?

Not sure if I understand what you are asking?

This is how you would change the time:

EuropeTime: DateAdd("h",IIf([flight_date] Between [LastMarch] And [LastOctober],2,1),[TimeTestTime])

I don't know what your table look like or where/how the time was stored, so I create a time field.

Is this what you are asking??

[rustynails]

My apologies!!
I had created so many formulas in the query and a text file, I ended up deleting the correct formulas and kept the wrong formulas.
The correct formulas are:

last sat of march
=DateAdd("d",-Weekday(DateSerial(Year([Flight_Date]),4,1)),DateSerial(Year([Flight_Date]),4,1))

last sat of october
=DateAdd("d",-Weekday(DateSerial(Year([Flight_Date]),11,1)),DateSerial(Year([Flight_Date]),11,1))

The 4 and 11 are the correct months.
Attached it a corrected db (still A2000 format)



Not sure if I understand what you are asking?

This is how you would change the time:

EuropeTime: DateAdd("h",IIf([flight_date] Between [LastMarch] And [LastOctober],2,1),[TimeTestTime])

I don't know what your table look like or where/how the time was stored, so I create a time field.

Is this what you are asking??

Hi ssanfu

Your new fix is correct and brings it to march and october, which is what I was trying to fix in the form

Thanks!!!!!!

[ssanfu]

You are welcome. Sorry for my rectal - cranial inversion.