[SOLVED] Selection to show in Report Page Header

wrightie · #1 06-25-2009, 05:31 AM

Hi ... I'm stumped again !

What I want to do is to have the dates that have been inputted on a form, appear in the Report page header.

The form is called Select_Inception. This form only has two text boxes called Start Date and End Date, they select on a field called PeriodFrom. There is also an OK button, this is the vba code I have used

Code:

Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb()

'Delete the existing dynamic query; trap the error if the query does not exist.
On Error Resume Next
db.QueryDefs.Delete ("Qry_By_Inception_Date")
On Error GoTo 0

If Not IsNull(Me![End Date]) Then
    where = where & " AND [PeriodFrom] between DateValue('" + _
    Me![Start Date] + "') AND DateValue('" & Me![End Date] & "')"
Else
    where = where & " AND [PeriodFrom] >= DateValue('" + Me![Start Date] _
    + "')"
End If

'MsgBox "Select * from Premium " & ("where " + Mid(where, 6) & ";")
Set QD = db.CreateQueryDef("Qry_By_Inception_Date", _
"SELECT * FROM Broker INNER JOIN Premium ON Broker.BrokerNo = Premium.BrokerNo " & (" where " + Mid(where, 6) & ";"))

DoCmd.OpenReport "Premium_by_Inception_Date", acViewPreview
End Sub

split_atom18 · #2 06-25-2009, 09:45 AM

Well there are 2 ways to do it, the one I would use is using TempVars.

On the form you enter the dates on make an ok key or something that when clicked does something like:

Code:

Private Sub OK_Click()
    Dim MaxBase As Double
    Dim MaxCash As Double
    Dim MaxEarlyOrder As Double
    Dim MaxEarlyShip As Double
    Dim MaxProgram As Double
    Dim MaxDiscount As Double

    MaxBase = Nz(Me.MaxBase, 0)
    MaxCash = Nz(Me.MaxCash, 0)
    MaxEarlyOrder = Nz(Me.MaxEarlyOrder, 0)
    MaxEarlyShip = Nz(Me.MaxEarlyShip, 0)
    MaxProgram = Nz(Me.MaxProgram, 0)
    MaxDiscount = (1 - MaxBase) * (1 - MaxCash) * (1 - MaxEarlyOrder) * (1 - (MaxEarlyShip + MaxProgram))
    
    TempVars.Add "MaxBase", MaxBase
    TempVars.Add "MaxCash", MaxCash
    TempVars.Add "MaxEarlyOrder", MaxEarlyOrder
    TempVars.Add "MaxEarlyShip", MaxEarlyShip
    TempVars.Add "MaxProgram", MaxProgram
    TempVars.Add "MaxSeedTenderDiscount", MaxDiscount
    
    Me.Visible = False
End Sub

wrightie · #3 06-25-2009, 09:56 AM

Hi Dan ,

We are on MS 2007 Basic, so for the time being still using Access 2003.

However I will keep this info for when we get round to upgrading, so thanks for that.

I don't suppose you have a 2003 option ?

wrightie

wrightie · #4 06-25-2009, 11:03 AM

I've tried this and get the text "For the period of to" . No error message so it's a tad better !

="For the period of" & Forms!Select_Inception![Start Date] & " to" & Forms!Select_Inception![End Date]

wrightie · #5 06-25-2009, 11:12 AM

Yep it worked. This code was ok, i just hadn't placed any dates in the form hence why it was just bringing up only the text.

="For the period of" & Forms!Select_Inception![Start Date] & " to" & Forms!Select_Inception![End Date]

Thanks yet again Dan for you help, I will remember the TempVars for when we upgrade to pro.

Cheers
wrightie

split_atom18 · #6 06-25-2009, 01:02 PM

Np, glad you figured it out.

TempVars are a fantastic addition.

Welcome to the Excel Forum

Forum Statistics