Solved

Open report does not recognize number as filter ACCESS 2010 VBA

Posted on 2014-11-17
6
389 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
  • 3
  • 2
6 Comments
 
LVL 35

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 49

Accepted Solution

by:
Gustav Brock earned 500 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 35

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is about my first experience with programming Arduino.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

785 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