Query to show year & date

[PJ]

Hi,

I have a report runs off a query and a form for users to enter the criteria
into pull this report - so far everything is working good. I have 2
textboxes so the users can enter the from date and end date in this format
(mm/dd/yyyy). but some of the records don't have from date or end day, but
have another box for just the year. is there anyway i can still pull this
report the same way it has been doing and to include the year (yyyy) within
the range that the users enter in the from date box and end date box?
Thanks.

 

[KARL DEWEY]

Open the query in design view.
In a separate criteria row from any other but under your 'Year' field enter
this --
IFF([FirstDateField] Is Null And [SecondDateField] Is Null,
Year([Forms]![YourFormName]![YouFromDateBox]),)

If you can not follow these instructions post your query SQL. Open query
in design view, click on SQL view, highlight all, copy, and paste in a post.

 

[PJ]

Thanks Karl,

this is the whole code from the WHERE clause, including your code with some
additions:

WHERE (((tblMain.Served) Between [Forms]![frmReportCrit_1]![FromDate] And
[Forms]![frmReportCrit_1]![EndDate])) OR (((tblMain.[Rec'd]) Between
[Forms]![frmReportCrit_1]![FromDate] And
[Forms]![frmReportCrit_1]![EndDate])) OR (((tblMain.Year)=IIf([Served] Is
Null And [Rec'd] Is Null,(tblMain.Year) Between
Year([Forms]![frmReportCrit_1]![FromDate]) And
Year([Forms]![frmReportCrit_1]![EndDate]))))

when i run this query - it said that the query is complex.

so this is what i have in the table:
Served 'format is mm/dd/yyyy and sometimes Null
Rec'd 'format is mm/dd/yyyy and sometimes Null
Year 'format is yyyy

on Form i have:
From Date 'from either Served or Rec'd
End Date 'from either Served or Rec'd

but Served and Rec'd sometimes can be null at the same time, so using Year
to pull any records without Served and received.

Thanks

Open the query in design view.
In a separate criteria row from any other but under your 'Year' field
enter
this --
IFF([FirstDateField] Is Null And [SecondDateField] Is Null,
Year([Forms]![YourFormName]![YouFromDateBox]),)

If you can not follow these instructions post your query SQL. Open query
in design view, click on SQL view, highlight all, copy, and paste in a
post.

--
KARL DEWEY
Build a little - Test a little

Hi,

I have a report runs off a query and a form for users to enter the
criteria
into pull this report - so far everything is working good. I have 2
textboxes so the users can enter the from date and end date in this
format
(mm/dd/yyyy). but some of the records don't have from date or end day,
but
have another box for just the year. is there anyway i can still pull
this
report the same way it has been doing and to include the year (yyyy)
within
the range that the users enter in the from date box and end date box?
Thanks.

 

[KARL DEWEY]

I think I got the right number of openoing and closing parens ---
WHERE (tblMain.Served Between [Forms]![frmReportCrit_1]![FromDate] And
[Forms]![frmReportCrit_1]![EndDate]) OR (tblMain.[Rec'd] Between
[Forms]![frmReportCrit_1]![FromDate] And [Forms]![frmReportCrit_1]![EndDate])
OR IIF ([Served] Is Null And [Rec'd] Is Null, tblMain.Year Between
Year([Forms]![frmReportCrit_1]![FromDate]) And
Year([Forms]![frmReportCrit_1]![EndDate]),))

--
KARL DEWEY
Build a little - Test a little

Thanks Karl,

this is the whole code from the WHERE clause, including your code with some
additions:

WHERE (((tblMain.Served) Between [Forms]![frmReportCrit_1]![FromDate] And
[Forms]![frmReportCrit_1]![EndDate])) OR (((tblMain.[Rec'd]) Between
[Forms]![frmReportCrit_1]![FromDate] And
[Forms]![frmReportCrit_1]![EndDate])) OR (((tblMain.Year)=IIf([Served] Is
Null And [Rec'd] Is Null,(tblMain.Year) Between
Year([Forms]![frmReportCrit_1]![FromDate]) And
Year([Forms]![frmReportCrit_1]![EndDate]))))

when i run this query - it said that the query is complex.

so this is what i have in the table:
Served 'format is mm/dd/yyyy and sometimes Null
Rec'd 'format is mm/dd/yyyy and sometimes Null
Year 'format is yyyy

on Form i have:
From Date 'from either Served or Rec'd
End Date 'from either Served or Rec'd

but Served and Rec'd sometimes can be null at the same time, so using Year
to pull any records without Served and received.

Thanks

Open the query in design view.
In a separate criteria row from any other but under your 'Year' field
enter
this --
IFF([FirstDateField] Is Null And [SecondDateField] Is Null,
Year([Forms]![YourFormName]![YouFromDateBox]),)

If you can not follow these instructions post your query SQL. Open query
in design view, click on SQL view, highlight all, copy, and paste in a
post.

--
KARL DEWEY
Build a little - Test a little

Hi,

I have a report runs off a query and a form for users to enter the
criteria
into pull this report - so far everything is working good. I have 2
textboxes so the users can enter the from date and end date in this
format
(mm/dd/yyyy). but some of the records don't have from date or end day,
but
have another box for just the year. is there anyway i can still pull
this
report the same way it has been doing and to include the year (yyyy)
within
the range that the users enter in the from date box and end date box?
Thanks.

 

[PJ]

Thanks Karl,

It works great and I learned something too.

I think I got the right number of openoing and closing parens ---
WHERE (tblMain.Served Between [Forms]![frmReportCrit_1]![FromDate] And
[Forms]![frmReportCrit_1]![EndDate]) OR (tblMain.[Rec'd] Between
[Forms]![frmReportCrit_1]![FromDate] And
[Forms]![frmReportCrit_1]![EndDate])
OR IIF ([Served] Is Null And [Rec'd] Is Null, tblMain.Year Between
Year([Forms]![frmReportCrit_1]![FromDate]) And
Year([Forms]![frmReportCrit_1]![EndDate]),))

--
KARL DEWEY
Build a little - Test a little

Thanks Karl,

this is the whole code from the WHERE clause, including your code with
some
additions:

WHERE (((tblMain.Served) Between [Forms]![frmReportCrit_1]![FromDate] And
[Forms]![frmReportCrit_1]![EndDate])) OR (((tblMain.[Rec'd]) Between
[Forms]![frmReportCrit_1]![FromDate] And
[Forms]![frmReportCrit_1]![EndDate])) OR (((tblMain.Year)=IIf([Served] Is
Null And [Rec'd] Is Null,(tblMain.Year) Between
Year([Forms]![frmReportCrit_1]![FromDate]) And
Year([Forms]![frmReportCrit_1]![EndDate]))))

when i run this query - it said that the query is complex.

so this is what i have in the table:
Served 'format is mm/dd/yyyy and sometimes Null
Rec'd 'format is mm/dd/yyyy and sometimes Null
Year 'format is yyyy

on Form i have:
From Date 'from either Served or Rec'd
End Date 'from either Served or Rec'd

but Served and Rec'd sometimes can be null at the same time, so using
Year
to pull any records without Served and received.

Thanks

Open the query in design view.
In a separate criteria row from any other but under your 'Year' field
enter
this --
IFF([FirstDateField] Is Null And [SecondDateField] Is Null,
Year([Forms]![YourFormName]![YouFromDateBox]),)

If you can not follow these instructions post your query SQL. Open
query
in design view, click on SQL view, highlight all, copy, and paste in a
post.

--
KARL DEWEY
Build a little - Test a little


:

Hi,

I have a report runs off a query and a form for users to enter the
criteria
into pull this report - so far everything is working good. I have 2
textboxes so the users can enter the from date and end date in this
format
(mm/dd/yyyy). but some of the records don't have from date or end
day,
but
have another box for just the year. is there anyway i can still pull
this
report the same way it has been doing and to include the year (yyyy)
within
the range that the users enter in the from date box and end date box?
Thanks.