Using a Form to create a Report

[Guest]

Good Day..

I am attempting to use a form to create a data specific report from my data
table. I appear to have a problem with the query that is running from this
command though. I am able to retrieve the data when I fill out the entire
form but the report chokes when an field has no entered data. I am 99.9%
sure it is an issue with my query. Here is what I have in SQL:

SELECT Exception.Date, Exception.[Thru Date], Exception.Name,
Exception.[Event Type], Exception.FMLA
FROM [Exception]

WHERE (((Exception.Name)=[Forms]![Report Generator]![Name] Or
(Exception.Name) Is Null) AND ((Exception.[Event Type])=[Forms]![Report
Generator]![Event] Or (Exception.[Event Type]) Is Null) AND
((Exception.FMLA)=[Forms]![Report Generator]![FMLA] Or (Exception.FMLA) Is
Null))

GROUP BY Exception.Date, Exception.[Thru Date], Exception.Name,
Exception.[Event Type], Exception.FMLA
HAVING (((Exception.Date) Between [Date] And [Thru Date]) AND
((Exception.[Thru Date]) Between [Date] And [Thru Date]));

My goal is to allow the user to specify the data they want to see in the
report. However, I would like for the report to work even when a value is
not found since we may wish to run a report based upon an event without the
need to limit the data down to specifics.

Thank you in advance for your help.

Jim

 

[Guest]

I think you want

WHERE (((Exception.Name)=[Forms]![Report Generator]![Name] Or
[Forms]![Report Generator]![Name] Is Null) AND ((Exception.[Event
Type])=[Forms]![Report Generator]![Event] Or [Forms]![Report
Generator]![Event] Is Null) AND ((Exception.FMLA)=[Forms]![Report
Generator]![FMLA] Or [Forms]![Report Generator]![FMLA] Is Null))

 

[Guest]

Thank you Duane.. I was sort of on the right track but had some issues as I
can see in your SQL below. I removed a few double spaces and it worked like
a charm.

Thanks for the help.

Jim
--
"Baliff. Whack him in the pee-pee!"
"How come his is so much bigger then yours?"
"I am not even suppose to be here today!"

I think you want

WHERE (((Exception.Name)=[Forms]![Report Generator]![Name] Or
[Forms]![Report Generator]![Name] Is Null) AND ((Exception.[Event
Type])=[Forms]![Report Generator]![Event] Or [Forms]![Report
Generator]![Event] Is Null) AND ((Exception.FMLA)=[Forms]![Report
Generator]![FMLA] Or [Forms]![Report Generator]![FMLA] Is Null))

--
Duane Hookom
Microsoft Access MVP

Good Day..

I am attempting to use a form to create a data specific report from my data
table. I appear to have a problem with the query that is running from this
command though. I am able to retrieve the data when I fill out the entire
form but the report chokes when an field has no entered data. I am 99.9%
sure it is an issue with my query. Here is what I have in SQL:

SELECT Exception.Date, Exception.[Thru Date], Exception.Name,
Exception.[Event Type], Exception.FMLA
FROM [Exception]

WHERE (((Exception.Name)=[Forms]![Report Generator]![Name] Or
(Exception.Name) Is Null) AND ((Exception.[Event Type])=[Forms]![Report
Generator]![Event] Or (Exception.[Event Type]) Is Null) AND
((Exception.FMLA)=[Forms]![Report Generator]![FMLA] Or (Exception.FMLA) Is
Null))

GROUP BY Exception.Date, Exception.[Thru Date], Exception.Name,
Exception.[Event Type], Exception.FMLA
HAVING (((Exception.Date) Between [Date] And [Thru Date]) AND
((Exception.[Thru Date]) Between [Date] And [Thru Date]));

My goal is to allow the user to specify the data they want to see in the
report. However, I would like for the report to work even when a value is
not found since we may wish to run a report based upon an event without the
need to limit the data down to specifics.

Thank you in advance for your help.

Jim