Hi there, I'm trying to write a query that will display "NA" if there is no End Time but if there is an end time then display it as normal. The problem I'm having is that if there is no End Time then I get an #ERROR appear in the record. Here is the query:
Expr1: IIf(IsError([End_Time]),"N/A",[End_Time])
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.
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.
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.
Why can't I sort ascend on this in a query:
a_aircraft_number: (Val([aircraft_number]))
???
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
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
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
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.
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
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!
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
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?
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
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
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
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 RelatedI 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?
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));
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.
Hi, i know ive asked this in another forum, but i just read a thread on a query function which may be able to deal with this so thought id post it here:
the function i just saw:- Expr1: IIf([Sold],="","0",[Sold])
this does nothing to help my problem though so any ideas?
i have three fields; [boughtin], [sold] and [instock]. [boughtin] comes from one query and [sold] from another.
[instock] = [boughtin] - [sold]
because [sold] only has a value once an invoice has been created for that item, it means that if you try to view an item that hasnt been sold yet, you see how many have been bought in, but no sold value and then because of this, no stock value. (you just get an '#error' value in the stock box.
basically what i need is this:
if both [boughtin] and [sold] are present, then the calculation is done and a [stock] value is shown.
if only [boughtit] is present then the stock value simply shows the [boughtin] value.
[boughtin] is located on one subform (frm_stocksub) and [sold] is one another (frm_stocksubsub).
i need a code to put into the after update event of the main combo on the main form so that it detects this and then displays the approriate value once both queries have run.
a simple idea of what i want is:
If [Sold] has no value Then
[InStock] = [BoughtIn]
Else
[InStock]=[BoughtIn]-[Sold]
End If
Obviously there is far more detail in terms of locating the particular text boxes, but i hope it gives you an idea of what i want.
Thanks
Jared James
I am using the following to extract date information from a text field as part of a query.
Narrr_Date:CDate(Left(Mid([si_narr_t],Instr([si_narr_t]," ")+1), Instr(Mid([si_narr_t],Instr([si_narr_t]," ")+1)," ")-1))
Due to users not putting the correct information in I will get the #Error in the results of the query.
I need to do a DateDiff on the results so I need to get rid of any values that come up with #Error.
Since I cannot ensure the date extracted by the query is correct, how do I deal with the error?
Hi again,
I now have the ability to select a query and have a form open on that record set.
The problem is that some of the queries generate the error message : Invalid SQL statement; expected 'DELETE',INSERT','PROCEDURE','SELECT' or 'UPDATE'
Code is
Private Sub Combo0_Click()
sQuery = Combo0.Column(0)
DoCmd.OpenForm "frmFilter"
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open sQuery, CurrentProject.Connection
Set Forms("frmFilter").Recordset = rstSuppliers
Forms("frmFilter").UniqueTable = sQuery
End Sub
THe queries are the same type. :confused:
I am running a query that keeps asking for me to enter data for some fields before it opens the query, which I don't. When the query opens, the data in the fields is there.? Why? This is what is written in the field (query):
Total Jumbos: nz([pathjbo50],0)+nz([esjbo50],0)+nz([f1jbo50],0)
The quote "Total Jumbos:" is my label. The rest is my expression. This setup works on a few other columns I entered but this one (and a few others) don't. Any suggestions would be appreciated.
FYI: The query information is gathered from a table populated by a form. The label "Total Jumbos" is not on the original table but I have other labels in the same query that are also not on the original table that do work.
I have a query and it works fine, it gives the progress of tasks related to the whole project.
The thing is that if the whole project is at 0% and you can't divide by 0 the query returns the value #Error.
Is there a way to mask it so that rather then returning #Error it shows up a blank filed or at least 0.
For example it is like this now
OutlineNumber |OutlineDescription |DeliverableDesc| Contribution
6.1| UST OTR Model V2| OTR Model v2| 100.00%
6.1.2| QA & Bug Fixing| OTR Model v2| 0.00%
7.1| STP | STP | #Error
And would like it to be this
OutlineNumber| OutlineDescription| DeliverableDesc| Contribution
6.1| UST OTR Model V2| OTR Model v2| 100.00%
6.1.2| QA & Bug Fixing| OTR Model v2| 0.00%
7.1| STP | STP |
//notice no #Error just blank space
Here is the query
Code:SELECT RawData.OutlineNumber, RawData.OutlineDescription, RawData.DeliverableDesc, FormatPercent(RawData.PercentComplete/subQuery.TotalProgress) AS ContributionFROM RawData, [SELECT DeliverableDesc, SUM(PercentComplete) AS TotalProgress FROM RawData GROUP BY DeliverableDesc]. AS subQueryWHERE ((RawData.DeliverableDesc)=(subQuery.DeliverableDe sc));
thanks for any help you might be able to give
Note I am presenting this query in a form to the user, so if it can't be done in query directly, is there a way I can code it on the form to remove the #Error values in teh query
I am trying to update a hyperlink field with data of textfield of form.
\192.168.4.40h driveNTPC SIPATDMSSDocumentsP-159
but instead of this server path, it is giving
file:c/\192.168.4.40h driveNTPC SIPATDMSSDocumentsP-159
Please help