select query error in control source of a text box

[skoolz]

Hi All, is it possible to use a sum select query in the control source field of a text box. I have a query that works fine in a combo text box but I keep getting a 'syntax error with subquery parenthesis' when I try and build on in a normal text box.

I am building the query using query design view and the query works and then I'm copying the SQL code behind the query into the text box field and access reformats it s a bit so not sure it's that's the reason.

R_P_Data_P = Table
approvalNosys = Field in the table
status = Field in the table
score = Field in the table
cmrOverview = Form
[approvalNoSys] = field in form
1 is the criteria

SELECT R_P_Data_P.approvalNosys, R_P_Data_P.status, Sum(R_P_Data_P.score) AS SumOfscore
FROM R_P_Data_P
GROUP BY R_P_Data_P.approvalNosys, R_P_Data_P.status
HAVING (((R_P_Data_P.approvalNosys)=[forms]![cmrOverview]![approvalNoSys]) AND ((R_P_Data_P.status)="1"));

You help is much appreciated as my head now aches...

 

[CJ_London]

no, you can't use queries in this way - you need to use the dsum function - something like

=DSum("P.score","R_P_Data_P","approvalNosys=" & me.approvalNoSys & " AND status=1")

This assumes both fields in the criteria are numeric

 

[skoolz]

no, you can't use queries in this way - you need to use the dsum function - something like

=DSum("P.score","R_P_Data_P","approvalNosys=" & me.approvalNoSys & " AND status=1")

This assumes both fields in the criteria are numeric

Many thanks for the reply and direction steer CJ, however over the past hour i have been trying to make the DSum work but access not accepts the expression but the rendered value in the field just returns error. I know from running the query using query design that the sum result should be 7.

I have tried copying the usage example below from msdn.microsoft.com/en-us/library/office/ff193998.aspx and tailoring it but still no avail.

variable = DSum("[FieldName]", "TableName", "[Criteria1] = " & Forms![FormName]![Control1] _
& " AND [Criteria2] = '" & Forms![FormName]![Control2] & "'" _
& " AND [Criteria3] =#" & Forms![FormName]![Control3] & "#")

This is my latest variation of the expression as i have tried quite a few different ways that access accepted but the field showed an error or was just blank.

=DSum("[score]","R_P_Data_P","[approvalNosys] = '" & [Forms]![cmrOverview]![approvalNoSys] & "' And [Status]=1")

approvalNoSys = string
1 = number

Thanks in advance

 

[CJ_London]

you said in your original post

[approvalNoSys] = field in form

I presumed you meant this is the name of the control in the form?

and is the approvalNoSys field in your table numeric or a string?

 

[skoolz]

My initial post was based on me trying to use a query but it seems that using a expression identifies field and table values differently ie brackets or no brackets.
So in my latest post what I'm trying to say is Dsum the Score field, in the table R_P_Data_P for the records where approvalNosys field in the same table = approvalNoSys on the cmrOverview form field AND where status field in the same table = 1

Thanks ..

 

[CJ_London]

square brackets are used if you have spaces or non alphanumeric characters (other than _) in your field or table names or those names are reserved words. If access is generating the code, then it will add square brackets 'to be safe'

It is generally better not to use spaces, non alphanumeric characters or reserved words because even with square brackets, unexpected errors can still be generated.

You haven't actually answered my questions so I can't provide any more suggestions. So to restate the questions plus some more

1. what is the name of the control (not field) in your cmrOverview form which you want to compare with the approvalNosys field in your R_P_Data_P table?
2. what field type is the approvalNosys field in your R_P_Data_P? Text, number, date?
3. what field type is the status field in your R_P_Data_P? Text, number, date?
4. for completeness - what is the name of the control where you are putting the dsum calculation, and is it in your cmrOverview form?
5. are you using a subform in your cmrOverview form?

 

[skoolz]

square brackets are used if you have spaces or non alphanumeric characters (other than _) in your field or table names or those names are reserved words. If access is generating the code, then it will add square brackets 'to be safe'

It is generally better not to use spaces, non alphanumeric characters or reserved words because even with square brackets, unexpected errors can still be generated.

You haven't actually answered my questions so I can't provide any more suggestions. So to restate the questions plus some more

1. what is the name of the control (not field) in your cmrOverview form which you want to compare with the approvalNosys field in your R_P_Data_P table?
2. what field type is the approvalNosys field in your R_P_Data_P? Text, number, date?
3. what field type is the status field in your R_P_Data_P? Text, number, date?
4. for completeness - what is the name of the control where you are putting the dsum calculation, and is it in your cmrOverview form?
5. are you using a subform in your cmrOverview form?

1. what is the name of the control (not field) in your cmrOverview form which you want to compare with the approvalNosys field in your R_P_Data_P table? =
approvalNosys (bound control name) or txtappNoSys (the textbox name) using either works.
2. what field type is the approvalNosys field in your R_P_Data_P? Text, number, date? = alphanumeric
3. what field type is the status field in your R_P_Data_P? Text, number, date? = Text

4. for completeness - what is the name of the control where you are putting the dsum calculation, and is it in your cmrOverview form? txtTotalScore

5. are you using a subform in your cmrOverview form? Yes

Dude, I finally got it to work but I think it's mostly due to your questions. As I was responding to each question, question 3 lit an Eurika light bulb in my head. The field type for status is text in the R_P_Data_P table however as the combo control that saves data to that table is bound to the id which is 1 instead of the word 'Active'. So even though I was looking for the value 1 in the table, the field type is text so I have to reference status as such by using '1'.

The control was on the a tab subform of cmrOverview but it seems that it's fine to reference the control whether it's on the mainform or subform by just referencing the mainform because in my code below txtappNoSys is on a subform of cmrOverview.

Here is my working code incase this helps others

=DSum("[score]","R_P_Data_P","[approvalNoSys] = '" & [Forms]![cmrOverview]![txtappNoSys] & "' And [Status]= '1'")

 

[CJ_London]

glad you got it sorted