Execute sql

[Fathi via AccessMonster.com]

Hello,
I have this sql

"SELECT Sum(Ou_Detail.[Delivery quantityTOTAL])" _
& "FROM Outbound_Detail" _
& "WHERE (((Ou_Detail.Material)" _
& "In (SELECT M_MatInfo.Material FROM M_MatInfo" _
& "WHERE M_MatInfo.[Serial No Profile]= " & "4" & "))" _
& "AND ((Ou_Detail.YYYYMM)=" _
& "[forms]![form_total_volume]![f_Period])" _
& "AND ((Ou_Detail.WRHS)=[forms]![form_total_volume]![f_Plant]))
;"



how i can run this statment?
the result will be stored in a field named "total_qty" in the form
"form_total_volume" , how i can do this?

when i open the form i want to be able to press button to return the result
from the sql statment into text box. considering that i have parameter in the
form, i already included the parameters in the sql statment, do you think
this will work?

any help?

 

[Brian]

Use DSum domain aggregate instead of SELECT Sum. It looks like this:

total_qty = Dsum("Field1","Table1","Field2 = 9")

The three arguments are the field to lookup, the table, and the where
clause. Check the Access help on DSum.

This is somewhatover-simplified. In reality, you will need to adjust your
SQL to correctly get the nesting correctly in your WHERE clause (you may need
a DLookup nested inside your DSum).

Alternatively, just build a query using your SQL statement (it will take a
couple of adjustments to work in Access), then do a DFirst or DLookup to pull
the result.

total_qty = DFirst("Total","myQuery")

or this

total_qty = DLookup("Total","myQuery")


(assuming that you have built your query so it returns a single record that
is equal to the Sum you want).

 

[fathi_abuayyash via AccessMonster.com]

although my query reads data from the form? will it still work?
let me try it and i hope it will work?
thanks

Use DSum domain aggregate instead of SELECT Sum. It looks like this:

total_qty = Dsum("Field1","Table1","Field2 = 9")

The three arguments are the field to lookup, the table, and the where
clause. Check the Access help on DSum.

This is somewhatover-simplified. In reality, you will need to adjust your
SQL to correctly get the nesting correctly in your WHERE clause (you may need
a DLookup nested inside your DSum).

Alternatively, just build a query using your SQL statement (it will take a
couple of adjustments to work in Access), then do a DFirst or DLookup to pull
the result.

total_qty = DFirst("Total","myQuery")

or this

total_qty = DLookup("Total","myQuery")

(assuming that you have built your query so it returns a single record that
is equal to the Sum you want).

Hello,
I have this sql

[quoted text clipped - 19 lines]

any help?

 

[Dale Fye]

I don't think this will work as written. You have encapsulated the
reference to your form in quotes, and quotes are not needed. Also, your In
( ) clause implies that the [Serial No Profile] field is a string, but you
don't have it wrapped properly to look for a string. I think it should look
like:

strSQL = "SELECT Sum(Ou_Detail.[Delivery quantityTOTAL]) as Tot " _
& "FROM Outbound_Detail " _
& "WHERE Ou_Detail.Material IN (SELECT M_MatInfo.Material " _
& "FROM
M_MatInfo " _
& "WHERE
M_MatInfo.[Serial No Profile] = '4') " _
& " AND Ou_Detail.YYYYMM=" &
[forms]![form_total_volume]![f_Period] _
& " AND Ou_Detail.WRHS= " &
[forms]![form_total_volume]![f_Plant]

If the [Serial No Profile] field is numeric, then replace '4' with 4.

HTH
Dale

 

[Brian]

Yes. You can refer to any control on any open form in a query. Just enter it
in the criteria section of the query like this:

Field: Ou_Detail.YYYYMM
Criteria: [forms]![form_total_volume]![f_Period]

Field: Ou_Detail.WRHS
Criteria: [forms]![form_total_volume]![f_Plant]

These are reference to the value of the f_Period & f_Plant controls at the
moment the query is run.

I think the simplest plan is to get your query working in the Access query
builder, then run the DFirst against that query.

although my query reads data from the form? will it still work?
let me try it and i hope it will work?
thanks

Use DSum domain aggregate instead of SELECT Sum. It looks like this:

total_qty = Dsum("Field1","Table1","Field2 = 9")

The three arguments are the field to lookup, the table, and the where
clause. Check the Access help on DSum.

This is somewhatover-simplified. In reality, you will need to adjust your
SQL to correctly get the nesting correctly in your WHERE clause (you may need
a DLookup nested inside your DSum).

Alternatively, just build a query using your SQL statement (it will take a
couple of adjustments to work in Access), then do a DFirst or DLookup to pull
the result.

total_qty = DFirst("Total","myQuery")

or this

total_qty = DLookup("Total","myQuery")

(assuming that you have built your query so it returns a single record that
is equal to the Sum you want).

Hello,
I have this sql

[quoted text clipped - 19 lines]

any help?

 

[fathi_abuayyash via AccessMonster.com]

Thanks all

I don't think this will work as written. You have encapsulated the
reference to your form in quotes, and quotes are not needed. Also, your In
( ) clause implies that the [Serial No Profile] field is a string, but you
don't have it wrapped properly to look for a string. I think it should look
like:

strSQL = "SELECT Sum(Ou_Detail.[Delivery quantityTOTAL]) as Tot " _
& "FROM Outbound_Detail " _
& "WHERE Ou_Detail.Material IN (SELECT M_MatInfo.Material " _
& "FROM
M_MatInfo " _
& "WHERE
M_MatInfo.[Serial No Profile] = '4') " _
& " AND Ou_Detail.YYYYMM=" &
[forms]![form_total_volume]![f_Period] _
& " AND Ou_Detail.WRHS= " &
[forms]![form_total_volume]![f_Plant]

If the [Serial No Profile] field is numeric, then replace '4' with 4.

HTH
Dale

Hello,
I have this sql

[quoted text clipped - 22 lines]

any help?