As I am really 'Green' in this line, can anyone tell me what wrong with my syntax?
Thanks in advance.
INSERT INTO Depreciation (AssetID,DepreciationAmount,DepreciationDate)
SELECT Assets.AssetID,
CASE WHEN Assets.AssetID FROM Assets not in (SELECT Depreciation.AssetID FROM Depreciation) THEN Month(Assets.DateAcquired)*SLN(Assets.BookValue,As sets.SalvageValue,Assets.DepreciableLife)/12
ELSE SLN(Assets.BookValue,Assets.SalvageValue,Assets.De preciableLife)/12
END,
Format(Me!DepnRunDT, "0")
FROM Status INNER JOIN Assets ON Status.StatusID=Assets.StatusID
WHERE ((Assets.StatusID)=1);
On my form I have 2 radio buttons rdoAll and rdoSpecific. If rdoAll is true then it prints a report. THis part works fine. However if rdoSpecific is true then I make visable combo box to look up an ID. Then when I click the button I want the same form to open but with just the info pertaining to the ID selected. Here is my code:
Private Sub cmdLotHistory_Click()
Dim stDocName As String, stSelection As String
stDocName = "rptLotHistory"
If Me.rdoAll = True Then DoCmd.OpenReport "rptLotHistory", acViewPreview End If
If Me.cboLotLU.Value > 0 Then stSelection = "[LotID] =" & Me![cboLotLU] End If
I have a function that builds a filter and it get a syntex error. It has three components and I must be missing something in combining them into the filter. SpecID and ReviewID are numbers. Selected is a checkbox and 'Yes' is a string. Can anyone see the source of the Syntax Error?
Private Function PlanFilter() Dim strFilter1 As String, strFilter2 As String, strFilter3 As String strFilter1 = "[SpecID] = " & [Forms]![frmMainEntry]![SpecID] strFilter2 = "[ReviewID] = " & [Forms]![frmMainEntry].Form!fctlReviewRequests!ReviewID strFilter3 = "[Selected] = 'Yes'" gstrFilter = strFilter1 & " And " & strFilter2 & " And " & strFilter3 & ";" Debug.Print gstrFilter End Function
I'm running a VBA query in excel trying to import the field from a table, simple so I thought, any idea on why i'm getting syntax error codes on this part?
can someone help me here? i'm trying to get a few selected employee from two tables, namely EmployeeParticulars and ProjectAllocation. EmployeeParticulars contained all the employees in the company whereas ProjectAllocation has two fields (PID - the project and EmpID - the employee who is allocated). One project consist of many employees.
Ok, let's move on to my problem now. I'm trying to get those employees who are not involved in a current project. i tried this
Code: Select Emp.Name from EmployeeParticulars Emp, ProjectAllocation Pro where Emp.EmpID <> Pro.EmpID
but it doesnt work. instead in returns me all the employees in the EmployeeParticulars table and on top of that, each employee appears 21 times!(this 21 i believe is from the 21 employees for this particular project) So, it actually runs 21*27(no of employees) = 546 times!
i was supposed to use != but i realise it doesnt not work in MS Access so i used this <> instead. however it does not give me what i want. Can someone assist me please.
Hi All, I'am using the following code to update a table (tbl_CDT) on a NotInList event of a cboBox (CDT) I keep getting a SQL syntax error message, can anyone help resolve the code please
Private Sub CDT_NotInList(NewData As String, Response As Integer)
On Error GoTo CDT_NotInList_Err Dim intAnswer As Integer Dim strSQL As String intAnswer = MsgBox("The CDT " & Chr(34) & NewData & _ Chr(34) & " is not currently listed." & vbCrLf & _ "Would you like to add to the list now?" _ , vbQuestion + vbYesNo, "DentureBase") If intAnswer = vbYes Then strSQL = "INSERT INTO tbl_CDT's([CDT_Name]) " & _ "VALUES ('" & NewData & "');" DoCmd.SetWarnings False DoCmd.RunSQL strSQL DoCmd.SetWarnings True MsgBox "The new CDT has been added to the list." _ , vbInformation, "DentureBase" Response = acDataErrAdded Else MsgBox "Please choose a CDT from the list." _ , vbInformation, "DentureBase" Response = acDataErrContinue End If CDT_NotInList_Exit: Exit Sub CDT_NotInList_Err: MsgBox Err.Description, vbCritical, "Error" Resume CDT_NotInList_Exit
My first post here, and I'm hoping some kind soul will be able to help me (as you can tell, I'm a kinda needy type!!) :o
I've got two completely separate databases, for both of which I need to be able to print a single form at a time. No problem there, a report and a bit of code solved that - but only for one of them! This is the successful one:
Private Sub cmdPrint_Click() On Error GoTo Err_cmdPrint_Click
But when I try to run this, all I get every time is: "Syntax error (missing operator) in query expression '(Issue No = Forms![Change Proposals]!Issue No)'".
I can't for the life of me figure why one works but not the other. I've checked and treble-checked that I've got exactly the right document names. The report is "Change Proposal Print" The form is "Change Proposals" The form field criterion is "Issue No" (without an underscore in this one.)
What operator could possibly be missing in the second one, but not the first? Am I being very dense? Can anyone out there help? (Am I asking too many questions in a whiny voice?) :confused:
I have a table called "ShrinkWrapBinding" where I am supposed to look up a "Price" from. As you see in the code below, I calculate a "totalPackages" number which comes from values in a form. My problem is that when I try to use this value (totalPackages) on the DLookUp function (as seen in this code) it gives me a syntax error exactly on the totalPackages variable. Please note that I added single quotes to the 'totalPackages' variable when I use it on the DLookUp function, and have tried every possible combination (single quotes, double quotes) but still doesn't work. What is it that I'm doing wrong?
Code: Dim totalPackages As Integer Dim tempPrice As Double
This may be a very silly question but I have the following code which is meant to take data from textboxes, checkboxes etc and insert it into a new record on a table using the INSERT INTO statment. However I am getting a Syntax error in my INSERT INTO statement which i cannot figure out.
Please Help!!?
Private Sub cmdSaveRecord_Click()
Dim SQL As String Dim Today As String Dim Ref As String Dim HK As String Dim Site As String Dim Equip As String Dim Serial As String Dim Invoice As String Dim Client As String Dim HKRef As String Dim Tested As String Dim Completed As String Dim chkHKr As String Dim Repaired As String Dim Spares As String Dim CompDate As String Dim Exp As String
IIf(Not IsNull([SubJobName]) Or [SubJobName]<>"",[SubJobName],[JobName]) AS Expr1, [SubJobs].Status
Can you have IIF statement in the query expression? because it said "Syntax error in string in the above query expression".
The full codes i have in the form load is Private Sub Form_Load() strSQL = "SELECT [Jobs].JobID, [SubJobs].IndustryNo, [SubJobs].ClientNo, [SubJobs].JobNo, [SubJobs].SubJobNo, IIf(Not IsNull([SubJobName]) Or [SubJobName]<>"",[SubJobName],[JobName]) AS Expr1, [SubJobs].Status" strSQL = strSQL & " FROM [SubJobs] INNER JOIN [Jobs] ON ([SubJobs].JobNo = [Jobs].JobNo) AND ([SubJobs].ClientNo = [Jobs].ClientNo) AND ([SubJobs].IndustryNo = [Jobs].IndustryNo)" strSQL = strSQL & " WHERE ((([SubJobs].Status) = -1))"
ok, i had created a form and wanted to add a couple new fields. so i did so in the related table and then continued to create a new form with all the fields i already had plus the new ones. (by the way half of the fields are from one table and half from another). The first time i created it through the form wizard it worked. however, i had forgotten a few fields, (fields with address and phone # information). so i deleted it and went back through the wizard and included all the fields i needed but this time after i completed the wizard an access window box popped up and said "The wizard is unable to open your form in Form view or Datasheet view, possibly because another user has a source table open in exclusive mode. Your form will be opened in design view." But the thing is i do have other users but no one even had the database open and i didn't have any other windows for access open. so then when i tried to open it in Form view i got this message "Syntax error in FROM claus". Can someone please tell me what that means and possibly what is wrong? :( :confused: :(
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!
This is a simple code for inserting values to database.But I'm getting syntax error in the insert statement.I'm attaching the code for reference.Do help me out Regards Ransha
Dim mail,Login,password,conn,strSql Login=Request.Form("name") password=Request.Form("password") mail=Request.Form("email") 'Creating connection Object Set conn=Server.CreateObject("ADODB.Connection") conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:/example/login.mdb" strSql = "INSERT INTO Names (UserName, Password, Email) VALUES ('"&Login&"','"&password&"','"&mail&"')" conn.Execute(strSql) conn.Close Set conn=nothing
