"Pop-Up" search criteria

  • Thread starter Thread starter dano0097
  • Start date Start date
D

dano0097

I am trying to put the finishing touches on a Monthly report. I have a
query set up so that it finds the dates between two dates that i type
in in the query. rather than having to change the query criteria
everytime i want to make a report for the next month, is there a way
that i can set up a query or a macro to ask for search critera and then
it uses the criteria i give it to pull up a month worth of data? any
help would be greatly appreciated! Thanks muchly.

-Dan L.
 
Maybe this will help get you started.

The current month:
DateSerial(Year(Date()), Month(Date()), 1)

The next month:
DateSerial(Year(Date()), Month(Date()) + 1, 1)

The last day of the current month:
DateSerial(Year(Date()), Month(Date()) + 1, 0)

The last day of the next month:
DateSerial(Year(Date()), Month(Date()) + 2, 0)

The first day of the previous month:
DateSerial(Year(Date()), Month(Date())-1,1)

The last day of the previous month:
DateSerial(Year(Date()), Month(Date()),0)

The first day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 1)

The last day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 4, 0)

The first day of the current week (assuming Sunday = day 1):
Date() - WeekDay(Date()) + 1

The last day of the current week:
Date() - WeekDay(Date()) + 7

The first day of the current week (using settings in Options dialog
box):
Date() - WeekDay(Date(), 0) + 1

The last day of the current week:
Date() - WeekDay(Date(), 0) + 7


HTH
 
Hi:

Place the selection criteria in the underlying queries.

For example, under the field name [Date] in each query enter this criteria,

between form![DateSelect]![startdate] and form![DateSelect]![enddate]

this assumes your input form is called 'DateSelect' of course and it
contains unbound fields called 'startdate' and 'enddate'

The following function is usefull to check if your popup form isloaded and
I
think its included in the NorthWind database example.

Option Compare Database
Option Explicit

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet
view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function

The NorthWind database will show you how to make a macro to take advantage
of this function.
The macro is associated with [ok] and [cancel] buttons on the popup form
through
the events of the report.

hope it helps :)
 
Back
Top