Filtering problem

[Jean-Paul]

I have a continuous form.
In the filter-propertie I wrote:

=Uren.Datum > #" & Format(Forms![Uur_overzicht]!Start_dat,'DD/MM/YYYY')
& "# And Uren.Datum < #" &
Format(Forms![Uur_overzicht]!Stop_dat,'DD/MM/YYYY') & "#

Allow filter is set to True

I don't have the feeling there is no filtering at all when I open the form.

Whe I click the filter-icon I get an error saying there is an operator
missing in the query-expression.

How come ther is no automatic filtering?
What do I learn form the errormessage?

Thanks

 

[Douglas J. Steele]

You cannot use dd/mm/yyyy in a filter or a query (well, you can, but it
won't work correctly for the first twelve days of each month). Change your
format to mm/dd/yyyy or yyyy-mm-dd.

For more details, see Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I had in my September 2003 Access
Answers column for Pinnacle Publication's "Smart Access" newsletter. (The
column and accompanying database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[Jean-Paul]

But in my table dates are stored as dd/mm/yyyy
So what to do?
JP

 

[Douglas J. Steele]

Your dates are NOT "stored as dd/mm/yyyy". You may choose to display the
dates in dd/mm/yyyy format, but that has no bearing on how they're stored.

Dates are eight byte floating point numbers, where the integer portion
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal portion represents the time as a fraction of a day.

Read the two references which I cited.

 

[Jean-Paul]

I changed the line in filter-propertie to:
=Uren.Datum > #" & Format(Forms![Uur_overzicht]!Start_dat,'yyyy/mm/dd')
& "# And Uren.Datum < #" &
Format(Forms![Uur_overzicht]!Stop_dat,'yyyy/mm/dd') & "#

When the table is run, All records are displayed.
When I click the filter icon I get the errormessage saying there is an
operator missing in the query-expression.

What do I do to filter upon opening the form
What do I do about the eroormessage

Thank you so much
JP

 

[Douglas J. Steele]

You seem to be missing quotes at the beginning and end of the string.

Try:

="Uren.Datum > " &
Format(Forms![Uur_overzicht]!Start_dat,'\#yyyy\-mm\-dd\#') & " And
Uren.Datum < " & Format(Forms![Uur_overzicht]!Stop_dat,'\#yyyy\-mm\-dd\#)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I changed the line in filter-propertie to:
=Uren.Datum > #" & Format(Forms![Uur_overzicht]!Start_dat,'yyyy/mm/dd') &
"# And Uren.Datum < #" &
Format(Forms![Uur_overzicht]!Stop_dat,'yyyy/mm/dd') & "#

When the table is run, All records are displayed.
When I click the filter icon I get the errormessage saying there is an
operator missing in the query-expression.

What do I do to filter upon opening the form
What do I do about the eroormessage

Thank you so much
JP

Your dates are NOT "stored as dd/mm/yyyy". You may choose to display the
dates in dd/mm/yyyy format, but that has no bearing on how they're
stored.

Dates are eight byte floating point numbers, where the integer portion
represents the date as the number of days relative to 30 Dec, 1899, and
the decimal portion represents the time as a fraction of a day.

Read the two references which I cited.

 

[Jean-Paul]

problem solved... thanks
JP

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.