Print Report Based On Date Range and Name Critteria 1

[iteach2]

Everyone has been very helpful to a newbie like myself...i really appreciate it!!!s-)

Once again i am coming to the well....

I have a table like this:

Startdate EndDate System SiteName


Right now I have a parameter setup to filter reports by SiteName...but then i realized that every time i run that report it will print every record wiht that SiteName regardless of Date..that could really waste alot of paper and patience sorting through it alls-)

What i want to do is be able to pull a report based on the Site name and Date Range...

Do I need to use a pop-up form(something i haven't created yet) or a Dlookup function I do not know.....


Maybe the form would look like this

unbound textbox(StartDate)
unbound testbox(EndDate)------(in my table these are seperate fields)

unbound textbox (SiteName)


I could just maintain a weeks worth of data...that would be bad though s-)

please help

 

[SyedAqif]

Hi

Y dont u write the query of ur report like:

Select Startdate,EndDate,System,SiteName from YourTable
Where
SiteName=Forms![PopupForm]![TxtSiteName]
And
StartDate between Forms![PopupForm]![TxtStartDate]
And Forms![PopupForm]![TxtEndDate];

Attach this query with report and open ur report from the popup form...Goodluck!

Cheers!
Aqif

 

[iteach2]

Thanks a bunch SyedAqif..it works flawlesslys-)

However...

I tried using it else where and ran into this problem:

I have a popup form where you enter

StartDate:_________
Enddate:___________
Typehardware or software)
Site:WW or RR



My query looks like this:

SELECT tblChange.Type, tblChange.[ChangeCtrl#], tblChange.StartDate, tblChange.EndDate, tblChange.Status, tblChange.Site, tblChange.Description
FROM tblChange
WHERE (((tblChange.StartDate) Between [Forms]![hardsoftpopup]![txtStartDate] And [Forms]![hardsoftpopup]![txtEndDate]) AND ((tblChange.Site)=[Forms]![hardsoftpopup]![cboSite]) AND (([Forms]![hardsoftpopup]![cboType])<>False));

My execute button on popupform looks like this:

Private Sub cmdOK_Click()
If IsNull(Me!txtStartDate) Then
MsgBox &quot;Hey! Enter a Start Date.&quot;
Me!txtStartDate.SetFocus
ElseIf IsNull(Me.txtEndDate) Then
MsgBox &quot;Hey! Enter an End Date.&quot;
Me!txtEndDate.SetFocus
ElseIf IsNull(Me.cboType) Then
MsgBox &quot;Hey! Enter Type.&quot;
Me!cboType.SetFocus
ElseIf IsNull(Me.cboSite) Then
MsgBox &quot;Hey! Enter Site Name.&quot;
Me!cboType.SetFocus

Else
DoCmd.OpenReport &quot; rptHardSoft&quot;, acPreview
DoCmd.Close acForm, Me.Name
End If
End Sub

As you can see the query will check a specific date range
site name and type of update(either hardware or software).


Everything works fine,&quot;but&quot; when i do

startdate: 09/22
enddate: 09/24
Site:ww
Type: software

I get:


software 09/22 09/24

software 09/22 09/24

hardware 09/22 09/24


I don't know how to fix its-)

It look like the dates are overiding the type criteria:

is their any way i can restrict the hardware entries(when running a software query)(and vice versa) eventhough it doe fit the specified date range criteria....

I am totally all thunk out.....i'd be very gratefuls-)

 

[SyedAqif]

Hi

Try checking the Software/hardware condition first and then check for dates. If it is still not giving the accurate results <= and >= instead of between. Also u can experiment with LIKE operator in checking the hardware software, ur query should look like this:

SELECT tblChange.Type, blChange.[ChangeCtrl#], tblChange.StartDate,tblChange.EndDate, tblChange.Status, tblChange.Site,
tblChange.Description
FROM tblChange
WHERE tblChange.Site like [Forms]![hardsoftpopup]![cboSite]
AND tblChange.StartDate
Between [Forms]![hardsoftpopup]![txtStartDate]
AND [Forms]![hardsoftpopup]![txtEndDate]
AND [Forms]![hardsoftpopup]![cboType])<>False;

Good Luck.........Cheers!
Aqif