Combine "ww" and "yyyy"

[Guest]

On a database that I have been given a command button on a form kicks off a
report which has a query that was built with only the week, no year and the
report is now pulling both 2006 and 2007 data. The query field is: Expr1:
Format([Load Date],"ww"); the criteria is:
Format([Forms]![frmMain]![weekreport].[value],"ww"). How do it code the query
to also look at the year field? What is on the form is an Active X control
calendar: MSCAL.Calendar.7. Suggestions? Thanks so much! Jani

 

[BruceM]

The criteria for LoadDate could be:
Year([LoadDate]) = Year(Date()) AND _
DatePart("ww",[LoadDate]) = _
DatePart("ww",[Forms]![frmMain]![weekreport])

Note that the underscores are for convenience of reading in your newsreader,
and must be removed (along with line breaks) when adding to the query's
criteria. The criteria I provided looks at records with the year from
LoadDate being the same as the current year, and for the week specified in
[Forms]![frmMain]![weekreport]).
I don't know how the calendar fits into this. Are you using it to get the
Week value, or what exactly?

 

[Douglas J. Steele]

Or, perhaps simpler,

Format([LoadDate], "yyyyww") = _
Format([Forms]![frmMain]![weekreport],"yyyyww")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The criteria for LoadDate could be:
Year([LoadDate]) = Year(Date()) AND _
DatePart("ww",[LoadDate]) = _
DatePart("ww",[Forms]![frmMain]![weekreport])

Note that the underscores are for convenience of reading in your
newsreader, and must be removed (along with line breaks) when adding to
the query's criteria. The criteria I provided looks at records with the
year from LoadDate being the same as the current year, and for the week
specified in [Forms]![frmMain]![weekreport]).
I don't know how the calendar fits into this. Are you using it to get the
Week value, or what exactly?

On a database that I have been given a command button on a form kicks off
a
report which has a query that was built with only the week, no year and
the
report is now pulling both 2006 and 2007 data. The query field is: Expr1:
Format([Load Date],"ww"); the criteria is:
Format([Forms]![frmMain]![weekreport].[value],"ww"). How do it code the
query
to also look at the year field? What is on the form is an Active X
control
calendar: MSCAL.Calendar.7. Suggestions? Thanks so much! Jani

 

[BruceM]

Yes, that would be simpler, now that you mention it.

Or, perhaps simpler,

Format([LoadDate], "yyyyww") = _
Format([Forms]![frmMain]![weekreport],"yyyyww")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The criteria for LoadDate could be:
Year([LoadDate]) = Year(Date()) AND _
DatePart("ww",[LoadDate]) = _
DatePart("ww",[Forms]![frmMain]![weekreport])

Note that the underscores are for convenience of reading in your
newsreader, and must be removed (along with line breaks) when adding to
the query's criteria. The criteria I provided looks at records with the
year from LoadDate being the same as the current year, and for the week
specified in [Forms]![frmMain]![weekreport]).
I don't know how the calendar fits into this. Are you using it to get
the Week value, or what exactly?

On a database that I have been given a command button on a form kicks
off a
report which has a query that was built with only the week, no year and
the
report is now pulling both 2006 and 2007 data. The query field is:
Expr1:
Format([Load Date],"ww"); the criteria is:
Format([Forms]![frmMain]![weekreport].[value],"ww"). How do it code the
query
to also look at the year field? What is on the form is an Active X
control
calendar: MSCAL.Calendar.7. Suggestions? Thanks so much! Jani