By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,501 Members | 831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,501 IT Pros & Developers. It's quick & easy.

Syntax Error in Query Expression - Help

P: n/a
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.OpenRecordset(strSQL). Here is the
code structure of the module:

Function GetTotalAdditional(IDRef) As Currency

Dim rst As Recordset, strSQL As String

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

Set rst = CurrentDb.OpenRecordset(strSQL)

If rst.RecordCount = 0 Then
GetTotalAdditional = 0

Else
GetTotalAdditional = 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]![sfrmAdditionalCosts]![TotalAdditionalCost]

On the subform (continuous forms) the field name is
(TotalAdditonalCost) and the Control Source is set to:
=GetTotalAdditional([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 (tblAdditionalCosts).

I have tried many different things, especially restructuring the code,
but to no avail. Any assistance will be greatly appreciated. Thanks.
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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.TotalAdditional FROM qryAddCost WHERE
qryAddCost.IDRef = " & IDRef & ";"
--
bebelino
Nov 12 '05 #2

P: n/a
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.com...
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.TotalAdditional FROM qryAddCost WHERE
qryAddCost.IDRef = " & IDRef & ";"
--
bebelino

Nov 12 '05 #3

P: n/a
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.google.c om...
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.OpenRecordset(strSQL). Here is the
code structure of the module:

Function GetTotalAdditional(IDRef) As Currency

Dim rst As Recordset, strSQL As String

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

Set rst = CurrentDb.OpenRecordset(strSQL)

If rst.RecordCount = 0 Then
GetTotalAdditional = 0

Else
GetTotalAdditional = 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]![sfrmAdditionalCosts]![TotalAdditionalCost]

On the subform (continuous forms) the field name is
(TotalAdditonalCost) and the Control Source is set to:
=GetTotalAdditional([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 (tblAdditionalCosts).

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

Nov 12 '05 #4

P: n/a
On Sun, 28 Sep 2003 12:34:36 GMT, "Douglas J. Steele"
<dj******@canada.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
Nov 12 '05 #5

P: n/a
bebelino <a.*@c.d> wrote in
news:2t********************************@4ax.com:
On Sun, 28 Sep 2003 12:34:36 GMT, "Douglas J. Steele"
<dj******@canada.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)ness 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
Nov 12 '05 #6

P: n/a
Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in
news:iq********************************@4ax.com:
On Sun, 28 Sep 2003 20:34:00 GMT, Bob Quintal
<bq******@generation.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)ness 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_Definitions contained Fields
C3014PK_ItemNumber, C3014_Description, N3014_ClassKey, N3014_UM.

Table T3022_Item_Groups contained N3022PK_Item_Group,
C3022_Description, etc.

Derived fields in queries would get QN0761_ExtendedQty

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


Nov 12 '05 #7

P: n/a
Bob Quintal <bq******@generation.net> wrote:
A programmer I know assigns a prefix to tables and to fields
within. Table T3014_Item_Definitions contained Fields
C3014PK_ItemNumber, C3014_Description, N3014_ClassKey, N3014_UM.

Table T3022_Item_Groups contained N3022PK_Item_Group,
C3022_Description, etc.

Derived fields in queries would get QN0761_ExtendedQty

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
Nov 12 '05 #8

P: n/a
"Douglas J. Steele" <dj******@canada.com> wrote in message news:<5E*******************@news04.bloor.is.net.ca ble.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.google.c om...
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.OpenRecordset(strSQL). Here is the
code structure of the module:

Function GetTotalAdditional(IDRef) As Currency

Dim rst As Recordset, strSQL As String

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

Set rst = CurrentDb.OpenRecordset(strSQL)

If rst.RecordCount = 0 Then
GetTotalAdditional = 0

Else
GetTotalAdditional = 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]![sfrmAdditionalCosts]![TotalAdditionalCost]

On the subform (continuous forms) the field name is
(TotalAdditonalCost) and the Control Source is set to:
=GetTotalAdditional([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 (tblAdditionalCosts).

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.
Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.