date on a complex 34 row query

[Krazy Darcy]

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.

 

[Smartin]

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.

Perhaps

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

 

[Krazy Darcy]

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.

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.

Perhaps

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

 

[Krazy Darcy]

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.

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.

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.

Perhaps

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

 

[Smartin]

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
"Krazy Darcy" wrote:

Glad you got it working.