Syntax Error in Query Expression - Help

[Dalan]

Please help - just take a quick look at the function code below. It
probably just needs some minor ["] tweaking.

The function module is based on an intermediate query to provide a
group record total from a subform that is posted to a field on the
main form. "Everything seems to work fine" except that I consistently
receive an error everytime I attempt to create a new record on the
main form. However, there are no errors received when accessing
existing records.

It is a Run-Time Error 3075 - Synax error (missing operator) in query
expression 'IDRef=' . And when opening the module, the following is
highlighted: Set rst = CurrentDb.OpenR ecordset(strSQL ). Here is the
code structure of the module:

Function GetTotalAdditio nal(IDRef) As Currency

Dim rst As Recordset, strSQL As String

strSQL = "Select TotalAdditional FROM qryAddCost WHERE IDRef = " &
IDRef

Set rst = CurrentDb.OpenR ecordset(strSQL )

If rst.RecordCount = 0 Then
GetTotalAdditio nal = 0

Else
GetTotalAdditio nal = rst![TotalAdditional]

End If

rst.Close
Set rst = Nothing

Exit Function

End Function
The text box Control Source (AddCost) on the main form uses:
=[Forms]![frmMain]![sfrmAdditionalC osts]![TotalAdditional Cost]

On the subform (continuous forms) the field name is
(TotalAdditonal Cost) and the Control Source is set to:
=GetTotalAdditi onal([IDRef])

The query (qryAddCosts) contains the IDRef as Group By and Total
Additional which is the sum of 3 groups of cost.

Based on the table (tblAdditionalC osts).

I have tried many different things, especially restructuring the code,
but to no avail. Any assistance will be greatly appreciated. Thanks.

[bebelino]

On 28 Sep 2003 04:36:11 -0700, ot***@safe-mail.net (Dalan) wrote:

strSQL = "Select TotalAdditional FROM qryAddCost WHERE IDRef = " &
IDRef

for a start this one should be:
strSQL = "Select qryAddCost.Tota lAdditional FROM qryAddCost WHERE
qryAddCost.IDRe f = " & IDRef & ";"
--
bebelino

[Douglas J. Steele]

If you've only got one table or query in the FROM section of the query,
there's no need to propagate that name throughout the query. And the
semicolon at the end is optional.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
"bebelino" <a.*@c.d> wrote in message
news:hq******** *************** *********@4ax.c om...

On 28 Sep 2003 04:36:11 -0700, ot***@safe-mail.net (Dalan) wrote:

strSQL = "Select TotalAdditional FROM qryAddCost WHERE IDRef = " &
IDRef

for a start this one should be:
strSQL = "Select qryAddCost.Tota lAdditional FROM qryAddCost WHERE
qryAddCost.IDRe f = " & IDRef & ";"
--
bebelino

[Douglas J. Steele]

I suspect that the function isn't getting a valid value for IDRef, so that
strSQL is only getting Select TotalAdditional FROM qryAddCost WHERE IDRef =,
with nothing else after the equal sign.

Have you got a text box IDRef on the subform that's bound to the query field
IDRef? If so, try renaming the text box to something different than the
field name (say to txtIDRef)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
"Dalan" <ot***@safe-mail.net> wrote in message
news:50******** *************** ***@posting.goo gle.com...

Please help - just take a quick look at the function code below. It
probably just needs some minor ["] tweaking.

The function module is based on an intermediate query to provide a
group record total from a subform that is posted to a field on the
main form. "Everything seems to work fine" except that I consistently
receive an error everytime I attempt to create a new record on the
main form. However, there are no errors received when accessing
existing records.

It is a Run-Time Error 3075 - Synax error (missing operator) in query
expression 'IDRef=' . And when opening the module, the following is
highlighted: Set rst = CurrentDb.OpenR ecordset(strSQL ). Here is the
code structure of the module:

Function GetTotalAdditio nal(IDRef) As Currency

Dim rst As Recordset, strSQL As String

strSQL = "Select TotalAdditional FROM qryAddCost WHERE IDRef = " &
IDRef

Set rst = CurrentDb.OpenR ecordset(strSQL )

If rst.RecordCount = 0 Then
GetTotalAdditio nal = 0

Else
GetTotalAdditio nal = rst![TotalAdditional]

End If

rst.Close
Set rst = Nothing

Exit Function

End Function
The text box Control Source (AddCost) on the main form uses:
=[Forms]![frmMain]![sfrmAdditionalC osts]![TotalAdditional Cost]

On the subform (continuous forms) the field name is
(TotalAdditonal Cost) and the Control Source is set to:
=GetTotalAdditi onal([IDRef])

The query (qryAddCosts) contains the IDRef as Group By and Total
Additional which is the sum of 3 groups of cost.

Based on the table (tblAdditionalC osts).

I have tried many different things, especially restructuring the code,
but to no avail. Any assistance will be greatly appreciated. Thanks.

[bebelino]

On Sun, 28 Sep 2003 12:34:36 GMT, "Douglas J. Steele"
<dj******@canad a.com> wrote:

If you've only got one table or query in the FROM section of the query,
there's no need to propagate that name throughout the query. And the
semicolon at the end is optional.

didn't know that. Thanks,
--
bebelino

[Bob Quintal]

bebelino <a.*@c.d> wrote in
news:2t******** *************** *********@4ax.c om:

On Sun, 28 Sep 2003 12:34:36 GMT, "Douglas J. Steele"
<dj******@canad a.com> wrote:

If you've only got one table or query in the FROM section of
the query, there's no need to propagate that name throughout
the query. And the semicolon at the end is optional.

didn't know that. Thanks,

Actually, if the fieldname is distinct across all the tables and
queries in the from section of your query, you dont need to
propagate the source name.

Beware however that aliasing names isn't protection against the
distinct(ive)ne ss of a name. I once had a query return an error
message because a name five queries under the called one violated
the rule. Took quite a while to locate that problem.

Bob Q

[Bob Quintal]

Chuck Grimsby <c.*******@worl dnet.att.net.in valid> wrote in
news:iq******** *************** *********@4ax.c om:

On Sun, 28 Sep 2003 20:34:00 GMT, Bob Quintal
<bq******@gener ation.net> wrote:

Actually, if the fieldname is distinct across all the tables
and queries in the from section of your query, you dont need
to propagate the source name.
Beware however that aliasing names isn't protection against
the distinct(ive)ne ss of a name. I once had a query return an
error message because a name five queries under the called one
violated the rule. Took quite a while to locate that problem.

I go back and forth on field names in the various tables and
queries I create for that reason.

On the one hand, I think that for clarities sake, two related
fields in two different tables should have the same name, but
then I run into the problem you mentioned on some of the
queries.

Aliasing in the table name helps some (more so then
propagating for me), but it can also confuse. So I go back on
forth on this issue.

A programmer I know assigns a prefix to tables and to fields
within. Table T3014_Item_Defi nitions contained Fields
C3014PK_ItemNum ber, C3014_Descripti on, N3014_ClassKey, N3014_UM.

Table T3022_Item_Grou ps contained N3022PK_Item_Gr oup,
C3022_Descripti on, etc.

Derived fields in queries would get QN0761_Extended Qty

Open a querydef based on querydefs and you could still tell exactly
where everything came from.

I wish I had the discipline to follow his convention.

Bob Q

[Tony Toews]

Bob Quintal <bq******@gener ation.net> wrote:

A programmer I know assigns a prefix to tables and to fields
within. Table T3014_Item_Defi nitions contained Fields
C3014PK_ItemNu mber, C3014_Descripti on, N3014_ClassKey, N3014_UM.

Table T3022_Item_Grou ps contained N3022PK_Item_Gr oup,
C3022_Descript ion, etc.

Derived fields in queries would get QN0761_Extended Qty

Open a querydef based on querydefs and you could still tell exactly
where everything came from.

Interesting idea.

I use table initials as field prefixs. So a field name for ItemDefinitions would be
idID, idDescription, idClassKey. Or ItemGroups would be igID, igDescription,
igInactiveQ and so forth.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Dalan]

"Douglas J. Steele" <dj******@canad a.com> wrote in message news:<5E******* ************@ne ws04.bloor.is.n et.cable.rogers .com>...

I suspect that the function isn't getting a valid value for IDRef, so that
strSQL is only getting Select TotalAdditional FROM qryAddCost WHERE IDRef =,
with nothing else after the equal sign.

Have you got a text box IDRef on the subform that's bound to the query field
IDRef? If so, try renaming the text box to something different than the
field name (say to txtIDRef)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
"Dalan" <ot***@safe-mail.net> wrote in message
news:50******** *************** ***@posting.goo gle.com...

Please help - just take a quick look at the function code below. It
probably just needs some minor ["] tweaking.

The function module is based on an intermediate query to provide a
group record total from a subform that is posted to a field on the
main form. "Everything seems to work fine" except that I consistently
receive an error everytime I attempt to create a new record on the
main form. However, there are no errors received when accessing
existing records.

It is a Run-Time Error 3075 - Synax error (missing operator) in query
expression 'IDRef=' . And when opening the module, the following is
highlighted: Set rst = CurrentDb.OpenR ecordset(strSQL ). Here is the
code structure of the module:

Function GetTotalAdditio nal(IDRef) As Currency

Dim rst As Recordset, strSQL As String

strSQL = "Select TotalAdditional FROM qryAddCost WHERE IDRef = " &
IDRef

Set rst = CurrentDb.OpenR ecordset(strSQL )

If rst.RecordCount = 0 Then
GetTotalAdditio nal = 0

Else
GetTotalAdditio nal = rst![TotalAdditional]

End If

rst.Close
Set rst = Nothing

Exit Function

End Function
The text box Control Source (AddCost) on the main form uses:
=[Forms]![frmMain]![sfrmAdditionalC osts]![TotalAdditional Cost]

On the subform (continuous forms) the field name is
(TotalAdditonal Cost) and the Control Source is set to:
=GetTotalAdditi onal([IDRef])

The query (qryAddCosts) contains the IDRef as Group By and Total
Additional which is the sum of 3 groups of cost.

Based on the table (tblAdditionalC osts).

I have tried many different things, especially restructuring the code,
but to no avail. Any assistance will be greatly appreciated. Thanks.

Thanks Doug and all for your input. I finally resolved the problem by
including in the code: If Not IsNull(IDRef) Then

I guess that I should listen to Allen a bit more regarding those pesky
"nulls" - lol.