Re: Date Range filtering trouble

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Jon Spivey (jons_at_mvps.org)
Date: 06/22/04 

Date: Wed, 23 Jun 2004 00:58:52 +0100

Hi,
The query you want is just

SELECT *
FROM Table
WHERE Publish_Date BETWEEN #::Date1::# AND #::Date2::#

Having said that there's a lot of problems that can arise from date querys -
for example to a UK user 1/6/2004 would be 1 June to a US user ir would be 6
Jan. If you have an international user base I'd suggest using ISO format for
dates, eg today is 2004-06-23 and presenting drop downs for day month and
year which you could combine into a date. 

--
Cheers,
Jon
Microsoft MVP - FP
"Thanasis (sch)" <thanasis_gr@hotmail.com> wrote in message
news:#M8oFPJWEHA.2908@TK2MSFTNGP10.phx.gbl...
> Hello to everyone,
>
> i am going to built a database application with DataBase Interface Wizard
> and FrontPage 2003.My DataBase will be Access 2000.
> Inside my database (mdb file) there is a table whose name is NEWS.A field
of
> the table is called PUBLISH_DATE(type DATE).
>
> I want the user of my application to provide 2 dates, in order to filter
the
> records according to values of the PUBLISH_DATE field.
> i have followed the steps below:
> 1) i have created a search form with 2 text boxes with names DATE1 and
> DATE2.I save the form as asp page SEARCH.ASP.The method parameter is POST
> and the action  is RESULTS.ASP.
>
> 2)I have created a RESULTS.ASP page which actually  filters the records
> according to the user input (from DATE1 to DATE2).
> I click Insert-->DataBase-->Results-->I choose my database and i click on
> Next-->then i click on Custom Query (Edit) and i type
> SELECT * FROM NEWS WHERE (PUBLISH_DATE>=  date1 AND PUBLISH_DATE<=
date2).
> I click next..next and the wizard is closed.
> Also i tried something like that:
> SELECT * FROM NEWS WHERE (PUBLISH_DATE>=  request.form("date1" AND
> PUBLISH_DATE<=  request.form("date2").
>
> However i got an error message from the Wizard.I didn't get the filtering
i
> expected.
>
> How this can be done with SQL or DIW?
> Wishes
> Thanasis
>
>

Relevant Pages

  • Re: maximum records in form view
    ... Form view are different formats for the user interface; ... in tables that you can open from the database window (the box that shows up ... query that doesn't return all the rows or it has a filter applied. ... you get all expected rows then the form must have a filter aplied to it. ...
    (microsoft.public.access.gettingstarted)
  • Re: How can i set up data to auto transfer to a different table up
    ... Then, any form, report, or query would have the criteria = false ... You never want to build a database that requires you to go in and clean ... > archiving, how do you get around having to go through 8000 records to view ... > I setup a filter or allowed the menubar filter to be present, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Want help with database diagnostics tool design.
    ... Currently I'm writing a database diagnostics application using Delphi 5 ... this problem is to let each of the filters represent a specific query ... specified by the filter. ...
    (comp.lang.pascal.delphi.databases)
  • Re: MailMerge and Access
    ... Click on the Query Options button and then use the Filter tab to set up a ... > I have the Merge Document with all needed fields getting ... > my Access 2000 database. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Sorting, filtering, wrapping
    ... The query will be more efficient, though, ... In a database, scanning all the records in a table to find the ones that ... and is used as the record source for an Employees form. ... >>> (filter by query vs. filter by code) in a situation like this? ...
    (microsoft.public.access.formscoding)