displaying parameter in label

[gator]

Query2 gets it's data from Query1 parameter (Begin Date, End Date).
Report1 is based on Query2. Thus, Report1 prompts user for Begin Date and
End Date from Query1 and displays Query2. I want the get the earliest date
from that parameter and put the Month("yyyy") in either a Label or TextBox.
I tried...

Private Sub Report_Open(Cancel As Integer)

Me.Text16.Caption = DMin("[TranDate]", "TransactionsQuery")

End Sub

.....with no success. can anyone tell me the best way to do this? Can I
somehow get that value from the Begin Date prompt? Do I need to open the
Query? If I open the query, will I need to reenter the parameters?

 

[John Spencer MVP]

I am a little confused on what you want to do. You say month but have a
format to show year. You say you want the earliest date from the parameter
while I think you want the earliest actual date that appears in the report.

I would use a text box (not a label) and set its source to
= Format(Min(TranDate),"mmmm")

Replace mmmm with yyyy if you want the year
Replace mmmm with mmmm yyyy if you want the month and the year.

If you want the parameter then you could use
= Format([Begin Date],"mmmm")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[gator]

I put in the control source property

=Format(Min([TranDate]),"mmmm yyyy")

It prompts me for TranDate after I fill in Begin Date and End Date parameters.

Keep in mind, TranDate is not a field being reported from the Query. Should
I use the field and set visible as false. Or is there a way to just get the
value from the Query or the parameter prompt?

I am a little confused on what you want to do. You say month but have a
format to show year. You say you want the earliest date from the parameter
while I think you want the earliest actual date that appears in the report.

I would use a text box (not a label) and set its source to
= Format(Min(TranDate),"mmmm")

Replace mmmm with yyyy if you want the year
Replace mmmm with mmmm yyyy if you want the month and the year.

If you want the parameter then you could use
= Format([Begin Date],"mmmm")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Query2 gets it's data from Query1 parameter (Begin Date, End Date).
Report1 is based on Query2. Thus, Report1 prompts user for Begin Date and
End Date from Query1 and displays Query2. I want the get the earliest date
from that parameter and put the Month("yyyy") in either a Label or TextBox.
I tried...

Private Sub Report_Open(Cancel As Integer)

Me.Text16.Caption = DMin("[TranDate]", "TransactionsQuery")

End Sub

....with no success. can anyone tell me the best way to do this? Can I
somehow get that value from the Begin Date prompt? Do I need to open the
Query? If I open the query, will I need to reenter the parameters?

 

[gator]

I used the parameter function and it works good...thankx

I put in the control source property

=Format(Min([TranDate]),"mmmm yyyy")

It prompts me for TranDate after I fill in Begin Date and End Date parameters.

Keep in mind, TranDate is not a field being reported from the Query. Should
I use the field and set visible as false. Or is there a way to just get the
value from the Query or the parameter prompt?

I am a little confused on what you want to do. You say month but have a
format to show year. You say you want the earliest date from the parameter
while I think you want the earliest actual date that appears in the report.

I would use a text box (not a label) and set its source to
= Format(Min(TranDate),"mmmm")

Replace mmmm with yyyy if you want the year
Replace mmmm with mmmm yyyy if you want the month and the year.

If you want the parameter then you could use
= Format([Begin Date],"mmmm")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Query2 gets it's data from Query1 parameter (Begin Date, End Date).
Report1 is based on Query2. Thus, Report1 prompts user for Begin Date and
End Date from Query1 and displays Query2. I want the get the earliest date
from that parameter and put the Month("yyyy") in either a Label or TextBox.
I tried...

Private Sub Report_Open(Cancel As Integer)

Me.Text16.Caption = DMin("[TranDate]", "TransactionsQuery")

End Sub

....with no success. can anyone tell me the best way to do this? Can I
somehow get that value from the Begin Date prompt? Do I need to open the
Query? If I open the query, will I need to reenter the parameters?