report parameter

[Dan Kean]

Hi all,

I am creating a report which I am having some difficulties with on a few
technical bits,

when the report is ran it asks for the "billing date" which I enter and is
shown on the report - I need to use this to select which "rate" (field in
another table) then gets displayed. However I also have a "billed in" field
which is the currency to bill in and this must be used to select which
"rate" to display. I assume this can be done with an expression but nothing
I'm trying is working?

Basically I want to enter a value when the report is opened and from that
value select the related "rate" and related "currency"

hopefully someone willunderstand what I mean!

Thanx in advance for any help,

Dan

[pietlinden]

How do you know what rate and what currency apply to the report? Is
this in another table? Seems like you've left out some key
information... because from what you've described, there should be
another table or query or something that joins these two pieces
together, and you don't describe it. Maybe take a step back and
explain (1) what you are trying to accomplish, (2) what relevant
tables/queries you have, and (3) what you've tried so far. If this is
just a matter of collecting bits of information to feed to the report,
you can use an unbound form for that...

[jimfortune]

Dan Kean wrote:

when the report is ran it asks for the "billing date" which I enter and is shown on the report - I need to use this to select which "rate" (field in another table) then gets displayed. However I also have a "billed in" field which is the currency to bill in and this must be used to select which "rate" to display. I assume this can be done with an expression but nothing I'm trying is working?

Basically I want to enter a value when the report is opened and from that value select the related "rate" and related "currency"

Use the Report_Open event. Use a recordset with SQL containing the
BillingDate surrounded by # signs to get the information you want.
Then use select case on the [billed in] field to change the display:

Something like:
'...
Dim Response As Variant
Dim strPrompt As String
Dim strTitle As String

strTitle = "Get Billing Date"
strPrompt = "Click OK or Type in a Billing Date"
Response = InputBox(strPrompt, strTitle, CStr(Date()))
lblBillingDate.Caption = Nz(Response, "")

'...
strSQL = "SELECT Rate, [billed in] AS BilledIn FROM tblRateHistory
WHERE theDate = #" & lblBillingDate.Caption & "#;"
dblRate = 0
strBilledIn = ""
If MyRS.RecordCount > 0 Then
MyRS.MoveFirst
dblRate = MyRS("Rate")
strBilledIn = MyRS("BilledIn")
End If
'...
If dblRate <> 0 Then
Select Case strBilledIn
Case "Euro":
lblBillingRate.Caption = Format(dblRate, "#.##0,00") & " Euros / hr"
Case "Doubloon":
lblBillingRate.Caption = Format(dblRate, "#,##0.00") & " Doubloons /
hr"
Case Else
lblBillingRate.Caption = "Currency type not available."
End Select
Else
txtBillingRate.Caption = "Rate info not available."
End If
DoEvents
'...

SQL might want the date to be Americanized. Test before using.

James A. Fortune

....just remember the user interface in Access uses the local Control
Panel settings to interpret dates typed into the user interface. --
Allen Browne