I have a combo box on a form where I allow the user to pick an office. The primary key is the bound column which I then want to use in a stored query to limit the query results to the specific office selected. I can easily do this with the docmd.runSQL in the module, but I'd like to use a stored query instead. My exact problem is I can't get the syntax correct in the Where clause in the query design. I use the build feature and get the following: [Forms]![frmReports]![cboOffice] which doesn't work. If I manually put the key value in the Where clause, it works fine. tell me the correct syntax for the Where clause.
Following is the query, when executed, I am getting the error as mentioned in the subject "Syntax error in FROM clause." The query beautifully works in SQL Server 2000 but not in MS-Access.
select distinct tblBusOperators.OperatorID,tblBusOperators.TradeNa me,tblBusOperators.isCurrent AS CurrOprtr, tblRouteContracts.[Route No],tblRouteContracts.BusITContractNo,tblRouteContrac ts.isCurrent AS CurrContract from tblBusOperators JOINS tblRouteContracts on (tblBusOperators.OperatorId = tblRoutecontracts.OperatorID) join tlkpmonth on (((tlkpmonth.monthno between 1 and 6) and 2006 =2006) or ((tlkpmonth.monthno between 7 and 12) and 2006=2007)) where tblBusOperators.iscurrent = true and tblRoutecontracts.iscurrent = true and not exists (select 1 from tblmsr where tblMSR.monthno = tlkpmonth.monthno and tblmsr.yearService = 2006 and tblMSR.[Route No] = tblRouteContracts.[Route No])
Hi I realised something adnormal about my asp program..hope someone can advise me on this
When I just to connect to my access DB using Microsoft's access driver(dsn approach), the program run fine and I can access all my tables correctly.
However now when I'm using OLEDB connection to connect instead, I encounter the problem of my program only being able to connect to certain tables in the DB only. The error msg that i received is as shown in the title : "Syntax error in FROM clause"
I have list my connection string and code below, pls go on reading if you can help me.. thanks a lot !
The code below is my connection string: <--------------------------------------------- Set cn = CreateObject("ADODB.Connection")
<-------------------------------------------- Dim RSlogin Dim RSlogin_numRows Set RSlogin = Server.CreateObject("ADODB.Recordset") RSlogin.ActiveConnection = cn RSlogin.open("SELECT * FROM User") .................................................
strangely this code will give me error but if i access another table instead e.g "SELECT Name FROM Products ORDER BY Name ASC", there won't be any error at all... I 'm really puzzled... any help will be appreciated!
The first table is created just fine. And if I leave the constraint clause out, the second table is created also. If I try to run it as an SQL query right in Access, after the error is delivered, it highlights the word DELETE, and if I reverse the update and delete portions, it highlights the word UPDATE.
I have looked up the clause in Access help and even using their example, i get the same error.
I am really stuck. I have spent two days searcinh different forums trying to solve my problem. I am trying to create an UPDATE q to my Access database. But I get either the: "Syntax error in query. Incomplete query clause" or "Syntax error in UPDATE query".
First of all here's the URL: www.innotec-as.no/login/Kunder Login U/P either: "alfen" or "thomas".
The page opening up shows the user info, U/P and adress. viewing the information is working perfectly - but editing it..no way.
When editing and submiting the data the above errors occour. Try that and you'll also see the SQL I am trying to execute. The CODE is as follows:
I have a form with 2 cbo. I want query to show all data if nothing has been selected, or show specific data to whatever has been selected in cbo1 AND/OR cbo2.My query was working ok for couple of days and then stopped. I don't know why, as no changes has been made.
if both cbo are blank then it works OR if both cbo have a selected value then it works, ok. But it doesn't if one of the cbo has a value and the other one hasn't.
Code: WHERE (dAreaFK=Forms!F_MainMenu.cboStatsArea AND dShiftFK=Forms!F_MainMenu.cboStatsShift) OR (Forms!F_MainMenu!cboStatsArea IS NULL AND Forms!F_MainMenu!cboStatsShift IS NULL)
I have several queries (crosstabs) which are eventually combined together to form the data for a single report. If I want to date select for this report I need to add a WHERE clause to every query via VBA. This isn't difficult but it set me wondering if there was any way to have one dummy query containing the necessary WHERE statement, and this somehow gets appended to all the other queries, i.e. to filter on date I only need a change in one place.
From a performance perspective, does it matter in what order a number of clauses are specified ? For example if many records satisfy ConditionA but few records satisfy ConditionB, is it better to put ConditionB first ?
SELECT Fields FROM Table WHERE ConditionA and ConditionB or SELECT Fields FROM Table WHERE ConditionB and ConditionA
Is there anyway to search each row within one column for a value ? The "IN" clause appears not to work for multiple values in a row.
SELECT Table_Payee_List.CategoryIDs, Table_Payee_List.PayeeSelectBox INTO GOTIT FROM Table_Payee_List WHERE (((Table_Payee_List.CategoryIDs) In ("25")));
Data (two columns with 5 rows) are represented below:
Payee CategoryIDs Column Column
John Doe 21, 27, 32, 34, 44 - Row 1 Jane Doe 20, 25, 28, 42, 44 - Row 3 Paul Doe 19, 25, 28, 42, 44 - Row 3 James Doe 56, 29, 31, 62, 54 - Row 4 Amy Doe 24, 25, 29, 42, 74 - Row 5
I need to change the below to a where clause to fit inside a union query that is just where clauses.
tblNEWNONTODATA.DateOfVisit) AS FirstOfDateOfVisit FROM tblNEWNONTODATA GROUP BY tblNEWNONTODATA.EVX, tblNEWNONTODATA.TCGDecision HAVING (((tblNEWNONTODATA.TCGDecision)="Adopted" Or (tblNEWNONTODATA.TCGDecision)="Hot Tasked") AND ((First(tblNEWNONTODATA.DateOfVisit))>=#10/1/2014#));
I have a date field NxtAPayDate that I need to update to the same day in the next calendar quarter. I have an update query set up using DATEADD but it does not change the dates. The table name is AutoPay
I am using the Query Design Grid. This is the SQL code behind the query:
UPDATE AutoPay SET AutoPay.NxtAPayDate = DateAdd("q",1,[AutoPay]![NxtAPayDate]);
When I view the results the dates are the same - original dates, when I try to sort the results, I get a message box with the following: syntax error, incomplete query clause?
I cannot get the dates to change by using a specific date either
UPDATE AutoPay SET AutoPay.NxtAPayDate = #1/2/2014#;
I have a SQL query to gather data from a number of tables (balances, accounts, currencies)
Quote:
SELECT [tblBalances].[BalanceDate], [tblAccounts].[AccountNumber], [tblCurrencies].[Ccy], [tblBalances].[Amount], ([tblBalances].[Amount]*[tblRates].[FXRate]) AS AmountUSD FROM (([tblBalances] INNER JOIN [tblAccounts] ON [tblBalances].[AccountID]=[tblAccounts].[AccountID]) INNER JOIN [tblCurrencies] ON [tblBalances].[CcyID]=[tblCurrencies].[CcyID]) INNER JOIN [tblRates] ON ([tblBalances].[BalanceDate]=[tblRates].[RateDate]) AND ([tblBalances].[CcyID]=[tblRates].[CcyID]) WHERE BalanceDate = #12/10/2013#
How do I add 'AmountUSD' to the WHERE clause (such that I can only return records above or below a certain value, for example)
Along the lines of :
Quote:
WHERE BalanceDate = #12/10/2013# AND AmountUSD>1000
I know it's an issue with referring to aggregated functions in the WHERE clause and you're supposed to use HAVING instead
Now I require to join these both, the table and the output of the query on the condition where query.fileName like table.fileName.
There is no key in this field. Why I need this because the table has the sort order which the user can change when needed, if I put the sort order in the query then each time there is a change then the query needs to updated which the user can go wrong. Also the filename in the query has date associated which changes every day so I need to pick the unique part of the file name and associate it with the query to get the output from query and sort order from the table.
I need to extract the date from CODEDATE field if the "-" character exist in or from DATE1 if the character "-" is not present in the CODEDATE field, then filter the query on the date found.
I wrote that:
Code: SELECT IIf(Mid([CODEDATE],3,1)='-',Mid([CODEDATE],4,10),[DATE1]) AS DateFound FROM MyTable WHERE (IIf(Mid([CODEDATE],3,1)='-',Mid([CODEDATE],4,10),[DATE1]) Between 1/1/2013 And 31/12/2013)
The query above extra extract some records without sense for me, I tried many other syntax without success.
After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.
Code: SELECT Unique_No, Table_Name, List_Order FROM My_Table WHERE Table_Name = 'Titles' AND List_Order IN (3,1,15,4,5,12,7,2) ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)
Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus
I think this is propably an easy thing to do, but nevertheless I have not been able to make it work in MS Access...
(Let's say) I have a table with information about people. Sometimes I need to get a subgroup of the table_people, like a group with a certain age, or who live in a certain area, or who like certain things. And sometimes a need to work with the whole table.
To be able to do this basic filtering I have a form with check boxes, like "check this if you want to filter by a persons hobby" and appropriate fields where to choose a hobby from a list. My problem is: how do I add this "condition" to my query? I've tried eg. using "Like" as a filtering tool and IIF() to add the condition, but this doesn't work (in the criteria row):
Like (IIF( Forms![basic]![Filter_by_hobby] , "[Forms]![basic]![Hobby_from_list]", "*"))
So the idea is that if the box is checked, the condition is true and the first text is chosen, and if no filtering by hobby is required, all people are chosen regardless of their hobbies (Like "*") How to fix this - is there a syntax error or a profound mistake in my thinking - or how to do the same more elegantly?
This query works and I can spot the record based on 3 fields and display it. Now I have discovered that we have a person who has an apostrophe in his name like O'Brian. The above code gives a syntax error in this case.
I have table where First Name, Last Name and Initial are all in one field. What is the syntax to remove initial? Been playing with Right and Len with no success, I'm sure this has been done before.
Trying to parse first two sections of a five section string. Now getting a SYNTAX COMMA ERROR. Th Left((([ArborID],InStr([ArborID],"-")+1,(InStr(InStr ([Arbor ID],"-")+1,[ArborID],"-")-InStr([ArborID],"-"))-1)),10)
