Open form with date filter

Question

I have 2 forms, frmOne and frmTwo

Both forms have a date field - formated to ShortDate  (dd/mm/yyyy)

[frmOne]![DateOne

and

[frmTwo]![DateTwo]

How can I open frmTwo after update of [frmOne]![DateOne]

I know this sounds like a simple question but I have been trying for hours and just can't get it to work.

If I put #05/06/2013# in the criteria row in the query for frmTwo, it will filter the list.  But as soon as I try and do this when opening the form, there are no records.

Answer 1

Option one: put the following in the criteria of the query, instead of #05/06/2013#:

[Forms]![frmOne]![DateOne]

Option 2: remove the condition from the query, and use the following code to open frmTwo from frmOne:

    DoCmd.OpenForm _

        FormName:="frmTwo", _

        WhereCondition:="DateTwo=#" & Format(Me.DateOne, "yyyy-mm-dd") & "#"

Answer 2

There is a very simple problem here which has nothing to do with the date format (assuming you followed the advice from Hans).

It must be a problem with query one or both the forms are based on.  It can’t be the table as some of the dates work and some don’t.  If you change the format of the date field this would affect all the records – so they would all work or not.

If neither of the answers provided by Hans worked then there is a problem with the query the form(s) are based on.   Check both queries (frmOne andfrmTwo) and see if all the date are in both.  If not there is your problem.

Answer 3

Are you sure you have formatted the value to the ISO standard for date notation of YYYY-MM-DD as Hans described?   A date literal must be in US short date format or an otherwise internationally unambiguous format.  Because you, like me, appear to be using a system whose short date format is set in Windows Control Panel to the format dd/mm/yyyy, a date literal entered in this format will be misinterpreted if it can be interpreted as a valid date in US short date format of mm/dd/yyyy.  This is why it works with a 'day' beyond 12 as in US short date format this is the month, so the value is not a valid date and Access therefore interprets the first two characters as the day.  Formatting the value to the ISO standard caters for whatever the system date format is.

Answer 4

The people who programmed Access lived in the United States - and used the (admittedly irrational) American convention that dates are mm/dd/yy. Therefore a date literal xx/xx/xx or xx/xx/xxxx will be interpreted as month-day-year, REGARDLESS OF YOUR COMPUTER'S DATE SETTING (and regardless of your expectations).

A date entry of 13/10/2014 will be interpreted as 13th of October because there is no thirteenth month, but if the date can be interpreted as m/d/y it will be.

That's why Hans' second suggestion should work. If it doesn't please explain - what was typed into the textbox? What were the relevant properties of the textbox?

Answer 5

Hi Hans

THank you for trying to help.

I have tried both of those (I did this moring also ;-), none of them work.

It only works if the day number is more than 12 (12th, 13th, etc)

Any more ideas please