query design for dates

[Guest]

Hi,
i want to sort a data base for all the dates in a specific month prior to a
certain year.
e.g. all the dates in every augusts prior to 2005

i am using d lookup for creating the start date and end date to sort the
query at the moment. this works well for finding dates in a specific month of
a specific year.

but it does not allow for a specifit month in more than just one year.

suggestions v. welcome

Simon

 

[James A. Fortune]

Hi,
i want to sort a data base for all the dates in a specific month prior to a
certain year.
e.g. all the dates in every augusts prior to 2005

i am using d lookup for creating the start date and end date to sort the
query at the moment. this works well for finding dates in a specific month of
a specific year.

but it does not allow for a specifit month in more than just one year.

suggestions v. welcome

Simon

You can use:

Month([YourDateField]) = 8 AND Year([YourDateField]) < 2005

Note that DLookup only returns the first occurrence.

Use a query to get all occurrences:

SELECT * FROM [YourTableName] WHERE Month([YourDateField]) = 8 AND
Year([YourDateField]) < 2005 ORDER BY [YourDateField];

James A. Fortune
(e-mail address removed)

 

[Guest]

Hi,
i want to sort a data base for all the dates in a specific month prior to a
certain year.
e.g. all the dates in every augusts prior to 2005

i am using d lookup for creating the start date and end date to sort the
query at the moment. this works well for finding dates in a specific month of
a specific year.

but it does not allow for a specifit month in more than just one year.

suggestions v. welcome

Simon

You can use:

Month([YourDateField]) = 8 AND Year([YourDateField]) < 2005

Note that DLookup only returns the first occurrence.

Use a query to get all occurrences:

SELECT * FROM [YourTableName] WHERE Month([YourDateField]) = 8 AND
Year([YourDateField]) < 2005 ORDER BY [YourDateField];

James A. Fortune
(e-mail address removed)

Hi James, many thanks for your help, but,

i have put the following in a query in the criteria for the field which
contains the date i am searching :

SELECT * FROM [FOS reminders] WHERE Month([next test]) = 8 AND Year([next
test]) < 2005 ORDER BY [next test];

but that is not accepted ?? it sugests putting the subquery in parenthesis

any further sugestions, many thanks, Simon

 

[James A. Fortune]

:

Hi,
i want to sort a data base for all the dates in a specific month prior to a
certain year.
e.g. all the dates in every augusts prior to 2005

i am using d lookup for creating the start date and end date to sort the
query at the moment. this works well for finding dates in a specific month of
a specific year.

but it does not allow for a specifit month in more than just one year.

suggestions v. welcome

Simon

You can use:

Month([YourDateField]) = 8 AND Year([YourDateField]) < 2005

Note that DLookup only returns the first occurrence.

Use a query to get all occurrences:

SELECT * FROM [YourTableName] WHERE Month([YourDateField]) = 8 AND
Year([YourDateField]) < 2005 ORDER BY [YourDateField];

James A. Fortune
(e-mail address removed)

Hi James, many thanks for your help, but,

i have put the following in a query in the criteria for the field which
contains the date i am searching :

SELECT * FROM [FOS reminders] WHERE Month([next test]) = 8 AND Year([next
test]) < 2005 ORDER BY [next test];

but that is not accepted ?? it sugests putting the subquery in parenthesis

any further sugestions, many thanks, Simon

It looks like you put the SQL for the entire query into the Criteria:
box for one of the fields. If you are using the Query By Example (QBE)
query designer rather than using SQL View then you should put '8' in as
the criterion for Month([next test]) and '<2005' in as the criterion for
Year([next test]). Choose 'Ascending' for the Sort: on field 'next
test.' After that, the SQL View of the query should be like what I posted.

James A. Fortune
(e-mail address removed)