date on a complex 34 row query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Krazy Darcy - 23 Feb 2007 01:25 GMT

I have a complex search query that works of several input fields on a form.

The criteria was:
[Forms]![artsearchkeyword]![yearsearch]

I was provided with code that can take a user specified year and filter
through the date field earlyer today (nzdst)

BETWEEN DateSerial([What Year?], 1, 1) AND DateSerial([What Year?], 12, 31)

It works on most other forms but not this one.

Could someone "rewrite" it to take it's input from a input field on a form.
form = artsearchkeyword
control name = yearsearch

This is a fairly conmplex query that has 34 rows of criteria as it tests for
all possible combinations of use of the input fields as a user may only fill
as few as one depending on what/how they want to search. All other fields
work ok.

currently the query is broked because of this.
(I have been removing the year field from queries/forms and migrating to
using the date field instead of having both) It did work before removing the
year field from the query.

Thanks for your time.

Reply to this Message

Smartin - 23 Feb 2007 01:50 GMT

> I have a complex search query that works of several input fields on a form.
>
[quoted text clipped - 23 lines]
>
> Thanks for your time.

Perhaps

WHERE
Year(nzdst) = Year([Forms]![artsearchkeyword]![yearsearch])

Signature

Smartin

Reply to this Message

Krazy Darcy - 24 Feb 2007 06:52 GMT

I put that in the criteria field for the datecreated column without the
(nzdst) as the database doesn't care if it is daylight saving or not.

It didn't work.

I spent a few more hours trying to get the query to work and ended up making
a real mess of the query. Had to restore from a backup file (mage the day
before) due to the complexity of this query 9this problem is on only part of
a multi-field search form).

> > I have a complex search query that works of several input fields on a form.
> >
[quoted text clipped - 28 lines]
> WHERE
> Year(nzdst) = Year([Forms]![artsearchkeyword]![yearsearch])

Reply to this Message

Krazy Darcy - 24 Feb 2007 07:39 GMT

I got it working now

year([datecreated])=[Forms]![artsearchkeyword]![yearsearch]

where datecreated is the field name
[Forms]![artsearchkeyword]![yearsearch] is the "path" to the input form text
box

I simply remaned the column datecreated from year and replaced each
occurance of [Forms]![artsearchkeyword]![yearsearch] with
year([datecreated])=[Forms]![artsearchkeyword]![yearsearch]
I didn't have to touch the Is Null column

For some reason the query only works when run from the form but not directly
(when run directly I get asked for the values but it returns zero results) -
not the end of the world

> I put that in the criteria field for the datecreated column without the
> (nzdst) as the database doesn't care if it is daylight saving or not.
[quoted text clipped - 40 lines]
> > WHERE
> > Year(nzdst) = Year([Forms]![artsearchkeyword]![yearsearch])

Reply to this Message

Smartin - 28 Feb 2007 23:59 GMT

> I got it working now
>
[quoted text clipped - 12 lines]
> (when run directly I get asked for the values but it returns zero results) -
> not the end of the world

Glad you got it working.

Signature

Smartin

Reply to this Message