This code worked great in about 8 other places now i am tryng to use in an existing database and when i run the query it gies me a syntex error and then it makes itself into a Union query andy Idea why?
See SQL below -
SELECT [EMP_LIST].[Associate Number], [EMP_LIST].[Assc Lname], [EMP_LIST].[Assc FName], [EMP_LIST].[Pay Rate], [EMP_LIST].Status, [EMP_LIST].Service, [EMP_LIST].Active, [EMP_LIST].Notes, [EMP_LIST].UNION , [EMP_LIST].dob, [EMP_LIST].doh, [EMP_LIST].aDDR, [EMP_LIST].aDDR2, [EMP_LIST].[tERM dATE], [EMP_LIST].[FT Date], [EMP_LIST].[Hire Date], [EMP_LIST].schhours FROM [EMP_LIST] WHERE ((([EMP_LIST].Status)=IIf(nz(Forms![Emp Info-Edit]!statusfltr,"")="",[EMP_LIST]!Status,Forms![Emp Info-Edit]!statusfltr)) And (([EMP_LIST].Active)=IIf(nz(Forms![Emp Info-Edit]!actfltr,"")="",[EMP_LIST]!Active,Forms![Emp Info-Edit]!actfltr)) And (([EMP_LIST].UNION)=IIf(nz(Forms![Emp Info-Edit]!unionfltr,"")="",[EMP_LIST]!union,Forms![Emp Info-Edit]!unionfltr)));
-
Thanks
T
6  1392  
This code worked great in about 8 other places now i am tryng to use in an existing database and when i run the query it gies me a syntex error and then it makes itself into a Union query andy Idea why?
See SQL below
SELECT [EMP_LIST].[Associate Number], [EMP_LIST].[Assc Lname], [EMP_LIST].[Assc FName], [EMP_LIST].[Pay Rate], [EMP_LIST].Status, [EMP_LIST].Service, [EMP_LIST].Active, [EMP_LIST].Notes, [EMP_LIST].UNION , [EMP_LIST].dob, [EMP_LIST].doh, [EMP_LIST].aDDR, [EMP_LIST].aDDR2, [EMP_LIST].[tERM dATE], [EMP_LIST].[FT Date], [EMP_LIST].[Hire Date], [EMP_LIST].schhours FROM [EMP_LIST] WHERE ((([EMP_LIST].Status)=IIf(nz (Forms![Emp Info-Edit]!statusfltr,"") ="",[EMP_LIST]!Status,Forms![Emp Info-Edit]!statusfltr)) And (([EMP_LIST].Active)=IIf(nz (Forms![Emp Info-Edit]!actfltr,"")="" ,[EMP_LIST]!Active,Forms![Emp Info-Edit]!actfltr)) And (([EMP_LIST].UNION)=IIf(nz( Forms![Emp Info-Edit]!unionfltr,"")= "",[EMP_LIST]!union,Forms![Emp Info-Edit]!unionfltr)));
Thanks
T
Union is a reserve word in both Access and SQL. Surround that field name in brackets, [union]
J
Union is a reserve word in both Access and SQL. Surround that field name in brackets, [union]
J
Thanks for the help. But unfortunitly I amnow getting a systex error(missing Oerator) in query wxpression'[emp list]'.' NOt sure what i did wrong. should I just change the name of the feild to something like "Unionsts". I have also included the SQL below -
SELECT [EMP LIST].[Associate Number], [EMP LIST].[Assc Lname], [EMP LIST].[Assc FName], [EMP LIST].[Pay Rate], [EMP LIST].Status, [EMP LIST].Service, [EMP LIST].Active, [EMP LIST].Notes, [EMP LIST].[UNION] , [EMP LIST].dob, [EMP LIST].doh, [EMP LIST].aDDR, [EMP LIST].aDDR2, [EMP LIST].[tERM dATE], [EMP LIST].[FT Date], [EMP LIST].[Hire Date], [EMP LIST].schhours FROM [EMP LIST] WHERE ((([EMP LIST].Status)=IIf(nz(Forms![Emp Info-Edit]!statusfltr,"")="",[EMP LIST]!Status,Forms![Emp Info-Edit]!statusfltr)) And (([EMP LIST].Active)=IIf(nz(Forms![Emp Info-Edit]!actfltr,"")="",[EMP LIST]!Active,Forms![Emp Info-Edit]!actfltr)) And (([EMP LIST].[UNION])=IIf(nz(Forms![Emp Info-Edit]!unionfltr,"")="",[EMP LIST]![union],Forms![Emp Info-Edit]!unionfltr)));
-
THanks for your help.
Thanks for the help. But unfortunitly I amnow getting a systex error(missing Oerator) in query wxpression'[emp list]'.' NOt sure what i did wrong. should I just change the name of the feild to something like "Unionsts". I have also included the SQL below
-
SELECT [EMP LIST].[Associate Number], [EMP LIST].[Assc Lname], [EMP LIST].[Assc FName], [EMP LIST].[Pay Rate], [EMP LIST].Status, [EMP LIST].Service, [EMP LIST].Active, [EMP LIST].Notes, [EMP LIST].[UNION] , [EMP LIST].dob, [EMP LIST].doh, [EMP LIST].aDDR, [EMP LIST].aDDR2, [EMP LIST].[tERM dATE], [EMP LIST].[FT Date], [EMP LIST].[Hire Date], [EMP LIST].schhours FROM [EMP LIST] WHERE ((([EMP LIST].Status)=IIf(nz(Forms![Emp Info-Edit]!statusfltr,"")="",[EMP LIST]!Status,Forms![Emp Info-Edit]!statusfltr)) And (([EMP LIST].Active)=IIf(nz(Forms![Emp Info-Edit]!actfltr,"")="",[EMP LIST]!Active,Forms![Emp Info-Edit]!actfltr)) And (([EMP LIST].[UNION])=IIf(nz(Forms![Emp Info-Edit]!unionfltr,"")="",[EMP LIST]![union],Forms![Emp Info-Edit]!unionfltr)));
-
THanks for your help.
[code=sql]
SELECT
[EMP LIST].[Associate Number],
[EMP LIST].[Assc Lname],
[EMP LIST].[Assc FName],
[EMP LIST].[Pay Rate],
[EMP LIST].[STATUS],
[EMP LIST].Service,
[EMP LIST].[Active],
[EMP LIST].Notes,
[EMP LIST].[UNION] ,
[EMP LIST].dob,
[EMP LIST].doh,
[EMP LIST].aDDR,
[EMP LIST].aDDR2,
[EMP LIST].[tERM dATE],
[EMP LIST].[FT Date],
[EMP LIST].[Hire Date],
[EMP LIST].schhours
FROM [EMP LIST]
WHERE
[EMP LIST].[STATUS]=nz(Forms![Emp Info-Edit]!statusfltr,[EMP LIST].[STATUS])
AND
[EMP LIST].[Active]=nz(Forms![Emp Info-Edit]!actfltr,[EMP LIST].[Active])
AND
[EMP LIST].[UNION]=nz(Forms![Emp Info-Edit]!unionfltr,[EMP LIST].[union]);
[/code=sql]
Thanks for the help. But unfortunitly I amnow getting a systex error(missing Oerator) in query wxpression'[emp list]'.' NOt sure what i did wrong. should I just change the name of the feild to something like "Unionsts". I have also included the SQL below
-
SELECT [EMP LIST].[Associate Number], [EMP LIST].[Assc Lname], [EMP LIST].[Assc FName], [EMP LIST].[Pay Rate], [EMP LIST].Status, [EMP LIST].Service, [EMP LIST].Active, [EMP LIST].Notes, [EMP LIST].[UNION] , [EMP LIST].dob, [EMP LIST].doh, [EMP LIST].aDDR, [EMP LIST].aDDR2, [EMP LIST].[tERM dATE], [EMP LIST].[FT Date], [EMP LIST].[Hire Date], [EMP LIST].schhours FROM [EMP LIST] WHERE ((([EMP LIST].Status)=IIf(nz(Forms![Emp Info-Edit]!statusfltr,"")="",[EMP LIST]!Status,Forms![Emp Info-Edit]!statusfltr)) And (([EMP LIST].Active)=IIf(nz(Forms![Emp Info-Edit]!actfltr,"")="",[EMP LIST]!Active,Forms![Emp Info-Edit]!actfltr)) And (([EMP LIST].[UNION])=IIf(nz(Forms![Emp Info-Edit]!unionfltr,"")="",[EMP LIST]![union],Forms![Emp Info-Edit]!unionfltr)));
-
THanks for your help.
[code=sql]
SELECT
[EMP LIST].[Associate Number],
[EMP LIST].[Assc Lname],
[EMP LIST].[Assc FName],
[EMP LIST].[Pay Rate],
[EMP LIST].[STATUS],
[EMP LIST].Service,
[EMP LIST].[Active],
[EMP LIST].Notes,
[EMP LIST].[UNION] ,
[EMP LIST].dob,
[EMP LIST].doh,
[EMP LIST].aDDR,
[EMP LIST].aDDR2,
[EMP LIST].[tERM dATE],
[EMP LIST].[FT Date],
[EMP LIST].[Hire Date],
[EMP LIST].schhours
FROM [EMP LIST]
WHERE
[EMP LIST].[STATUS]=nz(Forms![Emp Info-Edit]!statusfltr,[EMP LIST].[STATUS])
AND
[EMP LIST].[Active]=nz(Forms![Emp Info-Edit]!actfltr,[EMP LIST].[Active])
AND
[EMP LIST].[UNION]=nz(Forms![Emp Info-Edit]!unionfltr,[EMP LIST].[union]);
[/code=sql]
Thanks for the help. But unfortunitly I amnow getting a systex error(missing Oerator) in query wxpression'[emp list]'.' NOt sure what i did wrong. should I just change the name of the feild to something like "Unionsts". I have also included the SQL below
-
SELECT [EMP LIST].[Associate Number], [EMP LIST].[Assc Lname], [EMP LIST].[Assc FName], [EMP LIST].[Pay Rate], [EMP LIST].Status, [EMP LIST].Service, [EMP LIST].Active, [EMP LIST].Notes, [EMP LIST].[UNION] , [EMP LIST].dob, [EMP LIST].doh, [EMP LIST].aDDR, [EMP LIST].aDDR2, [EMP LIST].[tERM dATE], [EMP LIST].[FT Date], [EMP LIST].[Hire Date], [EMP LIST].schhours FROM [EMP LIST] WHERE ((([EMP LIST].Status)=IIf(nz(Forms![Emp Info-Edit]!statusfltr,"")="",[EMP LIST]!Status,Forms![Emp Info-Edit]!statusfltr)) And (([EMP LIST].Active)=IIf(nz(Forms![Emp Info-Edit]!actfltr,"")="",[EMP LIST]!Active,Forms![Emp Info-Edit]!actfltr)) And (([EMP LIST].[UNION])=IIf(nz(Forms![Emp Info-Edit]!unionfltr,"")="",[EMP LIST]![union],Forms![Emp Info-Edit]!unionfltr)));
-
THanks for your help.
-
SELECT
-
[EMP LIST].[Associate Number],
-
[EMP LIST].[Assc Lname],
-
[EMP LIST].[Assc FName],
-
[EMP LIST].[Pay Rate],
-
[EMP LIST].[STATUS],
-
[EMP LIST].Service,
-
[EMP LIST].[Active],
-
[EMP LIST].Notes,
-
[EMP LIST].[UNION] ,
-
[EMP LIST].dob,
-
[EMP LIST].doh,
-
[EMP LIST].aDDR,
-
[EMP LIST].aDDR2,
-
[EMP LIST].[tERM dATE],
-
[EMP LIST].[FT Date],
-
[EMP LIST].[Hire Date],
-
[EMP LIST].schhours
-
FROM [EMP LIST]
-
WHERE
-
[EMP LIST].[STATUS]=nz(Forms![Emp Info-Edit]!statusfltr,[EMP LIST].[STATUS])
-
AND
-
[EMP LIST].[Active]=nz(Forms![Emp Info-Edit]!actfltr,[EMP LIST].[Active])
-
AND
-
[EMP LIST].[UNION]=nz(Forms![Emp Info-Edit]!unionfltr,[EMP LIST].[union]);
-
Should get you there.
J
-
SELECT
-
[EMP LIST].[Associate Number],
-
[EMP LIST].[Assc Lname],
-
[EMP LIST].[Assc FName],
-
[EMP LIST].[Pay Rate],
-
[EMP LIST].[STATUS],
-
[EMP LIST].Service,
-
[EMP LIST].[Active],
-
[EMP LIST].Notes,
-
[EMP LIST].[UNION] ,
-
[EMP LIST].dob,
-
[EMP LIST].doh,
-
[EMP LIST].aDDR,
-
[EMP LIST].aDDR2,
-
[EMP LIST].[tERM dATE],
-
[EMP LIST].[FT Date],
-
[EMP LIST].[Hire Date],
-
[EMP LIST].schhours
-
FROM [EMP LIST]
-
WHERE
-
[EMP LIST].[STATUS]=nz(Forms![Emp Info-Edit]!statusfltr,[EMP LIST].[STATUS])
-
AND
-
[EMP LIST].[Active]=nz(Forms![Emp Info-Edit]!actfltr,[EMP LIST].[Active])
-
AND
-
[EMP LIST].[UNION]=nz(Forms![Emp Info-Edit]!unionfltr,[EMP LIST].[union]);
-
Should get you there.
J
J
THanks for your Help I ended up just changing the field Name it did not matter what I seem to do it did not want to work. But again thanksfor your help.
T
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Robert Neville |
last post by:
I would like to add filter functionality to my database whether
through the Main form or the subform. This question may be
rudimentary, yet I have not less experience with filtering data
outside from queries. Let me just add that Allen Browne excellent
article about this subject may not apply to this scenario on an
elementary level. (Here's the link to the article; Filter a Form on a
Field in a Subform -...
|
by: emailtonitin |
last post by:
I am facing a design issue in my code related to the
filesystemwatcher's filter property.
I figured out that the filter won't accept multiple patterns for eg.
"*.txt, *.csv" don't work.
To workaround this issue, I have 2 options:
1. To create multiple instances of filesystemwatcher class and hook all
of them to the same event handlers.
2. To use 1 instance of filesystemwatcher to listen to all files using
blank filter. And in the event...
|
by: Thall |
last post by:
Hey Gurus - I've seen a few solutions to this problem, but none of which I can do without a little help. Here's the situation
The following code loops thru a sales report, using the sales rep ID as a filter so that multiple reports are created. This is now generating a PDF report, but I need to change it to RTF documents.
My question is, since I can't create an RTF using open report, is there a way to use the strFilter with the OutputTo...
|
by: amir |
last post by:
How can I filter my database in Multiple way, I mean that I wana to
filter st. like this:
sm* *th* *er
Please Help me on this matter
Amir
|
by: Barb.Richards |
last post by:
I have created an append query that pulls information from one
database, and will append the selected information into a new table.
The fields are setup like 'number' 'category' 'code' 'shares' and
'dollars'. Using the "backend" of this table I can filter the numbers
by right clicking and using Filter For: then enter 1 or 2 or 3 and
this will return results for all information that has 1 or 2 or 3 as a
number.
However, the problem I...
| | |
by: nev |
last post by:
i declared 2 datatables:
dim dtt1 as datatable = ds.datatable1
dim dtt2 as datatable = ds.datatable2
dtt1 has firstname, lastname columns
dtt2 has firstname column
when i filter dtt1 by lastname, there will be a lot of firstnames
|
by: jcf378 |
last post by:
Hi all--
Does anyone have any insight as to how I might create a search form that allows a user to select criteria based on any related table in the whole database. The search form I have now only allows me to filter based on variables in a single table. I would like to have a search form where I can select multiple variables (from various linked tables) to filter by, and return results based on this multi-table filter.
Allen Browne has a...
|
by: jcf378 |
last post by:
Hi all--
Does anyone have any insight as to how I might create a search form
that allows a user to select criteria based on any related table in
the whole database. The search form I have now only allows me to
filter based on variables in a single table. I would like to have a
search form where I can select multiple variables (from various linked
tables) to filter by, and return results based on this multi-table
filter.
Allen Browne...
|
by: woodey2002 |
last post by:
Hi Everyone. Thanks for your time.
I am trying to create a search form that will allow users to select criteria from multiple multi select boxes. So far i have managed to achieve a search option for 2 list boxes:- county and nationality, while trying to add a third multi select list box for qualifications search is where i encounter my problem.
I've copied the working code from my working list boxes, however it cant seem to pick up the...
|
by: woodey2002 |
last post by:
Hi Everyone and many thanks for your time.. I am trying to begin access and a bit of VBA i am enjoying it but I have a annoying problem I just can’t get any where on.
My databse mostly includes bits of code for different examples. I have one last thing to finish.
I am trying to create a search form that will allow users to select criteria from multiple sources eg ,multi select list boxes , combo boxes.
I have a subform showing all the...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| | |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
