Default dates in parameter values

[Duchess]

I currently have parameters for reports for start/end dates with a defined
format:

SELECT *
FROM [Minor Injury]
WHERE ((([Minor Injury].Date)>=[Enter start date for report: 00/00/00] And
([Minor Injury].Date)<=[Enter end date for report: 00/00/00]) AND (([Minor
Injury].Dept)="AdTech Safety Team"))
ORDER BY [Minor Injury].Date;

I would like to have the start date default to the first day of the year and
the ending date to be the current date (now). How do I reword the SQL string
to get those values defaulted in?

 

[Duchess]

I should have clarified that the person opening the report needs to option of
selection any start or end date but that most would be using the first day of
the year to the current day for a starting point thus the reason for the
default (user ease).

 

[fredg]

I currently have parameters for reports for start/end dates with a defined
format:

SELECT *
FROM [Minor Injury]
WHERE ((([Minor Injury].Date)>=[Enter start date for report: 00/00/00] And
([Minor Injury].Date)<=[Enter end date for report: 00/00/00]) AND (([Minor
Injury].Dept)="AdTech Safety Team"))
ORDER BY [Minor Injury].Date;

I would like to have the start date default to the first day of the year and
the ending date to be the current date (now). How do I reword the SQL string
to get those values defaulted in?

1) Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

2) Be careful in wanting to use Now() to return records. Now()
includes the time of day and therefore might return different records
depending upon the time of day you run the query. If the Date AND Time
is not important, use Date() instead.

3)You can use this.
For brevity I've changed the parameter text (the text within the
brackets) to [StartDate] and [EndDate]. You can substitute your own
text. I've also used [FieldName] Between .... And. You can use your
[FieldName] >= and [FieldName] <= syntax if you wish.

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT [Minor Injury].*
FROM [Minor Injury]
WHERE ([Minor Injury].ADate Between
IIf(IsNull([StartDate]),DateSerial(Year(Date()),1,1),[StartDate]) And
IIf(IsNull([EndDate]),Date(),[EndDate])) And [Minor Injury].Dept) =
"AdTech Safety Team"
ORDER BY [Minor Injury].ADate;

Change ADate to whatever the new Date Field Name is.
Watch the parenthesis placement.
Note the Parameters declaration statement.

 

[John W. Vinson]

I currently have parameters for reports for start/end dates with a defined
format:

SELECT *
FROM [Minor Injury]
WHERE ((([Minor Injury].Date)>=[Enter start date for report: 00/00/00] And
([Minor Injury].Date)<=[Enter end date for report: 00/00/00]) AND (([Minor
Injury].Dept)="AdTech Safety Team"))
ORDER BY [Minor Injury].Date;

I would like to have the start date default to the first day of the year and
the ending date to be the current date (now). How do I reword the SQL string
to get those values defaulted in?

I would suggest creating a little form named frmCrit, with two textboxes
txtStart and txtEnd. Set the Default Value property of txtStart to

=DateSerial(Year(Date()), 1, 1)

and of txtEnd to =Now() - use =Date() if the table field contains no time
portion.

Change your prompt criteria to

= [Forms]![frmCrit]![txtStart] AND <= [Forms]![frmCrit]![txtEnd]

You can either open frmCrit (rather than opening the report) and put a command
button on it to launch the report, or perhaps better, open frmCrit in the
Report's Open event, and close it in the report's Close event. If you do the
latter, open the form in dialog mode and have the button set its Visible
property to No.

 

[Duchess]

Thank you to both of you for your quick replies. I will try them to see if
they work for what I need.

I currently have parameters for reports for start/end dates with a defined
format:

SELECT *
FROM [Minor Injury]
WHERE ((([Minor Injury].Date)>=[Enter start date for report: 00/00/00] And
([Minor Injury].Date)<=[Enter end date for report: 00/00/00]) AND (([Minor
Injury].Dept)="AdTech Safety Team"))
ORDER BY [Minor Injury].Date;

I would like to have the start date default to the first day of the year and
the ending date to be the current date (now). How do I reword the SQL string
to get those values defaulted in?

I would suggest creating a little form named frmCrit, with two textboxes
txtStart and txtEnd. Set the Default Value property of txtStart to

=DateSerial(Year(Date()), 1, 1)

and of txtEnd to =Now() - use =Date() if the table field contains no time
portion.

Change your prompt criteria to

= [Forms]![frmCrit]![txtStart] AND <= [Forms]![frmCrit]![txtEnd]

You can either open frmCrit (rather than opening the report) and put a command
button on it to launch the report, or perhaps better, open frmCrit in the
Report's Open event, and close it in the report's Close event. If you do the
latter, open the form in dialog mode and have the button set its Visible
property to No.