query criteria

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Rob (Rob_at_discussions.microsoft.com)
Date: 02/17/05 

Date: Thu, 17 Feb 2005 14:13:05 -0800

I'm using a query based on an imported table which uses two fields for date.
I do not control and cannot change the data format of the table before it
comes to me from another source.
One field [entrymo] uses the numerical value for the month 1=Jan and the
other [entryyr] uses the 4 digit year. The data spans more than one year.
I am trying to build a criteria which allows users to enter a range of
dates, but am not sure how to format it in a way which makes sense to a user.
If I use a "Between this and that".....criteria for each field I run into
trouble when searching between years. Trying to search between 1 2004 and 1
2005, I returned only records from 1 2004 and 1 2005, not the range.
I realize it is because the criteria is based on two different fields. In
effect I was asking the query to return [entrymo] records between 1 and 1 AND
[entryyr] records between 2004 and 2005.
This will become a real problem as data is populated for 2005. Any ideas
how to restructure the criteria?
I hope this is clear.

Relevant Pages

  • Re: Finding records by free text search
    ... Since you are searching for the string ... I have a query containing three fields: ... I have a form called frmSearch which contains the same three fields plus a ... The 'and' query criteria are ...
    (microsoft.public.access.queries)
  • Re: Create Form Run Query Display in Form
    ... query based on that form with a command button. ... The command button is linked to open the second form. ... searching, but I could not type in anything to search for. ... > on the Criteria line for each field that you want to search. ...
    (microsoft.public.access.forms)
  • Loop problem when time period loops from 1 to end of period again
    ... I have a dataset which I am searching by two fields, ... My goal is to write a query which will ... this query is generated in VBA given criteria from ... VBA, ...
    (microsoft.public.access.queries)
  • Re: OnClick Event Procedure
    ... I have the form based on a query, and the MCode field is what they ... but it doesn't run my criteria from my ... >>I have a similar form (for searching) and I added a new ...
    (microsoft.public.access.formscoding)
  • Re: query criteria
    ... DateSerial function to convert the and fields to one ... I then added my "between statement" to the criteria of column. ... However, when I ran the query, it still returned the same results as before. ... >> I do not control and cannot change the data format of the table before it ...
    (microsoft.public.access.queries)