Conflicting report parameters

J

Joan

I have designed a report where the user enters report parameters Start Date
and End Date on a form and when they then click the Preview button, my code
builds the 'where' criteria string called : mysql and opens the report using
mysql as the where argument. Essentially the criteria string says that
[ReceivedDate] must be >= StartDate and <= EndDate. The report works fine
except that the client would like to include one exception to this criteria.

The report prints out dog records of dogs that have come into inventory
between and including the StartDate and EndDate. However, sometimes a
Breeder will hold back a pup from a litter if it was small and bring it in
sometimes several weeks after the rest of the litter was brought in and
added to inventory. The client would like to also include this pup in the
report (because it belongs to the litter that was brought in earlier) even
though the [ReceivedDate] may fall outside the report parameters, StartDate
and EndDate. How do I go about getting my report to do this?

Joan
 
G

Gary Walter

Joan said:
I have designed a report where the user enters report parameters Start Date
and End Date on a form and when they then click the Preview button, my code
builds the 'where' criteria string called : mysql and opens the report using
mysql as the where argument. Essentially the criteria string says that
[ReceivedDate] must be >= StartDate and <= EndDate. The report works fine
except that the client would like to include one exception to this criteria.

The report prints out dog records of dogs that have come into inventory
between and including the StartDate and EndDate. However, sometimes a
Breeder will hold back a pup from a litter if it was small and bring it in
sometimes several weeks after the rest of the litter was brought in and
added to inventory. The client would like to also include this pup in the
report (because it belongs to the litter that was brought in earlier) even
though the [ReceivedDate] may fall outside the report parameters, StartDate
and EndDate. How do I go about getting my report to do this?
Click to expand...
Hi Joan,

Do you have a field (or fields) that identifies each dog
as coming from a specific breeder's specific litter?

If so, please provide your table(s) structure(s)
and maybe some simple example data.

Gary Walter
 
J

Joan

Gary,
Since this report has a lot of information from various tables in it, the
final record source for the report (called qryREGBookByBreeder) is built
from many other queries. However the basic tables that these queries use
are the Dogs, Litters, Breeders, and Breeds tables. Below is a shortened
but applicable list of the fields in these tables. The field that
identifies each dog as coming from a specific breeder's specific litter is:
[Litter Number] .

DOGS table:
[Dog Number] (Primary Key)
[Litter Number]
[Received Date]

LITTERS table:
[Litter Number] (Primary Key)
BreederCode
[Breed Code]
WhelpedDate

BREEDERS table:
BreederCode (Primary Key)
First
Last
Address
etc......

BREEDS table:
[Breed Code] (Primary Key)
[Breed Name]

On the form where the user enters report parameters Start Date and End
Date, they can also enter BreederCode as a report parameter.

Joan


Gary Walter said:
Joan said:
I have designed a report where the user enters report parameters Start Date
and End Date on a form and when they then click the Preview button, my code
builds the 'where' criteria string called : mysql and opens the report using
mysql as the where argument. Essentially the criteria string says that
[ReceivedDate] must be >= StartDate and <= EndDate. The report works fine
except that the client would like to include one exception to this criteria.

The report prints out dog records of dogs that have come into inventory
between and including the StartDate and EndDate. However, sometimes a
Breeder will hold back a pup from a litter if it was small and bring it in
sometimes several weeks after the rest of the litter was brought in and
added to inventory. The client would like to also include this pup in the
report (because it belongs to the litter that was brought in earlier) even
though the [ReceivedDate] may fall outside the report parameters, StartDate
and EndDate. How do I go about getting my report to do this?
Click to expand...
Hi Joan,

Do you have a field (or fields) that identifies each dog
as coming from a specific breeder's specific litter?

If so, please provide your table(s) structure(s)
and maybe some simple example data.

Gary Walter
Click to expand...
 
G

Gary Walter

Hi Joan,

It looks to me like you just need to shift
your focus to [Litter Number].

"show me all the dogs from litter(s)
that have been received (in part or full)
between start and end date"

This would (maybe wrongly) also report
the case where a late pup was received
in the time frame and show earlier sibs
from the litter.....

Anyway, this is what I think you are wanting...
(ignoring BreederCode for now)

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
Dim mysql As String
Dim stStartDate As String
Dim stEndDate As String

stStartDate = Me!txtStartDate
stEndDate = Me!txtEndDate

