Using a Form to create a Report Pt. 2

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

Guest

I am in need of assistance once again. Duane Hookom assisted in a major way
the first time around but I have just found another error. I have a form
which creates a report. The user can input: Date, Thru Date, Name, Event
Type & FMLA. Duane helped me with the code so that it would read "null" for
the Name, Event Type & FMLA fields. However, the report is not reading the
dates I am putting in for the range. I have fought with this all morning and
I cannot figure out why it is doing this. The SQL for this is:

SELECT Exception.Date, Exception.[Thru Date], Exception.[Associate Name],
Exception.[Event Type], Exception.FMLA
FROM [Exception]
WHERE (((Exception.[Associate Name])=[Forms]![Report Generator]![Name]) AND
((Exception.[Event Type])=[Forms]![Report Generator]![Event]) AND
((Exception.FMLA)=[Forms]![Report Generator]![FMLA])) OR (((Exception.[Event
Type])=[Forms]![Report Generator]![Event]) AND
((Exception.FMLA)=[Forms]![Report Generator]![FMLA]) AND (([Forms]![Report
Generator]![Name]) Is Null)) OR (((Exception.[Associate
Name])=[Forms]![Report Generator]![Name]) AND
((Exception.FMLA)=[Forms]![Report Generator]![FMLA]) AND (([Forms]![Report
Generator]![Event]) Is Null)) OR (((Exception.FMLA)=[Forms]![Report
Generator]![FMLA]) AND (([Forms]![Report Generator]![Name]) Is Null) AND
(([Forms]![Report Generator]![Event]) Is Null)) OR (((Exception.[Associate
Name])=[Forms]![Report Generator]![Name]) AND ((Exception.[Event
Type])=[Forms]![Report Generator]![Event]) AND (([Forms]![Report
Generator]![FMLA]) Is Null)) OR (((Exception.[Event Type])=[Forms]![Report
Generator]![Event]) AND (([Forms]![Report Generator]![Name]) Is Null) AND
(([Forms]![Report Generator]![FMLA]) Is Null)) OR (((Exception.[Associate
Name])=[Forms]![Report Generator]![Name]) AND (([Forms]![Report
Generator]![Event]) Is Null) AND (([Forms]![Report Generator]![FMLA]) Is
Null)) OR ((([Forms]![Report Generator]![Name]) Is Null) AND
(([Forms]![Report Generator]![Event]) Is Null) AND (([Forms]![Report
Generator]![FMLA]) Is Null));

I just need to know what I am missing to force the report to read the input
date & thru date for the results.

Thank you in advance for your help. This group has been the best as I have
gone through this project.

Thanks,
Jim
 
