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  1371  
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
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...
|
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...
|
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,...
| |
