[AccessD] Automating parameterized action queries.....DATE?!

Hi group,

after almost 3 hours I give up. I'm using Ken's
solution and it works great. I'm using for all my
params....off course then I crashed into a F#$@ng date
field.

Can anybody tell me why code does not return a date?
What am I missing?

Module: mParams
Option Compare Database
Option Explicit
   Public oRptParam As cReportParams
   
Public Function ParamVersion() As Variant
   'Dim oRptParam As cReportParams
   Dim blnDestroyRepParam As Boolean
   
   If oRptParam Is Nothing Then
      Set oRptParam = New cReportParams
      blnDestroyRepParam = True
   Else
      blnDestroyRepParam = False
   End If
   
   ParamVersion = oRptParam.Version
   If blnDestroyRepParam Then
      Set oRptParam = Nothing
   End If
End Function

Public Function ParamNewBusinessDate() As Date
   'Dim oRptParam As cReportParams
   Dim blnDestroyRepParam As Boolean
   
   If oRptParam Is Nothing Then
      Set oRptParam = New cReportParams
      blnDestroyRepParam = True
   Else
      blnDestroyRepParam = False
   End If
'????????????????????????????????????????????????????
'===> Somehow ParamNewBusinessDate stays 00:00:00????
'????????????????????????????????????????????????????
   ParamNewBusinessDate = oRptParam.NewBusinessDate
   If blnDestroyRepParam Then
      Set oRptParam = Nothing
   End If
End Function

CLASS cReportParams
Option Compare Database
Option Explicit

Private mVersion As String
Private mdtmNewBusinessDate As Variant
'This parameter checks if the query is run via a form!
Private mFormDriven As Boolean


Property Get Version() As String
   Dim strVersion As String
   
   If mFormDriven Then
      Version = mVersion
   Else
      If mVersion = "" Then
         strVersion = InputBox("Enter the version:")
         If strVersion > "" Then
            mVersion = strVersion
         End If
         Version = mVersion
      Else
         Version = mVersion
      End If
   End If
End Property

Property Let Version(VersionIn As String)
   mVersion = VersionIn
End Property
Property Get NewBusinessDate() As Variant
   Dim dtmNewBusinessDate As Date
   
   If mFormDriven Then
      NewBusinessDate = mdtmNewBusinessDate
   Else
      If mdtmNewBusinessDate = "00:00:00" Then
'I fill the InputBox with the value 22/02/2005
         dtmNewBusinessDate = InputBox("Enter the new
business date:")
         If Not dtmNewBusinessDate = "00:00:00" Then
            mdtmNewBusinessDate = dtmNewBusinessDate
         End If
      Else
         NewBusinessDate = mdtmNewBusinessDate
      End If
   End If
End Property

Property Let NewBusinessDate(NewBusinessDateIn As
Variant)
   mdtmNewBusinessDate = NewBusinessDateIn
End Property

Property Let FormDriven(FormDrivenIN As Boolean)
   mFormDriven = FormDrivenIN
End Property

Property Get FormDriven() As Boolean
   FormDriven = mFormDriven
End Property

Private Sub Class_Initialize()
   mVersion = ""
   mdtmNewBusinessDate = "00:00:00"
   mFormDriven = False
End Sub

Private Sub Class_Terminate()
   mVersion = ""
   mdtmNewBusinessDate = "00:00:00"
   mFormDriven = False
End Sub

-----------------------------------------------------
If somebody needs more details....please ask!

Thnx.

Regards,

Sander
--- Ken Ismert <KIsmert at TexasSystems.com> wrote:
> Sander,
> 
> There is a class-based approach:
> 
> Define a class, CReportParms, with two variant
> properties, Version and
> StartDate:
> 
>    Private mVersion As Variant
>    Private mStartDate As Variant
> 
>    Property Get Version() As Variant
>       Version = mVersion
>    End Property
>    Property Let Version(VersionIn As Variant)
>       mVersion = VersionIn
>    End Property
> 
>    ' (Similar Get/Let for StartDate)
> 
>    Private Sub Class_Initialize()
>       mVersion = Null
>       mStartDate = Null
>    End Sub
> 
> Declare it as global in one of your modules:
> 
>    Public gRptParms As New CReportParms
> 
> Provide two global functions to use in your queries
> in place of your
> parameters:
> 
>    Public Function ParmVersion() As Variant
>       Parms = gRptParms.Version
>    End Function
>    Public Function ParmStartDate() As Variant
>       Parms = gRptParms.StartDate
>    End Function
> 
> You can set these properties in your form before you
> run your report:
> 
>    gRptParms.Version = txtVersion
>    gRptParms.StartDate = txtStartDate
> 
> If you can calculate reasonable values for Version
> and StartDate, you can do
> so in the CReportParms Initialize event.
> Alternately, you can prompt the
> user in the class when the queries are run
> standalone:
> 
>    Property Get Version() As Variant
>       Dim sVersion As String
>       If IsNull(mVersion) Then
>           sVersion = InputBox("Enter Version:")
>           If sVersion > "" Then
>              mVersion = CLng(sVersion)
>           End If
>       End If
>       Version = mVersion
>    End Property
> 
> You could also have a third property, FormDriven,
> that, when set to True,
> doesn't prompt the user. Only your frmReport form
> will set this property,
> and clear it upon exit. That way, the user is always
> prompted when running
> the queries stand-alone.
> 
> -Ken
> 
> > Hi group,
> >
> > my story of the queries continues. I need to
> > run a set of action queries. This works fine.
> >
> > Several queries have parameters. So that means
> > the user has to watch the screen for a
> > parameter to pop-up. Is it possible to add
> > some parameter fields in the form to fill
> > the required parameters...WITHOUT changing
> > the query it self?
> >
> > So I have a parameters in my query:
> > [Enter version:]
> > [Enter startdate:]
> >
> > And I do not want to change these to:
> > forms!frmReport!txtVersion
> > forms!frmReport!txtStartDate
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com