?
Solved

Open report does not recognize number as filter ACCESS 2010 VBA

Posted on 2014-11-17
6
Medium Priority
?
407 Views
Last Modified: 2014-11-17
I have a procedure that worked when using the "=", but I changed to "IN".  However, I get a runtime errr orf 3075, Syntax error (missing operator) inquery expression ' DealID IN(29,16) Order by UpdateDate Asc'.  The statement looks fine in the debug window

DealID IN(29, 16) Order by UpdatedDate ASC

It looks fine so am not sure why it does not work when passing the filter in the open report statement.

Sandra
0
Comment
Question by:ssmith94015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 38

Expert Comment

by:PatHartman
ID: 40448087
When you have this type of question (syntax) it is impossible for us to do anything but guess when you don't post the actual query/code.

I'm going to guess that you are trying to supply the In() as a parameter and you can't do that.  If you want to use In(...) in a query, you will need to build the entire SQL string with VBA and run that.
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40448109
You can't have "DealID IN(29, 16) Order by UpdatedDate ASC" as a filter.
You can have "DealID IN (29, 16)"
Specify the ordering in Grouping and Sorting of the report. not (and never) in the source query.

/gustav
0
 

Author Comment

by:ssmith94015
ID: 40448110
Hi Pat

Actually, once I got rid of the Order By Clause it works in.  So perhaps the real question is in code, how do I sort the report dynamically?

Sandra
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:ssmith94015
ID: 40448113
The sort order can be selected by the user based on various date categories in the filter form.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40448126
If the report order is not dynamic, use the report's sorting and grouping properties to specify sort order.  Never do it in your query.  Access always rewrites the report's RecordSource query to eliminate columns it thinks you don't need and it ignores your sort order also.
0
 

Author Closing Comment

by:ssmith94015
ID: 40448139
Since this actually answered the question I asked, I am accepting it.  Am posting another question to resolve the dynamic sort problem.

Sandra
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Make the most of your online learning experience.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Simple Linear Regression
Suggested Courses

764 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