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.
I have a form based on a table with three controls: txt1, txt2 and txt3. Txt is unbound, while the first two get their source from the table. I would like that if txt1 is showing data, then txt3 shows the same data of txt2. If this possible? I have tried something like this but does not work:
If IsNull(Me![txt1]) Then Me!txt3.Visible = True
If Me!txt1 = "*" Then Me!txt3 = Me!txt2 End If End If
Tasklist.RowSource = "SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < aa ORDER BY [Tasks].Staff_Name;"
how can i make it look the the data i put in "aa" rather than prompt a msg box asking for "aa"?
Select Materials.ID, Materials.AvailableUnits, Substances.Name, Suppliers.CompanyName FROM Materials Right Join Substances on Materials.Substance = Substances.ID
This works in joining the substances.name (from substances table) with the value stored in materials.substance (which is ID of substances.ID)
How can I add another Join to join Suppliers.CompanyName with suppliers.ID. The current Materials.Supplier is a key of Suppliers.ID which holds the value of Suppliers.CompanyName. I've tried this a million times and as soon as I insert a second join, no matter what format I get "syntax error in join statement".
I think this would be a simple query but I have not been successful in getting anything to work at this point. So I thought I would through it out to the pro's here! Thanks for any help you might give.
Table looks like this: EventID EventDate Score EventType 46 09/20/2007 72 1 Day Net 46 09/20/2007 72 2 Day Net - Day 1 46 09/20/2007 68 1 Day Best Ball 48 07/14/2007 69 1 Day Net 48 07/14/2007 34 Nassau
What I am trying to get from this data is the AVG score, but not include Nassau or Best Ball events. So I have used the Not Like "%Nassau%" etc, in ASP (since ASP requires % rather than * like access. The results I would like to see are one score from each DATE avg'd. That means with this sample data we should get... 46 09/20/2007 72 1 Day Net 48 07/14/2007 69 1 Day Net
Avg score 70.5
I don't care to have the Event Type listed, if that causes grouping issues. I just am not getting anything to work. Thanks for any help.
In short If I could get the avg score using only one score a day that is not a Nassau or Best Ball event, I would have to rejoice! Thanks so much.
I have an open form with buttons to open other forms and use an If statement to position them an example is below.
Code: Private Sub Command31_Click() DoCmd.OpenForm "Opener", acNormal If Me.Frame54 = 1 Then DoCmd.MoveSize 2500, 1000 ElseIf Me.Frame54 = 2 Then DoCmd.MoveSize 25500, 1000 End If End Sub
This works perfectly, however I need to put similar code as a line in a macro and cannot seem to link it.
Code: Public Function totalmove() If Forms.Scan_Data.Frame54 = 1 Then DoCmd.MoveSize 2500, 1000 ElseIf Forms.Scan_Data.Frame54 = 2 Then DoCmd.MoveSize 24000, 1000 End If End Function
I get Error 438 object doesn't support this property or method...
Private Sub NextApplication_Click() On Error GoTo Err_NextApplication_Click DoCmd.GoToRecord , , acNext
Exit_NextApplication_Click: Exit Sub
Err_NextApplication_Click: If Err.Number = 2105 Then MsgBox "Cannot navigate to the next record. This is the last record." Else MsgBox Err.Description End If
Resume Exit_NextApplication_Click
End Sub
but even when this error occurs nothing is being properly handled the way i specified - any ideas ?
I am designing a table that will be used on a Form...thee will be a series of yes/no questions. I wanted to include a list box that the user adds one or the other. However i can't remember how to do it :D :confused:
i'm not very good at vb and i'm sure this is an easy problem but i can't find the answer. i have constructed a vb code to show images from a stored table [not OLE linked]:
Private Sub Form_Current() If Not IsNull(Me.combined_image_path) Then Me.cempic.Picture = Me.combined_image_path End If End Sub
this works fine except when there is no image to show, the last image stays visable. what i want is no image to show if no image is availiabe for that record. i know that the statement should be constructed along the lines of 'then' if no image exists then show nothing 'else' Me.cempic.Picture = Me.combined_image_path
I got this to work easily on another form but on this particular form it doesnt work. I enter a command button and name it d1, then I enter this code for it to populate the current date in the field next to the box:
Private Sub d1_Click() Date_Entered.Value = Date End Sub
Can anyone see why it doesnt work :eek:
ERROR IS: Run-time error '2465':
Database cant find the field 'Date' referred to in your expression.
When I enter a date field it enders whats in that field (blank) into the field I told it (Date_Entered) on my form.
I am making a simple tabular form to use as a subform in another.
The form has a cbo box (cboItem_Name) Quantity (txtQuantity) Price (txtItem_Cost) Extended Price (txtExtended_Price)
Selecting an item in the combo puts the price in txtItem_Cost. Default for Quantity is 1. Control Source for txtExtended_Price = [txtQuantity]*[txtItem_Cost] and gives me my extended price.
I have a control in the footer of the form called txtSub_Total. It's Control Source =SUM([txtExtended_Price]). This Sub Total box generates the #error.
Can anyone tell me why? Do I need to do some other magic with my txtExtended_Price control instead of a simple calculation, like I have?
quick easy question, as im a noob when it comes to coding/vb.
Within our company database we have the usual timesheet table. With the table I have create a query (called "dailytimeforkeith") that gives me the sum of time for an individual person for one day (in fact its the previous day). What I have done then is created a form (called "keithyesterdaytime") that shows this query entry (called "sumoftimespent").
Each user has a different database screen logon and forms that are present when they log in. So what I then done is on one of these forms the load up, i attached this:
Private Sub Employee_Enter()
Me.Employee = User.FirstName
If Me.Employee = "Keith" Then
Dim stdocname As String
stdocname = "keithyesterdaytime"
DoCmd.OpenForm stdocname, , , acAdd
............
then on "keithyesterdaytime" opening the following code runs ------------------------------------------------------------
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?
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"
