SQL Function value in text box

[popen_73]

Hi.
I have a function that runs a SQL SCRIPT to calculate a value from two other scripts:

Public Function sql_risk_rating()

Dim sql1 As String

sql1 = "SELECT qry_cf_value.device, [cm_value]*[cf_value] AS Risk_value" & _
"FROM qry_cf_value INNER JOIN qry_cm_value ON qry_cf_value.device = qry_cm_value.device" & _
"WHERE (((qry_cf_value.device)=[Forms]![frm_device_main]![txt_uid]));"

End Function

.........Easy enough.

Now, I want the value for this to show in a text box (unbound) against the specified item shown (current record).

COuld anybody give advice on this?

MAny thanks.

 

[Mile-O]

Queries return records, not values - lookup the Domain Aggregate Functions they will get the values from a query/table that you want. i.e DSum, DCount, DMax, DMin, etc.

 

[popen_73]

Thanks for your info.

Is it possible for the DSUM function to calculate from 2 domains?
The fields I need to calculate together are from two totals queries.

 

[Fizzio]

No but you can either
a) create a new query that does the new calculation for you and DSum that or
b) add your DSum values in the textbox
ie =Dsum(......)+Dsum(.......)

Be aware though that aggregate functions do slow a form down, so the more you have, the bigger the speed hit (especially if run via a network)

 

[popen_73]

Hi again.
Thanks for all your help, I have got it sorted now.
I have done a query to do the calculation then 'DSUM''d an unbound text box and it works a treat.

Thanks again,

-NEIL