Solved

Access 2010 Run-Time Error 3075

Posted on 2014-10-10
6
217 Views
Last Modified: 2014-10-10
I'm using the code below and get error code 3075 -- Syntax error (missing operator) in query expression '[Budgetary_Dr]='(4287001',45900001'). Any Thoughts

Dim strList As String


strList = "('" & Replace(Me.Budgetary_DR, " ", "','") & "')"

If DCount("*", "tbl_USSGL", "[Budgetary_DR]='" & strList & "'") = 0 Then
     MsgBox "No related record found", vbExclamation, "ATM"
     Exit Sub
 Else
End If
DoCmd.OpenForm "frm_USSGL", , , "[USSGL Child] In " & strList
0
Comment
Question by:shieldsco
  • 2
  • 2
  • 2
6 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 40373439
Looks like the quotes are mis-aligned:

'[Budgetary_Dr]='(4287001',45900001')

maybe this?

[Budgetary_Dr]='(4287001,45900001)'
?

To help out, just before the DoCmd, put this:

Debug.Print strList

And you can look in the Immediate window (open from the View menu) and you'll see the value.
Or set a breakpoint there and examine strList to ensure that it's correct.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 40373453
Another thought is to take the DCount, DCount("*", "tbl_USSGL", "[Budgetary_DR]='" & strList & "'")
And turn it into a query to make sure it works, something like:

SELECT * from tbl_USSGL
WHERE [Budgetary_DR]=' {contents of strList} '

test that in the query builder
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40373537
first check strList values to be in this format

'4287001','45900001'

values are wrapped in single quotes (')

then use this syntax

If DCount("*", "tbl_USSGL", "[Budgetary_DR] In (" & strList & ")") = 0 Then


----

DoCmd.OpenForm "frm_USSGL", , , "[USSGL Child] In (" & strList & ")"
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40373601
correction..

strList = "('" & Replace(Me.Budgetary_DR, " ", "','") & "')"
'add the line below to check the format of strList

debug.print strList

the result should be

('4287001','45900001')

so change the syntax i posted above with

If DCount("*", "tbl_USSGL", "[Budgetary_DR] In " & strList) = 0 Then

----

DoCmd.OpenForm "frm_USSGL", , , "[USSGL Child] In " & strList
0
 

Author Comment

by:shieldsco
ID: 40373657
Rey - run time error 2471 The expression you entered as a query parameter produced tis error: '[Budgetary_DR]''

If DCount("*", "tbl_USSGL", "[Budgetary_DR] In " & strList) = 0 Then
0
 

Author Closing Comment

by:shieldsco
ID: 40373701
Very Good
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now