Using a query result in a report that isn't in the report datasource

[lution]

I have a report (Default Community Service) that has a saved query as its datasource (Default Notices). However, I have 5 users that all have different indigency times that I need to put into the report. I am currently using a report variable in an unbound textbox to get the indigency trial time (Enter Indigency Time):

="2. Appearing on " & Format([indigency hearing],"Long Date") & ", at " & [Enter Indigency Time] & ", in person, at " & IIf(IsNull([court address1])," ",[court address1] & " ") & IIf(IsNull([court address2])," ",[court address2] & " ") & [jurisdiction.city] & ", " & [longstate] & "."

However, I have a preferences table tblPreferences and I'd like to move away from the report variable and put it into my preferences table so the users don't have to type the time in each time they run the report. tblPreferences has the following columns:

preferenceID - autonumber
txtPreferenceName - string, name of the preference
txtDescription - description of the preference
memPreferenceValue - memofield with the preference value

In this case I need a single record from the preference table (IndigencyAppearanceTime) and would like to replace the report parameter [Enter Indigency Time] with the value from the query.

What do I need to do to swap them out?

Thanks
Lution

 

[stopher]

Hi Lution

Are you saying that IndigencyAppearanceTime is another column in the preference table and you want to get the IndigencyAppearanceTime from the table for a given PreferenceID?

Typically you would use the DLOOKUP function to look up values from tables like this. The only thing is where do you get PreferenceID from?

Suppose PreferenceID is a textbox on your form Form1. Then you could replace the [Enter Indigency Time] bit with:

dlookup("[IndigencyAppearanceTime]","tblPreferences","PreferenceID=" & form1!preferenceID)

Literally this reads... get the value of IndigencyAppearanceTime from tblPreferences where PreferenceID= the value in textbox preferenceID

hth
Stopher

 

[lution]

No. I have a table tblPreferences which has the following columns:

preferenceID - autonumber
txtPreferenceName - string, name of the preference
txtDescription - description of the preference
memPreferenceValue - memofield with the preference value

One of the rows in the table is:
preferenceID - 1
txtPreferenceName - IndigencyAppearanceTime
txtDescription - Time defendant should appear for indigency hearings
memPreferenceValue - some time set by the user, say 10 a.m.

I'd like to get the 10 a.m. memPreferenceValue from the record and include it as part of a string in an unbound text box on a report. The report's datasource is on another group of tables that doesn't include tblPreferences.

The current value of the unbound text box is:

="2. Appearing on " & Format([indigency hearing],"Long Date") & ", at " & [Enter Indigency Time] & ", in person, at " & IIf(IsNull([court address1])," ",[court address1] & " ") & IIf(IsNull([court address2])," ",[court address2] & " ") & [jurisdiction.city] & ", " & [longstate] & "."

I want to replace the runtime variable [Enter Indigency Time] with the memPreferenceValue where txtPreferenceName = "IndigencyAppearanceTime" so the user doesn't have to input the value every time they run the report. But, I haven't been able to figure out how to rebuild the value of the unbound text box or which report event to use to build it.

 

[stopher]

Replace [Enter Indigency Time] bit with:

dlookup("[memPreferenceValue]","tblPreferences","txtPreferenceName='IndigencyAppearanceTime' ")

So the full expression is:

="2. Appearing on " & Format([indigency hearing],"Long Date") & ", at " & dlookup("[memPreferenceValue]","tblPreferences","txtPreferenceName='IndigencyAppearanceTime' ") & ", in person, at " & IIf(IsNull([court address1])," ",[court address1] & " ") & IIf(IsNull([court address2])," ",[court address2] & " ") & [jurisdiction.city] & ", " & [longstate] & "."

hth
Stopher

 

[lution]

Bingo, thanks stopher.