frustrationmultiplied February 2016

I need a formula that finds a departure date between two given dates where the departure occurs later than a given time

Im using Access 365 2016.

I need a formula that finds a departure date between two given dates where the departure occurs later than a given time.

I have tried this formula but it doesnt work:

Between [date1] And [date2] Where (TimeValue([Date1] > 09:30

Thank you

The formula i quoted above is a simpler way of writing the formula that references controls on my form.

The formula below references controls in my Flights form and is the exact formula that I used in the expression builder.

Between [Forms]![Flights]![cboDeparture]

    And

    [Forms]![Flights]![cboDeparture2]

    And

    TimeValue([Forms]![Flights]![cboDeparture])>[Forms]![Flights]![cboGreaterThanTime])

I altered the formula according to the code provided by Gustav in the comments section but neither code worked. See comments. This is the code that I used for Method 1:

    (Between [Forms]![Flights]![cboDeparture] 
And [Forms]![Flights]![cboDeparture2]) 
And (TimeValue([Forms]![Flights]![cboDeparture])>[Forms]![Flights]![cboGreaterThanTi‌​me])

This is the code that I used for Method 2:

    Between (DateValue([Forms]![Flights]![cboDeparture])+#09:30:00#) 
And [Forms]![Flights]![cboDeparture2]

Answers


Gustav February 2016

Try with:

(Between [Date1] And [Date2]) And (TimeValue([Date1]) > #09:30#)

or:

Between (DateValue([Date1]) + #09:30#) And [Date2]

With comboboxes perhaps:

Between DateValue([Forms]![Flights]![cboDeparture]) And DateValue([Forms]![Flights]![cboDeparture2])
And
TimeValue([Forms]![Flights]![cboDeparture]) > TimeValue([Forms]![Flights]![cboGreaterThanTime])

Post Status

Asked in February 2016
Viewed 2,397 times
Voted 10
Answered 1 times

Search




Leave an answer