Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-20-2019, 03:43 PM   #1
elly.khanlar
Newly Registered User
 
Join Date: Oct 2019
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
elly.khanlar is on a distinguished road
Remove some lines from query

Hi everyone, I have a make a union query which I am trying to union two tables together.
In one of the tables, I don't want two things to be included in two fields in Table A that contain certain criteria.

Here is my SQL which is not working and I get this error" Undefined function "Where" in expression.

here is my

SELECT field 1, field 2, field 3, field 4

From table A
WHERE(table A.FIELD 2])<>"Syrups")

OR

WHERE((table A.field 4])<>1)


UNION ALL SELECT *
FROM [table b)

elly.khanlar is offline   Reply With Quote
Old 10-20-2019, 03:54 PM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,455
Thanks: 0
Thanked 571 Times in 567 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Remove some lines from query

Remove all parentheses as they are not needed in this case. First version has odd number of parens - they must be in pairs. Names have space so need []. You have unpaired [ and ]. Advise not to use space nor punctuation/special characters in naming convention. This effort would be greatly simplified if you followed that guidance.

SELECT [field 1], [field 2], [field 3], [field 4] FROM [table A] WHERE [FIELD 2]<>"Syrups"
UNION ALL SELECT * FROM [table b];

Does tableB have only 4 fields all in same order as TableA?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 10-20-2019, 05:08 PM   #3
elly.khanlar
Newly Registered User
 
Join Date: Oct 2019
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
elly.khanlar is on a distinguished road
Re: Remove some lines from query

yes, table b has exactly 4 fields. how about same conditions?

basically, i don't want to include rows that has syrup in field 2 or field 4 has number 1 .

elly.khanlar is offline   Reply With Quote
Old 10-20-2019, 05:32 PM   #4
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,455
Thanks: 0
Thanked 571 Times in 567 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Remove some lines from query

Apply WHERE clause in both SELECT lines if you want to restrict records from both tables.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 10-20-2019, 09:19 PM   #5
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Remove some lines from query

firstly on your second union query, Specify the fieldnames as you specify on the first table even if you have same number of fields.
you'll get unexpected result when the order of fields on the second table is not as on your first table:

SELECT [field 1], [field 2], [field 3], [field 4]
From [table A]
WHERE(([table A].[FIELD 2])<>"Syrups")
OR
WHERE(([table A].[field 4])<>1)
UNION ALL
SELECT [field 1], [field 2], [field 3], [field 4]
FROM [table b]
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-20-2019, 09:22 PM   #6
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,455
Thanks: 0
Thanked 571 Times in 567 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Remove some lines from query

Except don't repeat keyword WHERE.

However, I doubt that OR with <> will produce desired output. But try it.

Consider:

SELECT [field 1], [field 2], [field 3], [field 4] FROM [table A]
WHERE [FIELD 2]<>"Syrups" AND [Field 4] IN (SELECT [Field 4] FROM [table A] WHERE [Field 4]<>1)

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 10-20-2019 at 09:30 PM.
June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Please help! I need to remove characters from a memo field, and enter new lines Urbane Queries 20 09-16-2018 03:01 AM
[SOLVED] Remove Blank Address Lines ECEK Forms 4 07-13-2018 03:08 AM
[SOLVED] export to text: remove blank lines/empty rows anski Modules & VBA 6 05-26-2018 08:49 PM
Remove First Two Lines in XML using XML Application.ExportXML hoychep Modules & VBA 2 06-12-2014 03:17 PM
Remove lines from textfile bodylojohn General 12 11-20-2008 03:12 AM




All times are GMT -8. The time now is 05:53 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World