Query Re-writes Itself And Throws Error
Mar 13, 2006
Hi,
I have this query here:
SELECT sum(subtotal) AS final_count
FROM [select count(*) as subtotal from 1_2_06 WHERE Role='EUM' union all select count(*) from 1_9_06 WHERE Role='EUM' union all select count(*) from 1_16_06 WHERE Role='EUM' union all select count(*) from 1_23_06 WHERE Role='EUM' union all select count(*) from 1_30_06 union all select count(*) from 2_6_06 WHERE Role='EUM' union all select count(*) from 2_13_06 WHERE Role='EUM' union all select count(*) from 2_20_06 WHERE Role='EUM' union all select count(*) from 2_27_06 WHERE Role='EUM']. AS all_counts;
I save it as a query then I try and run it, but I get an error saying that the path is invalide. When I open the query to look at the SQL it looks like this:
SELECT sum(subtotal) AS final_count
FROM [select count(*) as subtotal from 1_2_06 WHERE Role='EUM' union all select count(*) from 1_9_06 WHERE Role='EUM' union all select count(*) from 1_16_06 WHERE Role='EUM' union all select count(*) from 1_23_06 WHERE Role='EUM' union all select count(*) fro] AS all_counts;
Why is this happening?
View Replies
ADVERTISEMENT
Dec 14, 2007
sql = "SELECT Table1.* INTO Table1 IN '" & sExpFileName & "' FROM Table1"
CustDB.Execute sql
where the sExpFileName contains the path of The Target MS Access DB
The Query works fine. But recently I discovered that if my target File name contains apostrophe It gives error as Query Input must contain at least one table or query
Is there any way to solve this problem. Please suggest me some way
View 7 Replies
View Related
Apr 12, 2008
I've been using MS access to lift some text files for a number of years
I have now got an issue where by the new machinary is very clever and has alittle server to collect the data. Machine1_127.0.0.1, Machine2_127.0.0.1
Previously I picked them up via ftp and downloaded the data into a text file called Machine1.txt
Now I can run a server where by the Machine1_.txt is created locally on the processing machine
however the software included ( which I can't change) just creates text files like so Machine1_127.0.0.1 and now the link doesn't work when I create the link access throw this:
"Microsoft Jet Database engine could not find the object c:Path to fileMachine1_127.0.0.1.txt. Make sure the object exists and name and Path are correct"
If I manually copy the file and rename it to machine1.txt all is good
Any offers/thoughts or suggestions, the path is quite deep at present and that could be changed.
I can't import the files either? unless I rename them and at the moment I am doing this through the interface
View 2 Replies
View Related
Aug 22, 2006
i have a subform on a form with Source Object="Query.myquery", my form is generating a sql string dynamically and assigning this sql string to the RecordSource property which works fine. But now if the user changes the width of the columns in the subform and close the form , a prompt is thrown asking if the "myquery" needs to be saved. I dont want to save the settings of "myquery" but at the same time avoid this modal dialog. If i use DoCmd.SetWarnings=False , i dont see the warning anymore BUT the query gets saved.
I am using "myquery" to just display column names in the subform when the forms loads and also without it i cant directly use RecordSource property in my code.
any suggestions?
View 1 Replies
View Related
Jun 22, 2007
I have a form with fields being input to a table to create records in a query. The query is dispalyed in a ListBox.
People are forgetting to create a New Record, selecting an existing record in the List Box and overwriting it.
How can I prevent this?
Please, no code solutions - I don't understand those. Events and macros I can just about grasp.
Thanks
View 2 Replies
View Related
Nov 6, 2006
Hi is it possible for multiple users to use the same interface to write to a access database ? i know multiple users can open an access DB but can they use a form to update a table at the same time ?
View 3 Replies
View Related
Jan 28, 2015
I have a form full of cascading unbound combo boxes which allows me to assign companies and people to a project.I have a save button which writes the selected values to a table.To view the assignments when the record is later viewed I have placed a bound field behind the combo box.
View 7 Replies
View Related
Aug 12, 2014
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
View 9 Replies
View Related
Sep 28, 2005
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:
SQLtemp = "UPDATE 'Brukere' SET"
SQLtemp = SQLtemp & " 'navn' = '" & request("Navn") & "', "
SQLtemp = SQLtemp & " 'epst' = '" & request("Epst") & "', "
SQLtemp = SQLtemp & " 'Pass' = '" & request("Pass") & "', "
SQLtemp = SQLtemp & " 'Firma' = '" & request("Firma") & "', "
SQLtemp = SQLtemp & " 'BAdresse' = '" & request("BAdresse") & "', "
SQLtemp = SQLtemp & " 'BPostAdr' = '" & request("BPostAdr") & "', "
SQLtemp = SQLtemp & " 'PAdresse' = '" & request("PAdresse") & "', "
SQLtemp = SQLtemp & " 'PPostAdr' = '" & request("PPostAdr") & "', "
SQLtemp = SQLtemp & "WHERE 'Bnavn' = '" & request("Bnavn") & "'"
Response.Write(SQLtemp)
Response.End()
conn.Execute(SQLtemp)
rs.Update[/COLOR]
The finished SQL statement looks like this:
UPDATE 'Brukere' SET 'navn' = 'Alf Byman', 'epst' = 'alf@baccara.no', 'Pass' = 'alfen', 'Firma' = '', 'BAdresse' = '', 'BPostAdr' = '', 'PAdresse' = 'sdfg', 'PPostAdr' = '', WHERE 'Bnavn' = 'alfen'
I have tried to user single quotes, doubble quotes, brackets etc. nothing works.
The code I use for connection is as follows:
<!--#include file="../adovbs.inc"-->
<%
dim conn, rs, SQLtemp
' DSNless connection to Access Database
set conn = server.CreateObject ("ADODB.Connection")
rs="DRIVER={Microsoft Access Driver (*.mdb)}; "
rs=rs & "PWD=uralfjellet; DBQ=" & server.mappath("../../../../db/kunder.mdb")
conn.Open rs
I'll be very HAPPY for some expert help on this.
View 1 Replies
View Related
Apr 22, 2015
All I'm trying to do is a make table (table name to overwrite: "TBL_5_7_RFCs") query via VBA so I can loop through a set of VARs in a table. Here is my code:
Code:
Public Function test_sql()
Dim Conn As ADODB.Connection
Dim RS As New ADODB.Recordset
Dim MySQL As String
Set Conn = CurrentProject.Connection
RS.ActiveConnection = Conn
[code]...
I'll add the loop and array once I get this working but I'm stuck.
View 7 Replies
View Related
Nov 20, 2007
Why can't I sort ascend on this in a query:
a_aircraft_number: (Val([aircraft_number]))
???
View 4 Replies
View Related
Jul 22, 2005
hi............
querycompany ="SELECT distinct company_id,company_name,location_id FROM appointment_detail,company where appointment_detail.location_id="&request.querystring("location")&"ORDER BY company_name"
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'appointment_detail.location_id='.
/sd/jobsearchasp60.asp, line 33
pls sort out
View 3 Replies
View Related
Jun 15, 2006
here is my query
Code:SELECT FormatPercent(COUNT(ThreeWeekStatus.Status)/subQuery.Total) AS OnTimeTasksFROM RawData, ThreeWeekStatus, [SELECT COUNT(ThreeWeekStatus.Status) AS Total FROM RawData, ThreeWeekStatus WHERE RawData.OutlineNumber=ThreeWeekStatus.TaskId]. AS subQueryWHERE RawData.OutlineNumber=ThreeWeekStatus.TaskId And ThreeWeekStatus.Status="On Time";
and get the error "you tried to execute a query that does not include the specified expression....
basically have two tables
ThreeWeekStatus (has TaskId and Status as columns)
RawData (OutlineNumber, OutlineDescription, Category, etc)
RawData. Category has values like planed tasks, milestones, unplanned tasks, etc.
ThreeWeekStatus.Status has values like on time, delayed, completed, late
TaskId and OutlineNumber are the same
now I want to take all the tasks that are in both ThreeWeekStatus and RawData and get a percent of of which tasks are completed. also want to group this by category so in the end have it as 80% of the tasks marked as milestones have been completed thinking of the following for the percentage...
(count number of tasks in both tables and marked as completed in ThreeWeekStatus table)/(count total number of tasks in both tables)
want to have similar queries for tasks that are on time, delayed, and late so there should be four
View 4 Replies
View Related
Jun 15, 2006
What I am trying to do is select the Max value in a field in a table in Access and set it to a variable. If I run the query from below in the query window it runs....when I try and launch if from code it bombs out....gives me an error:
ERROR:
Run Time error '2342'
A RunSQL action requires an argument consisting of an SQL statement.
I have other SQL queries in my code and they are running fine...whats differene about this one. How can I run it from the Query window and not from code?
CODE:
strSQL5 = "SELECT Max(POINTS_TO_PLOT_SQL.ID) AS MaxOfID FROM POINTS_TO_PLOT_SQL;"
DoCmd.RunSQL strSQL5
View 5 Replies
View Related
Oct 10, 2007
I have "New Campaign" set as a Yes/No data type, and Income as currency. I have five different levels I'm trying to divide SumofGrossIncome into, such as
$0 to $750,000
$750,000 to $1,500,000
and so on...
These levels only work on existing campaigns. If it's a new campaign, I have different levels to go by.
In my query, I typed:
Level A: IIf(([New Campaign]="No") And ([SumOfGrossIncome] Between 0 And 750000),"A")
I'm getting the error "This expression is typed incorrectly, or it is too complex to be evaluated."
Am I missing something small, or totally off base?
Thanks.
View 6 Replies
View Related
Sep 15, 2005
With ColdFusion and MSAccess 2003, the following query :
SELECT A.*, B.entry_id
FROM A.blog_entries, B.blog_subscriptions
WHERE B.blog_id = #variables.blog_id#
AND B.user_id = '#variables.userInfo.user_id#'
AND A.user_id = B.user_id
returns this error :
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Could not find file 'C:WINDOWSsystem32B.mdb'.
Please advise. ThankYou
View 4 Replies
View Related
May 15, 2006
I've been agonizing over this all day! This is the SQL for the query that I built:
UPDATE [Compl P129 A3C] INNER JOIN MasterFundSch ON [Compl P129 A3C].LOANNUMBER = MasterFundSch.[Loan Number] SET MasterFundSch.P129OldInv = [OLDINVNUMBER], MasterFundSch.P129OldCat = [OLDCATNUMBER], MasterFundSch.P129NewInv = [Compl P129 A3C]!NEWINVNUMBER, MasterFundSch.P129NewCat = [Compl P129 A3C]!NEWCATNUMBER, MasterFundSch.P129PrinBal = [PRINBAL], MasterFundSch.P129PrinTran = [Compl P129 A3C]!PRINTRANS, MasterFundSch.P129Diff = [PRINBAL]-[PRINTRANS], MasterFundSch.P129RptDt = [Compl P129 A3C]!RPTDATE, MasterFundSch.P129ActionCd = [Compl P129 A3C]!ACTIONCODE, MasterFundSch.P129DueDt = [Compl P129 A3C]!DUEDATE, MasterFundSch.P129EffDt = [Compl P129 A3C]!EFFDATE, MasterFundSch.[REPUR SUB] = IIf([groupID]="FH",IIf(Month([fund dt])=Month(Now()),IIf(Day([fund Dt])<16,"N","C"),"C"),Null)
WHERE (((MasterFundSch.P129RptDt) Is Null) AND (([Compl P129 A3C].NEWINVNUMBER)=[MasterFundSch]![New Inv]) AND (([Compl P129 A3C].NEWCATNUMBER)=[MasterFundSch]![New Cat]) AND (([Compl P129 A3C].ACTIONCODE)="0000" Or ([Compl P129 A3C].ACTIONCODE)="0820" Or ([Compl P129 A3C].ACTIONCODE)="0077" Or ([Compl P129 A3C].ACTIONCODE)="0078"));
I'm getting records updating that are not one of the four Action Codes that I've assigned.
Help before I go crazy!
View 1 Replies
View Related
Jul 12, 2006
I have a DB where I run two queries every week that extracts data from a Datawarehouse and appends records to a table. Then i run an update query that updates a policy status on the same table the appended records are added to.
For some reason this week when i try to run the update queary i get the following message:
Operation must use an updatable query.
Anyone familiar with this error? The table in not read-only and no other users are in the db
View 3 Replies
View Related
Aug 14, 2006
I have the following update query:
UPDATE DISTINCTROW MVADMP_SECURITY INNER JOIN Activity ON MVADMP_SECURITY.CUSIP_NO = Activity.[CUSIP#] SET Activity.[Wire Date] = [BK_ENT_DT]
WHERE (((Activity.[Fannie Mae Loan #]) Is Null));
which works fine in the production environment. However, in the test database, I get an updatabale query error.
The Activity table is a table that is local in each database, while the MVADMP_SECURITY table is in the back end and is used by both the test and production environments. Both Activity tables have the same keys and indexes. I have write permissions for Activity table in both environments, while no one has write permissions to the MVADMP_SECURITY in either environment.
I have tried both copying and importing it into the test environment and the result is always the same; updatabale query error. Does anyone have any idea what's going on?
View 1 Replies
View Related
Aug 18, 2006
I am wanting to run a query in my FE which takes data from a Linked DB and copies this to my BE for general user access. The query itself contains three tables with the final table being a Left Outer Join.
If I run this query to append to a table in my FE it runs fine. If I run it to append to the exact same table copied to the BE I get the error "Record is Deleted". If I then open the table there is nothing showing in it. If I then run a delete query on the table it find records to delete.
After some investigation I have discovered this happens whenever there is null data on the Left Outer Join table.
Does anyone know why this works fine appending to the FE but not the BE and more importantly how to resolve or work round this problem.
Many Thanks
JC3
View 3 Replies
View Related
Nov 11, 2006
Hi All,
I made a function to calculate the difference in hours between two times.
Function TimeDiff(Time1 As String, Time2 As String) As Double
'This function is used to calculate the difference in hours between two times.
'It is capable of handling a finish time that is after midnight.
TimeDiff = 0
If Not IsNull(Time1) And Not IsNull(Time2) Then
If CDate(Time1) > CDate(Time2) Then
TimeDiff = ((CDate(Time2) - CDate(Time1)) + 1) * 24
Else
TimeDiff = (CDate(Time2) - CDate(Time1)) * 24
End If
End If
End Function
I have a query where there are 2 sets of times in fields (Start1, Finish1, Start2, Finish2).
When I add a calculated column with the following expression:
Expr1: TimeDiff(Start1,Finish1) + TimeDiff(Start2,Finish2)
it calculates in the query correctly.
However, when I made a new function that incorporated this function, it gave me the #error result in the calculated field.
Function OrdinaryHours(Status As String, Start1 As String, Finish1 As String, Optional Start2 As String, Optional Finish2 As String) As Double
'Initialise
OrdinaryHours = 0
'If employee is NOT a casual
If Not Status = "CT" Then
If Not IsMissing(Start2) And Not IsMissing(Finish2) Then
OrdinaryHours = TimeDiff(Start1, Finish1) + TimeDiff(Start2, Finish2)
Else
OrdinaryHours = TimeDiff(Start1, Finish1)
End If
End If
End Function
Does anyone know why this would #Error. I have the TimeDiff function initialising to = 0 before doing anything...I am stumped why it returns #Error when it should return 0 in the case of something going wrong.
Brad
View 5 Replies
View Related
Jan 9, 2007
Hi,
I was wondering if i could get some help here. I got error message saying "user defined - typed not defined" and it's highlighting the first line that is WorkBase as Database.
Here is my code.
Dim WorkBase As Database
Dim WorkRS1 As Recordset
Dim rsNew As New ADODB.Recordset
Set WorkBase = CurrentDb
strSQL = "INSERT INTO [Projects](Project_ID, [OLD J_ID]) "
strSQL = strSQL + "VALUES ('" & myProjectID & "', '" & myOldPID & "')"
WorkBase.Execute strSQL, dbFailOnError
WorkBase.Close
This is for Office 2003
Thank you in advance
View 1 Replies
View Related
May 29, 2007
Noob! I am building an inventory db. I need a query(s) that uses a Qty available to add inventory added, subtract inventory used and put the final total back into Qty Available. I'd prefer to do in all in one query if possible. Any suggestions?
View 7 Replies
View Related
Jul 26, 2007
I am trying to view a query in design view but everytime I right click on it (or any method) it brings up the:
syntax error in from clause.
I then have to say OK and that's it. Cannot view it.
Interestingly enough, the query works fine when called from my VBA code.
Know anyway round this?
View 10 Replies
View Related
Feb 1, 2008
I have created an append query to begin the transfer of terminated employees to a separate table then delete them from the Active file using a Macro. I get an error running the append query and I have attached the error to this thread. I answer all the halts in the affirmative and the process does happen the way I want it to. The terminated employees get added to the Terminated table and they delete from the Active table. I don't understand what the error is telling me and why its doing the job anyway. Thanks for looking.
INSERT INTO tblEmployeesTerminated ( EmpID, LastName, FirstName, Status, [Position], EmpDate, TermDate, LastChgDate )
SELECT tblEmployeeRecord.EmpID, tblEmployeeRecord.LastName, tblEmployeeRecord.FirstName, tblEmployeeRecord.Status, tblEmployeeRecord.Position, tblEmployeeRecord.EmpDate, tblEmployeeRecord.TermDate, tblEmployeeRecord.LastChgDate
FROM tblEmployeeRecord
WHERE (((tblEmployeeRecord.Status)="Terminated") AND ((tblEmployeeRecord.TermDate) Is Not Null));
View 1 Replies
View Related
Feb 20, 2008
Does anyone know why I get the error "Operation must use an updateable query" when I run this query.
UPDATE Food_Table SET Food_Table.Delivery_Size = (SELECT Delivery_Size
FROM Delivery_Table
WHERE ((Delivery_Table.Region=Forms.Formulation_Combo.Re gion_Combo) AND (Delivery_Table.SubRegion=Forms.Formulation_Combo. SubRegion_Combo) AND (Delivery_Table.Segment=Forms.Formulation_Combo.Se gment_Combo) AND (Delivery_Table.Sector=Forms.Formulation_Combo.Sec tor_Combo) AND (Delivery_Table.Product=Forms.Formulation_Combo.Pr oduct_Combo)));
When I run only the sub SELECT query portion all is well, but not in the update query.
View 14 Replies
View Related
