Hello,
Run-time error ...
No value given one or more required paramete
UPDATE GuitarOptionDetails SET GuitarOptionDetails.OptionCombo = Concatenate("SELECT Option_Item FROM GuitarHeader WHERE GuitarItem & Option_Item =""" & [GuitarOptionDetails].[GuitarItem] & [GuitarOptionDetails].[Option_Item] & """");
basically i need to create an update query to update the OptionCombo field where:
1. OptionCategory=BODY
2. Combine all the BODY OptionItems as one into the OptionCombo field (the Concatenate() function should accomplish this portion)
3. For each InvoiceNumber AND GuitarItem.
NOTE: some invoices will not have any BODY Option_Item, this can place "N" in the OptionCombo BUT only if the invoice does not have a single BODY Option_Item, if it does, it needs to put the combined BODY Option_Item values into the OptionCombo field.
How should I rewrite the SQL to achieve this without getting this error?
I have a query that I set up to return multiple records. The number of the records is in that text box. I have tried to set the where clause to the text box but for some reason I don't get any of the records I want returned. I am using a Where... IN statement. I have tried to put the numbers in single quotes and no quotes and I have had no luck at all. If I do individual records they will return, just not when I try and do more then one at a time. This query is a part of something more, but since it doesn't return anything, Here is my sql statement
Code: SELECT CalibratedEquipmentListTable.ID, CalibratedEquipmentListTable.Manufacturer, CalibratedEquipmentListTable.ModelNo, CalibratedEquipmentListTable.Description, CalibratedEquipmentListTable.SerNo, CalibratedEquipmentListTable.LastCal, CalibratedEquipmentListTable.CalDue FROM CalibratedEquipmentListTable WHERE CalibratedEquipmentListTable.ID In ([Forms]![ReportForm]![Text41]);
I am trying to sum a column on a subform named Cost of New if another column named Final = any of the values listed in my code. However, it is summing the Cost of New column regardless of the values of Final. It is summing for all values.What do I need to modify to make this correct or should I do it another way?
=IIf([Final]="RPR-RPR & RTN" Or [Final]="NFF-TST & NFF" Or [Final]="RTN-RETURN" Or [Final]="SCL-SCR LOCALLY" Or [Final]="SCR-SCR RETURN" Or [Final]="BER-BER RTN CST",Sum([Cost Of New]),Null)
I am trying to delete from a form, the record in the JD SOP TBL where the record Job Description matches the Combo Box "Job Description" on the form, and the Required SOP in the table matches the selection in the list box "SOP List". The list box has multi select to none and both fields are text fields.
Code: DoCmd.RunSQL "DELETE FROM [JD SOP TBL] WHERE [Job Description] = '" & [Job Description] & "' " And [Required SOP] = " & SOP_List & "
Hey there! I've been searching about this forum and found a lot of threads on this sort of issue but haven't been able to figure it out from those alone, so feeling a bit useless :o long winded explanation by the way... I'm useless at explaining! :D
Well basically I have a query that is supposed to power a flexible search form, where you choose the fields you want to search by and enter your criteria. The form contains labelled fields with checkboxes next to them, which you tick if you want to search for something in that field. The results are displayed in a subform which reads from the query.
The problem is with the query! My solution seemed to work fine when I put my criteria in just one field when testing (that is the fields in query design builder, not the form), but when I applied the same to two fields it didn't. Instead, it would return nothing unless I specified exact and correct criteria for BOTH fields I had set up with my solution. So whereas before I could tick "Customer" and search for a customer account number and it would display ALL relevant records, now if i just ticked "Customer" and entered the same number, it would display nothing, unless I ALSO ticked "Booking" and entered a correct booking number that matched a record for that customer in the tables.
I've heard iif statements don't do too well in the criteria fields in the query design builder?? But I know nothing about using SQL really.
For example for the invoices field have used: IIf([Forms]![frmFindInvoices]![ChkInvNo]=True,[Forms]![frmFindInvoices]![InvNo])
So: IIf(form checkbox = true, form text box value)
However it seems to think that if the checkbox is not ticked, I want it to only display EMPTY invoicing fields. This is never the case and so no records are shown! If the tick box is not shown, there is not supposed to be criteria for this field, in other words it should allow ANYTHING through.
What I don't know how to do is tell the bloody thing that if the box is NOT ticked, it is NOT to check for anything in this field, because the user does not want to specify any criteria for this field! Things like "Is Null" produce the same results, probably because the query still thinks I want it to find records that have empty fields.
Any suggestions to specify to the query what I want? I really need to get this done! Thanks! :)
I have a left-join query where I know that some of the values in the left-hand table have no corresponding values in the right-hand table...that's why I used a left-join: I want all the values in the left-hand table to show up. I would expect that records without corresponding values in the right-hand table would show up as nulls (I swear I have dozens of other queries that work this way) but in this one, they show up as "#error" instead.
This is a problem because I want to do some calculations on this field. Normally I would use the nz function to change the nulls to zeros.
So, 1. Any idea why this is happening? 2. Is there a way to change the #error values to zeroes, similar to using the nz function?
HEre's the SQL: SELECT FeederInventorySummary.machine, FeederInventorySummary.Size, FeederInventorySummary.Leaf, FeederInventorySummary.Type, FeederInventorySummary.CountOfFeederID,FeederNeeds Summary_7.FeederQty FROM FeederInventorySummary LEFT JOIN FeederNeedsSummary_7 ON (FeederInventorySummary.Type = FeederNeedsSummary_7.Type) AND (FeederInventorySummary.Leaf = FeederNeedsSummary_7.Leaf) AND (FeederInventorySummary.Size = FeederNeedsSummary_7.FeederSize) AND (FeederInventorySummary.machine = FeederNeedsSummary_7.ActualMachine);
For every record without a corresponding record in FeederInventorySummary_7, the FeederQty field shows up
Code: Public Function DegreeChecker(AGShrs As Integer, ASBAhrs As Integer, ASCJhrs As Integer, AAhrs As Integer) As String Dim Degree As String If ((AGShrs = 0) And (ASBAhrs = 0)) Then Degree = "ASBA" ElseIf ((AGShrs = 0) And (ASCJhrs = 0)) Then Degree = "ASCJ"
[code]...
The problem is, the information that is populating the query which is running this code may not be there. As in some of the fields, AA, AGS, ASBA, and ASCJ might be left blank. Unless these four fields are populated by something, it returns #Error instead of the specified number.Is there a way to say, If variable is blank, ignore and continue on to check if the next thing is 0, or if none are 0, state Potential.The information in this query is to be exported out to excel to be used as a Mail Merge file.
I have a table with a list of Students. While the academic year is in progress those students are in an "Active" status. Once the year is finished those students are moved into (hopefully) a "Passed" status or some other status that is not considered "Active".
Throughout the rest of the database I have many forms that refer to the Student Listing. As an example we have a Test so we select the Student performing the test using a Drop Down List and go from there.
I have set search criteria to the Drop Down List via the Combo Box's Query to only display active students and it works fine during the Academic Year. However if I have to review a test from a previous year then the Student Drop Down List no longer displays the name but their ID# (Primary Key) because the Student's status has now changed and no longer meets the query criteria.
Is there a way to use the Filter/Search Criteria to chose from a listing of Active Students but once selected it will display the Student name regardless of their status?
I am trying to format an excel spreadsheet through access, specifically trying to convert a column from text to dates (I receive the data in text format and need to translate it to dates).
Code: Dim excelApp As Object Set excelApp = CreateObject("Excel.Application") excelApp.screenupdating = False excelApp.Visible = False Set excelWB = excelApp.workbooks.Open("Z:DataBasicSMData.xlsx")
[Code] ....
I'm no longer getting an error, but it isn't actually modifying the spreadsheet...
Okay, now I'm suspecting there's more to this than an overly complicated control source statement....
My summary report contains multiple objects that calculate totals based on a statement in their control source. The report ran fine for a few weeks but all of a sudden it started producing this error:
"The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
I thought perhaps that 12 lengthy control source statements were simply too much for Access to handle, but then I did some testing and eventually removed all but this control source statement: =Sum([NotifType]=9)
That's about as simple as it gets, but when I try and run the report I still get the error. Ggggrrrrrrrrr....
Can anyone tell me what causes this annoying error and how I can get rid of it? (I already compiled.) What little Access quirk am I running into? I will gladly zip the file and post if necessary.
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))"
Set rstSimple = cnnSimple.Execute("SELECT sponser2 FROM data2")
Do While Not rstSimple.EOF spo=rstSimple.Fields("sponser2").Value set tamt = cnnsimple.Execute("SELECT SUM(amount1) FROM data1 WHERE sponser1='"&spo&"' ") Set rstSimple1 = cnnSimple.Execute("UPDATE data2 SET amount2= "&tamt(0)&" WHERE sponser2='"&spo&"' ") rstSimple.MoveNext Loop
What is the syntax error in this Insert Into statement ?
INSERT INTO RawData(RunID,fullName,name,category,type,subType, numberOfLines,virtual,date,namespace) SELECT 257 ,fullName,name,category,type,subType,numberOfLines ,virtual,#1/3/2007#,namespace FROM RawData WHERE namespace ='customer.demo' AND RunID =256
I'm new to working with Access Iif statements. I'm attempting to change data values in an existing table for column named "Status." Some of the data in this column has the correct value (Active) that needed represented, while the rest is populated with either a "J" which is Active or "T" which is inactive. I needed an update query that will ignore any values that are already "Active," and convert any values that are "J" to Active and values that are "T" to "Non Active." Any help would be greatly appreciated. Below is the function I have written that's full of syntax errors:
IIf(([STATUS] T,"T") = "T", then "Non Active"), IIf([STATUS] J,"J") = "J" then "Active"), else Active = "Active"))
I had a post last week with this same problem. Last time I was using a reserved word but I have ruled it out with this database because I changed every field name and I still get the error. I think it may have to do with the combo boxes I am using, but not really sure why I am getting the error. Any help would be great. The sql statement is:
If anymore info is required please tell me, but basically i can insert into the database no problems , but when it comes to updating what is in there i recieve the above error
Code: If Nz(DCount("*", "[Tblupdate]")) = 0 Then ' The count is zero i = 1 Else i = DMax("ID", "tblupdate") End If Dim ssql As String Dim j, k As String j = "P" & i k = Environ("username") ssql = "Insert into tblUpdate(Update_ID,Date,Username) values('" & j & "',#" & Format(Date, "dd/mm/yyyy") & "#,'" & k & "')" CurrentDb.Execute ssql, dbFailOnError
Anybody see anything wrong the syntax for the below query. I'm trying set up this query to pass a string to 'struser' for multiple users. I'm getting 'Syntax error in FROM clause'. Thanks ! ! !
Dim strSQL As String Dim struser As String struser = "MIKE" Set db = CurrentDb() Set qdf = db.QueryDefs("qryUser") strSQL = "SELECT Salable_Figures.Loan_Originator_Code," & _ "Salable_Figures.Loan_Originator_Name," & _ "Salable_Figures.Lastname_Sort," & _ "Salable_Figures.Team, Salable_Figures.Plan," & _ "Salable_Figures.Monthly_SCountOfLoan_Program_Code AS Monthly_SCount, " & _ "Salable_Figures.Monthly_SSumOfNote_Amount," & _ "Salable_Figures.Salable_Monthly_Goal," & _ "Salable_Figures.YTD_SCountOfLoan_Program_Code AS Yearly_SCount," & _ "Salable_Figures.YTD_SSumOfNote_Amount AS Yearly_SSumOfNote_Amount, " & _ "Salable_Figures.YTD_SSumOfNote_Amount AS Yearly_SSumOfNote_Amount," & _ "Salable_Figures.Yearly_Salable AS Yearly_Salable_Goal " & _ "FROM Salable_YTD_Figures INNER JOIN Salable_Figures" & _ "ON Salable_YTD_Figures.Loan_Originator_Code = Salable_Figures.Loan_Originator_Code " & _ "WHERE Salable_Figures.Loan_Originator_Code)=" & struser & _ "';'" ' Apply the new SQL statement to the query qdf.SQL = strSQL ' Open the query DoCmd.OpenQuery "qryUser"
I have 2 tables called MakeTable1 and DBO_TBL_Activity
Im trying to update MakeTable1 with the values from TBL_Activity when both activity.StartDate and maketable1.Dates match but also acticity.IDStaff and Maketable1.ID Match
Below is the SQL i have so far
Code:
UPDATE [MakeTable1].[Detailsa] SET [dbo_tbl_activity].[details]
WHERE [MakeTable1.Dates)=[dbo_tbl_activity].[StartDate] AND [MakeTable1].[id]=[dbo_tbl_activity].[idstaff]);