I would try to simplify this down to
SELECT Exception.Date, Exception.[Thru Date], Exception.[Associate Name],
Exception.[Event Type], Exception.FMLA
FROM [Exception]
WHERE
[Associate Name] & "" = Nz([Forms]![Report Generator]![Name],[Associate
Name] & "")
AND
[Event Type] & "" = Nz([Forms]![Report Generator]![Event],[Event Type] & "")
AND
FMLA & "" = Nz([Forms]![Report Generator]![FMLA],FMLA & "")
AND
Nz([Date],#1/1/1900#) >= Nz([Forms]![Report
Generator]![txtFromDate],Nz([Date],#1/1/1900#))
AND
Nz([Date],#12/31/2099#) <= Nz([Forms]![Report
Generator]![txtThruDate],Nz([Date],#12/31/2099#))

You may need to correct my text box names and other typos.
--
Duane Hookom
Microsoft Access MVP


That Crazy Hockey Dood said:
I am in need of assistance once again. Duane Hookom assisted in a major way
the first time around but I have just found another error. I have a form
which creates a report. The user can input: Date, Thru Date, Name, Event
Type & FMLA. Duane helped me with the code so that it would read "null" for
the Name, Event Type & FMLA fields. However, the report is not reading the
dates I am putting in for the range. I have fought with this all morning and
I cannot figure out why it is doing this. The SQL for this is:

SELECT Exception.Date, Exception.[Thru Date], Exception.[Associate Name],
Exception.[Event Type], Exception.FMLA
FROM [Exception]
WHERE (((Exception.[Associate Name])=[Forms]![Report Generator]![Name]) AND
((Exception.[Event Type])=[Forms]![Report Generator]![Event]) AND
((Exception.FMLA)=[Forms]![Report Generator]![FMLA])) OR (((Exception.[Event
Type])=[Forms]![Report Generator]![Event]) AND
((Exception.FMLA)=[Forms]![Report Generator]![FMLA]) AND (([Forms]![Report
Generator]![Name]) Is Null)) OR (((Exception.[Associate
Name])=[Forms]![Report Generator]![Name]) AND
((Exception.FMLA)=[Forms]![Report Generator]![FMLA]) AND (([Forms]![Report
Generator]![Event]) Is Null)) OR (((Exception.FMLA)=[Forms]![Report
Generator]![FMLA]) AND (([Forms]![Report Generator]![Name]) Is Null) AND
(([Forms]![Report Generator]![Event]) Is Null)) OR (((Exception.[Associate
Name])=[Forms]![Report Generator]![Name]) AND ((Exception.[Event
Type])=[Forms]![Report Generator]![Event]) AND (([Forms]![Report
Generator]![FMLA]) Is Null)) OR (((Exception.[Event Type])=[Forms]![Report
Generator]![Event]) AND (([Forms]![Report Generator]![Name]) Is Null) AND
(([Forms]![Report Generator]![FMLA]) Is Null)) OR (((Exception.[Associate
Name])=[Forms]![Report Generator]![Name]) AND (([Forms]![Report
Generator]![Event]) Is Null) AND (([Forms]![Report Generator]![FMLA]) Is
Null)) OR ((([Forms]![Report Generator]![Name]) Is Null) AND
(([Forms]![Report Generator]![Event]) Is Null) AND (([Forms]![Report
Generator]![FMLA]) Is Null));

I just need to know what I am missing to force the report to read the input
date & thru date for the results.

Thank you in advance for your help. This group has been the best as I have
gone through this project.

Thanks,
Jim
Click to expand...
 
Duane.. You are a bloody genius. Once I made the changes to ensure that all
the names were correct it worked like a charm. I will study this as I want
to understand what some of the things used in the criteria field are as I
have never used them before (Nz). Thank you for all of your help with this
form.

Thanks,
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 would try to simplify this down to
SELECT Exception.Date, Exception.[Thru Date], Exception.[Associate Name],
Exception.[Event Type], Exception.FMLA
FROM [Exception]
WHERE
[Associate Name] & "" = Nz([Forms]![Report Generator]![Name],[Associate
Name] & "")
AND
[Event Type] & "" = Nz([Forms]![Report Generator]![Event],[Event Type] & "")
AND
FMLA & "" = Nz([Forms]![Report Generator]![FMLA],FMLA & "")
AND
Nz([Date],#1/1/1900#) >= Nz([Forms]![Report
Generator]![txtFromDate],Nz([Date],#1/1/1900#))
AND
Nz([Date],#12/31/2099#) <= Nz([Forms]![Report
Generator]![txtThruDate],Nz([Date],#12/31/2099#))

You may need to correct my text box names and other typos.
--
Duane Hookom
Microsoft Access MVP


That Crazy Hockey Dood said:
I am in need of assistance once again. Duane Hookom assisted in a major way
the first time around but I have just found another error. I have a form
which creates a report. The user can input: Date, Thru Date, Name, Event
Type & FMLA. Duane helped me with the code so that it would read "null" for
the Name, Event Type & FMLA fields. However, the report is not reading the
dates I am putting in for the range. I have fought with this all morning and
I cannot figure out why it is doing this. The SQL for this is:

SELECT Exception.Date, Exception.[Thru Date], Exception.[Associate Name],
Exception.[Event Type], Exception.FMLA
FROM [Exception]
WHERE (((Exception.[Associate Name])=[Forms]![Report Generator]![Name]) AND
((Exception.[Event Type])=[Forms]![Report Generator]![Event]) AND
((Exception.FMLA)=[Forms]![Report Generator]![FMLA])) OR (((Exception.[Event
Type])=[Forms]![Report Generator]![Event]) AND
((Exception.FMLA)=[Forms]![Report Generator]![FMLA]) AND (([Forms]![Report
Generator]![Name]) Is Null)) OR (((Exception.[Associate
Name])=[Forms]![Report Generator]![Name]) AND
((Exception.FMLA)=[Forms]![Report Generator]![FMLA]) AND (([Forms]![Report
Generator]![Event]) Is Null)) OR (((Exception.FMLA)=[Forms]![Report
Generator]![FMLA]) AND (([Forms]![Report Generator]![Name]) Is Null) AND
(([Forms]![Report Generator]![Event]) Is Null)) OR (((Exception.[Associate
Name])=[Forms]![Report Generator]![Name]) AND ((Exception.[Event
Type])=[Forms]![Report Generator]![Event]) AND (([Forms]![Report
Generator]![FMLA]) Is Null)) OR (((Exception.[Event Type])=[Forms]![Report
Generator]![Event]) AND (([Forms]![Report Generator]![Name]) Is Null) AND
(([Forms]![Report Generator]![FMLA]) Is Null)) OR (((Exception.[Associate
Name])=[Forms]![Report Generator]![Name]) AND (([Forms]![Report
Generator]![Event]) Is Null) AND (([Forms]![Report Generator]![FMLA]) Is
Null)) OR ((([Forms]![Report Generator]![Name]) Is Null) AND
(([Forms]![Report Generator]![Event]) Is Null) AND (([Forms]![Report
Generator]![FMLA]) Is Null));

I just need to know what I am missing to force the report to read the input
date & thru date for the results.

Thank you in advance for your help. This group has been the best as I have
gone through this project.

Thanks,
Jim
Click to expand...
Click to expand...
 
Back
Top