If IsDate(stStartDate) And IsDate(stEndDate) Then
mysql = "[Litter Number] IN (SELECT [Litter Number] " _
& "FROM DOGS WHERE ([Received Date] >=#" & stStartDate & "#) " _
& "AND ([Received Date] <=#" & stEndDate & "#))"
Else
MsgBox "Please verify start and end dates."
End If

stDocName = "rptDOGS"
DoCmd.OpenReport stDocName, acPreview, , mysql

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub

Joan said:
Gary,
Since this report has a lot of information from various tables in it, the
final record source for the report (called qryREGBookByBreeder) is built
from many other queries. However the basic tables that these queries use
are the Dogs, Litters, Breeders, and Breeds tables. Below is a shortened
but applicable list of the fields in these tables. The field that
identifies each dog as coming from a specific breeder's specific litter is:
[Litter Number] .

DOGS table:
[Dog Number] (Primary Key)
[Litter Number]
[Received Date]

LITTERS table:
[Litter Number] (Primary Key)
BreederCode
[Breed Code]
WhelpedDate

BREEDERS table:
BreederCode (Primary Key)
First
Last
Address
etc......

BREEDS table:
[Breed Code] (Primary Key)
[Breed Name]

On the form where the user enters report parameters Start Date and End
Date, they can also enter BreederCode as a report parameter.

Joan


Gary Walter said:
Joan said:
I have designed a report where the user enters report parameters Start Date
and End Date on a form and when they then click the Preview button, my code
builds the 'where' criteria string called : mysql and opens the report using
mysql as the where argument. Essentially the criteria string says that
[ReceivedDate] must be >= StartDate and <= EndDate. The report works fine
except that the client would like to include one exception to this criteria.

The report prints out dog records of dogs that have come into inventory
between and including the StartDate and EndDate. However, sometimes a
Breeder will hold back a pup from a litter if it was small and bring it in
sometimes several weeks after the rest of the litter was brought in and
added to inventory. The client would like to also include this pup in the
report (because it belongs to the litter that was brought in earlier) even
though the [ReceivedDate] may fall outside the report parameters, StartDate
and EndDate. How do I go about getting my report to do this?
Click to expand...
Hi Joan,

Do you have a field (or fields) that identifies each dog
as coming from a specific breeder's specific litter?

If so, please provide your table(s) structure(s)
and maybe some simple example data.

Gary Walter
Click to expand...
Click to expand...
 
G

Gary Walter

Nuts... there should have been an "Exit Sub"
after the MsgBox..


Else
MsgBox "Please verify start and end dates."
Exit Sub
End If


Gary Walter said:
Hi Joan,

It looks to me like you just need to shift
your focus to [Litter Number].

"show me all the dogs from litter(s)
that have been received (in part or full)
between start and end date"

This would (maybe wrongly) also report
the case where a late pup was received
in the time frame and show earlier sibs
from the litter.....

Anyway, this is what I think you are wanting...
(ignoring BreederCode for now)

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
Dim mysql As String
Dim stStartDate As String
Dim stEndDate As String

stStartDate = Me!txtStartDate
stEndDate = Me!txtEndDate

If IsDate(stStartDate) And IsDate(stEndDate) Then
mysql = "[Litter Number] IN (SELECT [Litter Number] " _
& "FROM DOGS WHERE ([Received Date] >=#" & stStartDate & "#) " _
& "AND ([Received Date] <=#" & stEndDate & "#))"
Else
MsgBox "Please verify start and end dates."
End If

stDocName = "rptDOGS"
DoCmd.OpenReport stDocName, acPreview, , mysql

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub

Joan said:
Gary,
Since this report has a lot of information from various tables in it, the
final record source for the report (called qryREGBookByBreeder) is built
from many other queries. However the basic tables that these queries use
are the Dogs, Litters, Breeders, and Breeds tables. Below is a shortened
but applicable list of the fields in these tables. The field that
identifies each dog as coming from a specific breeder's specific litter is:
[Litter Number] .

DOGS table:
[Dog Number] (Primary Key)
[Litter Number]
[Received Date]

LITTERS table:
[Litter Number] (Primary Key)
BreederCode
[Breed Code]
WhelpedDate

BREEDERS table:
BreederCode (Primary Key)
First
Last
Address
etc......

BREEDS table:
[Breed Code] (Primary Key)
[Breed Name]

On the form where the user enters report parameters Start Date and End
Date, they can also enter BreederCode as a report parameter.

Joan


Gary Walter said:
I have designed a report where the user enters report parameters Start Date
and End Date on a form and when they then click the Preview button, my code
builds the 'where' criteria string called : mysql and opens the report using
mysql as the where argument. Essentially the criteria string says that
[ReceivedDate] must be >= StartDate and <= EndDate. The report works fine
except that the client would like to include one exception to this criteria.

