Using a Form to create a Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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))
 
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!"


Duane Hookom said:
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


That Crazy Hockey Dood said:
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
Click to expand...
Click to expand...
 
Back
Top