Need Help Referencing a Year

  • Thread starter Thread starter JessiRight77
  • Start date Start date
J

JessiRight77

I need to pull all "year-to-date" values for a product, and I am having
a hard time composing the criteria for the query.

The "year" that I need to reference is stored in another query column
called YearProd, which uses the DatePart command to extract the year
from a date range entered by me on a search criteria dialog box. When
I run the query, YearProd correctly displays the year of whatever date
I typed in the dialog box.

Now, I want to add the values of all products made for the year of a
date that was entered in the search criteria dialog box.

This is what I originally entered as the criteria for fldDate:

Between 1/1/YearProd And Now()

But after I tab out of the criteria field, it translates it into the
following (because 2005 happened to be the current value of YearProd):

Between #1/1/05#/"YearProd" And Now()

Then when I run the query, I get an error message that says my query is
either typed incorrectly or too complex.

Am I using the wrong syntax, or can this even be done? Does anyone
know how to create an expression for a date field in a query that
references the year that is stored in another column of that same
query?

Thanks,
Jessi
 
I need to pull all "year-to-date" values for a product, and I am having
a hard time composing the criteria for the query.

The "year" that I need to reference is stored in another query column
called YearProd, which uses the DatePart command to extract the year
from a date range entered by me on a search criteria dialog box. When
I run the query, YearProd correctly displays the year of whatever date
I typed in the dialog box.
Click to expand...

Use the DateSerial function:

BETWEEN DateSerial(Year([parameter]), 1, 1) AND [parameter]

assuming that [parameter] is a date/time paramter specifying the
desired "to-date".

John W. Vinson[MVP]
 
Well... I spoke too soon.

The DateSerial function worked perfectly, but now the "YearProd" query
expression that it is based on has started asking for the year in a
separate "Enter Parameter Value" box (instead of automatically
extracting the year from the date I type in a search criteria form.

This is the expression I was using in the query to extract the year
from the date that I typed in a text box on a search criteria form:

YearProd: DatePart("yyyy",[Forms]![frmQryInventory]![txtStartDate])

Do you know what might have happened to cause YearProd to stop
extracting the year from the date in the text box?

Jessi
 
While trying to fix this, I made it worse. Since I began having
problems referencing another column in the query, I decided to extract
the year into a text box ([YearProd]) on the form that I use to enter
the search criteria (frmInventorySearchDialogBox).... so it would
already be available for my query.

My new "YearProd" text box on the form has this for the control source:
=DatePart("yyyy",[txtStartDate])
I left the format property blank because it displayed the entire date
if I formatted it with one of the built-in date formats.

On my query, I then entered the following as the criteria for the field
that needs a date range:
Between
DateSerial(Year([Forms]![frmInventorySearchDialogBox]![YearProd]),1,1)
And Date()

However, the DateSerial function no longer works... it still asks me to
"Enter Parameter Value" for YearProd, and when I do, it returns ALL
values instead of the values for those products between the date range
entered on frmInventorySearchDialogBox.

So sorry... Do you know where I screwed this up?

Thanks,
Jessi
 
Well... I spoke too soon.

The DateSerial function worked perfectly, but now the "YearProd" query
expression that it is based on has started asking for the year in a
separate "Enter Parameter Value" box (instead of automatically
extracting the year from the date I type in a search criteria form.

This is the expression I was using in the query to extract the year
from the date that I typed in a text box on a search criteria form:

YearProd: DatePart("yyyy",[Forms]![frmQryInventory]![txtStartDate])

Do you know what might have happened to cause YearProd to stop
extracting the year from the date in the text box?

Jessi
Click to expand...

You (usually) can't reuse a calculated field in a further calculation.
Just leave YearProd out altogether. Instead, use

DateSerial(Year([Forms]![frmQryInventory]![txtStartDate], 1, 1)

Of course frmQryInventory must be open for this to work.

John W. Vinson[MVP]
 
Back
Top