The report prints out dog records of dogs that have come into inventory
between and including the StartDate and EndDate. However, sometimes a
Breeder will hold back a pup from a litter if it was small and bring it in
sometimes several weeks after the rest of the litter was brought in and
added to inventory. The client would like to also include this pup in the
report (because it belongs to the litter that was brought in earlier) even
though the [ReceivedDate] may fall outside the report parameters, StartDate
and EndDate. How do I go about getting my report to do this?

Hi Joan,

Do you have a field (or fields) that identifies each dog
as coming from a specific breeder's specific litter?

If so, please provide your table(s) structure(s)
and maybe some simple example data.

Gary Walter
Click to expand...
Click to expand...
Click to expand...
 
J

Joan

Thanks for your reply, Gary.

I got this to work by doing a work-around. I added a field called,
LitRecdDate to the Litter's table. Then on the AddLitters form where
Litters are added on the main form , individual dogs on a subform, I put an
invisible control on the main form. This control's control source is
[LitRecdDate] and the default value is = Date(). This automatically added
the date received for the litter to the Litters table and then I used this
field in the Record Source for the report instead of [Received Date]. It
seems to work fine.

Thank you though for your help. I am keeping a copy of your reply in my
records to refer to on other similar situations.
Joan


Gary Walter said:
Nuts... there should have been an "Exit Sub"
after the MsgBox..


Else
MsgBox "Please verify start and end dates."
Exit Sub
End If


Gary Walter said:
Hi Joan,

It looks to me like you just need to shift
your focus to [Litter Number].

"show me all the dogs from litter(s)
that have been received (in part or full)
between start and end date"

This would (maybe wrongly) also report
the case where a late pup was received
in the time frame and show earlier sibs
from the litter.....

Anyway, this is what I think you are wanting...
(ignoring BreederCode for now)

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
Dim mysql As String
Dim stStartDate As String
Dim stEndDate As String

stStartDate = Me!txtStartDate
stEndDate = Me!txtEndDate

If IsDate(stStartDate) And IsDate(stEndDate) Then
mysql = "[Litter Number] IN (SELECT [Litter Number] " _
& "FROM DOGS WHERE ([Received Date] >=#" & stStartDate & "#) " _
& "AND ([Received Date] <=#" & stEndDate & "#))"
Else
MsgBox "Please verify start and end dates."
End If

stDocName = "rptDOGS"
DoCmd.OpenReport stDocName, acPreview, , mysql

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub

Joan said:
Gary,
Since this report has a lot of information from various tables in it, the
final record source for the report (called qryREGBookByBreeder) is built
from many other queries. However the basic tables that these queries use
are the Dogs, Litters, Breeders, and Breeds tables. Below is a shortened
but applicable list of the fields in these tables. The field that
identifies each dog as coming from a specific breeder's specific litter is:
[Litter Number] .

DOGS table:
[Dog Number] (Primary Key)
[Litter Number]
[Received Date]

LITTERS table:
[Litter Number] (Primary Key)
BreederCode
[Breed Code]
WhelpedDate

BREEDERS table:
BreederCode (Primary Key)
First
Last
Address
etc......

BREEDS table:
[Breed Code] (Primary Key)
[Breed Name]

On the form where the user enters report parameters Start Date and End
Date, they can also enter BreederCode as a report parameter.

Joan



I have designed a report where the user enters report parameters Start
Date
and End Date on a form and when they then click the Preview button, my
code
builds the 'where' criteria string called : mysql and opens the report
using
mysql as the where argument. Essentially the criteria string says that
[ReceivedDate] must be >= StartDate and <= EndDate. The report works
fine
except that the client would like to include one exception to this
criteria.

The report prints out dog records of dogs that have come into inventory
between and including the StartDate and EndDate. However, sometimes a
Breeder will hold back a pup from a litter if it was small and bring it
in
sometimes several weeks after the rest of the litter was brought in and
added to inventory. The client would like to also include this pup in
the
report (because it belongs to the litter that was brought in earlier)
even
though the [ReceivedDate] may fall outside the report parameters,
StartDate
and EndDate. How do I go about getting my report to do this?

Hi Joan,

Do you have a field (or fields) that identifies each dog
as coming from a specific breeder's specific litter?

If so, please provide your table(s) structure(s)
and maybe some simple example data.

Gary Walter
Click to expand...
Click to expand...
Click to expand...
 
Top