WHERE clause in SQL statement (1 Viewer)

[willksr]

I've got a form that gets data from a table containing both deposit and withdrawal data So, in the SQL statement associated with a form I need to get only Deposit or only Withdrawal info. I put in a WHERE clause like this:

WHERE table.depwithfield = "Dep"

I get a missing operator error. What am I doing wrong?

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[CJ_London]

Not enough information to provide an answer

provide the full sql and data types of the table fields

 

[willksr]

Actually, I had the SELECT clause in the wrong place. I put it where it belongs and now I get a message asking me to enter the parameter value for "With"

 

[willksr]

Jon,
Here is the entire SELECT command. Everything works except the WHERE clause and the field it is referring to is Short Text. Forgive me for the long field names.

SELECT tblSavingsDepWith.[Savings DepWith Number], tblSavingsDepWith.[Savings DepWith Date], tblSavingsDepWith.[Savings DepWith Amount], tblSavingsDepWith.[Savings DepWith Who], tblSavingsDepWith.[Savings DepWith Cleared], tblSavingsDepWith.[Savings Account] FROM tblSavingsDepWith WHERE tblSavingsDepWith.[Savings DepWith Type]=”With” ORDER BY tblSavingsDepWith.[Savings Withdr Date];

willkr

 

[CJ_London]

Cross posted here

Adding a WHERE clause with a text modifier

I'm using a SELECT statement in a form to specify which data to get. One of the fields is a short text field that can be either "State" or "Local." I've tried a WHERE clause that says WHERE table.field= "State" to isolate only those records that have the locale set to State. I keep getting...

www.accessforums.net

 

[CJ_London]

Looks to me like your double quotes are the wrong character

suggest create the query in the query builder , move to the sql view and compare the characters

compare post 4 - "With"

With post 5 - ”With”

and in the cross post - ”With”

Correct character per post 4

 

[willksr]

The correct character is not the double quote. I have tried single quotes, double quotes, and double-double quotes. They all produce the same error. Access keeps asking me for the parameter value of "With"

I'm starting to get seriously frustrated here. Do no one know what I need to do?

 

[June7]

Well, the posted quotes are "smart" quotes - tilted. VBA won't like those so how did they get into your post? OTOH, apostrophes should work, as long as they aren't also "smart".

Next step is to provide your db for analysis. Follow instructions at bottom of my post.

 

[CJ_London]

Another possible reason is your naming convention. Although technically the square brackets should solve the problem of your spaces there are documented cases on this forum and elsewhere where it doesn’t - the result being an ambiguous error - I’m thinking in particular of the word ‘type’ which is a reserved word

from the other forum, your app is old and over the years access has become less tolerant of poor practices so it may be it was ok back then, perhaps not now.

have you tried running the sql as a query and not from your form?

Try removing the spaces from your field names - at least the type one

and if you want to cut down on you typing- alias the table

 

[willksr]

CJ, How do I run the SQL as a query? I'm not sure what that means.

 

[MajP]

I googled this a couple times and they all point to the issue of Smart Quotes. Do not build this in Word and paste in.

Why am I getting the Missing Operator error?

I keep getting the same error: Missing Operator. I have looked over this code three times and cannot find it. Can someone with a keen eye please help? WHERE ([Letter Status].[Letter_Status] = “Agr...

stackoverflow.com

MS-Access : Syntax error (missing operator) in query expression

I keep getting a syntax error (missing operator) in query expression for this formula in MS-Access. IIf([Employee List].[Employee Type]=”Employee”,Format([Employee List].[Date of Birth],'dd/mm/yyy...

stackoverflow.com

 

[CJ_London]

CJ, How do I run the SQL as a query? I'm not sure what that means.

On my phone but in the form recordsource property, click on the 3 dot carat to the right.

alternatively
1. Copy your sql
2. Open the query builder
3. Switch to the sql window
4. Paste your code
5. Try to execute or switch to the query builder view

 

[plog]

I've got a form that gets data from a table containing both deposit and withdrawal data So, in the SQL statement associated with a form I need to get only Deposit or only Withdrawal info. I put in a WHERE clause like this:

I think you're attempting to achieve this in the wrong manner.

I don't think the correct way to filter the data on your form is by using a query. Instead I think the correct why is with either:

DoCmd.OpenForm where you use it to open your form and provide a criteria string to limit the records to just the ones you want

DoCmd.OpenForm method (Access)

Office VBA reference topic

learn.microsoft.com

Form.Filter where you filter an already opened form by setting a filter string to show just the records you want

Form.Filter property (Access)

Office VBA reference topic

learn.microsoft.com

 

[willksr]

OK I solved it! I realized that all deposits are put in the table as positive amounts and all withdrawals are put in the table as negative amounts. So my WHERE clause is
WHERE tblSavingsDepWith.[Savings DepWith Amount] < 0
It works perfectly!

BTW the comments above about smart quotes is something that I never thought of. I do all my queries in Word and paste them into access. I'll have to go back and try the previous version without smart quotes to see if that is what has been messing me up.

 

[June7]

Why would you do queries in Word when you have query designer and SQL View?

 

[willksr]

Why do them in Word? Because the font is much easier to read and I'm not that familiar with query designer. (I'm in my late 70's and my vision isn't what it used to be even with glasses.) I'll have to spend more time with query designer. And, after changing the smart quotes to standard double quotes, my previous version works like a charm.

Thanks, guys for all your help!

 

[June7]

You can change the font and font size for query designer with File > Options > Object Designers > Query Design.

I turn 70 in a few days. Your comment prompted me to change this setting. Much better.

 

[KitaYama]

I turn 70 in a few days. Your comment prompted me to change this setting. Much better.

If you're on windows 10 or 11 there are two other options :

If you want to enlarge everything on screen on the whole :
Settings - System - Display - Scale

( you can also click scale and enter any percentages you want)




If you want to enlarge font only :
Settings - System - Display - Scale - Font

 

[willksr]

OK, I answered my own question. I have a custom ribbon in my app so I seldom see the standard ribbon. I have to start my app with the shift key depressed in order to see the standard ribbon.