Query Run Gives "error In Criteria Expression" HELP?
May 15, 2006
I have a query with the following function to correct a system code.
I want to exclude a certain code from the function output from the query records after i correct the coding. . . I have run the query with no selection criteria, all works fine, there are no null fields. As soon as I put <>"53" in the criteria for this function, i get the error message "Error in criteria Expression."
SystemSalesType is from 1 to 53 from the field data.
Any help, or what am I missing?
thanks in advance,
sportsguy
Function Recode(anyFacePrice As Currency, anyMarket As String, anyNewChange As String, SystemSalesType As String) As String
If anyMarket = "Electrical" And anyNewChange = "N" And SystemSalesType = "53" And anyFacePrice >= 15000 Then
Recode= "51"
ElseIf anyMarket = "Sprinkler" And anyNewChange = "N" And SystemSalesType = "53" And anyFacePrice >= 50000 Then
Recode= "51"
ElseIf anyMarket = "Suppression" And anyNewChange = "N" And SystemSalesType = "53" And anyFacePrice >= 50000 Then
Recode= "51"
ElseIf anyMarket = "Electrical" And anyNewChange = "C" And SystemSalesType = "53" And anyFacePrice >= 15000 Then
Recode= "51"
ElseIf anyMarket = "Sprinkler" And anyNewChange = "C" And SystemSalesType = "53" And anyFacePrice >= 50000 Then
Recode= "51"
ElseIf anyMarket = "Suppression" And anyNewChange = "C" And SystemSalesType = "53" And anyFacePrice >= 50000 Then
Recode= "51"
ElseIf anyMarket = "Electrical" And anyNewChange = "C" And SystemSalesType = "53" And anyFacePrice <= -15000 Then
Recode= "51"
ElseIf anyMarket = "Sprinkler" And anyNewChange = "C" And SystemSalesType = "53" And anyFacePrice <= -50000 Then
Recode= "51"
ElseIf anyMarket = "Suppression" And anyNewChange = "C" And SystemSalesType = "53" And anyFacePrice <= -50000 Then
Recode= "51"
Else
Recode= SystemSalesType
End If
I am creating a simple query in a farm audit database.
In criteria of the customers field, i have entered "Farmer One" as this is the customers data which I want to retrieve and in the Totals section I have changed the setting to Where instead of group by, but I am still getting "Data type mismatch in criteria expression" popping up.why this is?
I am trying to pass parameters to my qury thru my combo selection. I keep getting this error "Data type mismatch criteria expression", does anyone have an idea why? WHERE (((fShiftWorked([tblTimeLog].[timeStart])=[Forms]![frmOperatorWorkDone]![cboShift] Or IsNull([Forms]![frmOperatorWorkDone]![cboShift]))=True));
I have spent so much time onthis already and i am sick of it :mad:
Hi all, I wonder if anyone can help me? What I am trying to do is relativley simple but I am just having a problem executing the expression.
I have a search form which returns the list of results in a subform, in the search form (attached), there is a field which is to bring back results for contracts due in X amount of days, where the user inputs X. In the query results I only want this field to queried on if something is actually typed into the field. The field in the query I am writing this under is called [RenDate]. I have written a criteria expression in my query as follows, but am getting no results:
IIf(IsNull([Forms]![F Search]![days for renewal]),"",[RenDate] Between Date() And Date()+[Forms]![F Search]![days for renewal])
Everything else is working fine with this form/query. If anyone could help or advise on my expression it would be much appreciated.
I have a grades_table with data regarding a grade a person gets. It contains like Name, Date, Grade, Grader_Name, Month, Year...
I have another table (grader_table) that contains the Grader_Name, and how many Grades that grader needs done in a given month (i'm using two numbers to indicate month and year).
What I would like to do is I have a form where all info for the grades_table is entered. When the query is run, I would like to have the month and Year field use the date given to auto fill numbers.
So I picture it as like 6/5/07 is given, so the query records the date, but also stores the 5 and 7 in other columns. I tried this in my query for i think its title - Month: Month([Date]) but it says there one too many ")".
I have a feeling I may also need help with the actual retrieval of a months score since it will cross tables, but this is the big question for now. Any help is appreciated.
My query is coming along nicely, but as always once one problem is solved you find another :rolleyes: !
My problem is that I have thus far specified criteria for the field OrdDeliveryCountry, but this field is not filled in unless the delivery address is different from the default address for the customer, therefore it is frequently blank and so the query wasn't finding all records, only those where the Delivery Address was specific to the order.
I want to use the IIf function to make an expression to say (in linguistic terms): If OrdDeliveryCountry is blank, then use the country in the Customers table.
Sounds simple enough, but the criteria currently is: WHERE (((ORDERS.ORDDELIVERYCOUNTRY) = "Austria" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "France" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden") AND ((PRODUCTS.PRODUCTNAME) NOT LIKE "*Upgrade" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Repair" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Rpr" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Commission") AND ((ORDERS.[DEMO/SALEID]) = 2))
So how do I combine the IIf(expr,truepart,falsepart) with "Is Not x Or x Or x"?I.E. I need to get it to exclude records where OrdDeliveryCountry does not equal one in the list, and if that is blank then the Country field in the Customers table does not equal one in the list?
My attempt is this, but I think I'm way off the mark
SELECT ORDERS.SHIPDATE, PRODUCTS.[STANDARD TARRIFF NUMBER], [ORDER DETAILS].[QUANTITY] * [ORDER DETAILS].[UNITPRICE] * (1 - [DISCOUNT]) * (1 - [SPECIAL DISCOUNT]) AS LINETOTAL, [ORDER DETAILS].QUANTITY, ORDERS.ORDDELIVERYCOUNTRY, ORDERS.ORDERID, [ORDER DETAILS].PRODUCTID FROM CUSTOMERS RIGHT JOIN (PRODUCTS RIGHT JOIN (ORDERS LEFT JOIN [ORDER DETAILS] ON ORDERS.ORDERID = [ORDER DETAILS].ORDERID) ON PRODUCTS.PRODUCTID = [ORDER DETAILS].PRODUCTID) ON CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID WHERE (((ORDERS.ORDDELIVERYCOUNTRY) = IIF(ISNULL([ORDERS]![ORDDELIVERYCOUNTRY]),([CUSTOMERS]![COUNTRY] NOT LIKE "Austria" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "France" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden"), (([ORDERS]![ORDDELIVERYCOUNTRY]) NOT LIKE "Austria" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "France" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden"))) AND ((PRODUCTS.PRODUCTNAME) NOT LIKE "*Upgrade" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Repair" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Rpr" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Commission") AND ((ORDERS.[DEMO/SALEID]) = 2)) ORDER BY ORDERS.SHIPDATE DESC;
My thoughts:
Maybe I need to re-structure the WHERE clause? Would it work if the IIf expresssion was in the SELECT part not the WHERE part?
I would really appreciate some help with this: I'm not sufficiently familiar with structuring statements as complex as this and I don't know all the syntax rules etc.
I've never used DLookup before and I can't get it to work for me so far.
I have 1 table which contains products and different properties of each product, such as the weight of the product.
I have created a query which sums the weight of all products, but only for those that have a value >0 in a certain field. This all works fine.
Now I simply want to display that calculated total weight in a text box on a form. So I thought DLookup could be used for that. But I can't get it to work, maybe because I'm not putting in any criteria? In the control source of the text box I've put the following:
=dlookup("[TotalWeight]","qryTotals")
I don't have any criteria, I just want the value from my qry expression. The textbox on my form now displays #name?
I need to implement the following logic in a report column:
IF hist_mis_cds!typgess = "", then calculated field= "" ELSE IF hist_mis_cds!typgess <> hist_code_translation! src_code_desc then calculated field= "X" ELSE calculated field= ""
I have implemented the following query in Expression Builder for the calculated field:
I need to implement the following logic in a report column:
IF hist_mis_cds!typgess = "", then calculated field= "" ELSE IF hist_mis_cds!typgess <> hist_code_translation! src_code_desc then calculated field= "X" ELSE calculated field= ""
I have implemented the following query in Expression Builder for the calculated field:
Hello, first time posting, not sure what info you all need. I'm looking for some help with an if/then statement in access query involving dates. Below is the query I am trying to use, along with the error message. Any ideas?
Domestic Violence CME Required?: IIf(([Date of Next Medical License Renewal]-[Date of Last Domestic Violence Credit (2)])>("yyyy",6),"yes","no")
syntax error (comma) in query expression 'IIf(([Date of Next Medical License Renewal]-[Date of Last Domestic Violence Credit (2)])>("yyyy",6),"yes","no")'
The error seems to be in relationship to the number 6. I want a "yes" to show up in the field if the difference is greater then 6 years.
Hello I'm having trouble getting my nested Iif statement to run. Can anyone help??? I've attached a screen shot of the syntax error that I'm receiving. The example code below needs tweaking.
Update [Goodrec-copy3] set [Goodrec-copy3].shortname = Iif (Not Null([shortname]),[Goodrec-copy3].shortname Like "*,JR*" Or ([Goodrec-copy3].[shortname] Like "*, SR*" Or ([Goodrec-copy3].[shortname]) Like "*, II*" Or [Goodrec-copy3].[shortname]) Like "*, III*", InStrRev([shortname])," ",InStr([shortname]," ,")+1,50) &" "& Left([shortname],InStrRev([shortname])," ,")-1) WHERE ((([Goodrec-copy3].[ctype])="I"));
What I'm attempting to accomplish with this query is to keep the field shortname the same if not null and if it doesn't meet the criteria of having a string value of "JR", "SR", "II", or "III". If the field does have a string value of "JR", "SR", "II", or "III" reverse the string (example John Gissom JR) to reflect shortname as such for example: "Gissom JR John".
I am creating a database to analyze prices. I have 2 tables. One table has a bunch of dates, product names and the prices. There are many of the same dates and many of the same products. I.E. a product could get priced one day and than have a different price the next day.The other table has a bunch of information and dates and product names also. The goal is to add a week to the dates in this table and use that date and product name to look up the corresponding price for that date and product and add it to the record.
I am trying to use Dlookup but evey record is then filled with the same price. I am using this Expr1: DLookUp("Price","Qry_Historic_Price","[Date]=#[End Period]# And primary_id ='[primary_id']")...When I run the query I get a message that says "Syntax error in query expression" than a message box that pops and says "Unknown" with only "ok" to click.
I have an update query for tGLCashAccount where it adds a value from another table with the BeginningBalance to arrive at CurrentBalance.
Here's what it looks like in design view:
Field: CurrentBalance Table: tGLCashAcct Update to: [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
Here is SQL code: UPDATE tGLCashAcct, tMakeNewCashBal SET tGLCashAcct.CurrentBalance = [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance] WHERE (((tGLCashAcct.GLCashAcctID)="102"));
I get the error: data type mismatch in criteria expression when I run it.
Morning all The problem whihc i have is that, when trying to open the form "Create Timetable", through the switch board.... a message box appears saying "Syntax error (missing opertor) in query exprssion" Any ideas on why it is showing this message box??? Help would be appricated Thank you
I have a combobox iBrand on a form frmFoodSub with the following query used to get its values (based on the imput of another combobox, iProduct, linked to the field Product in the table Products.) This code was generated by query builder, not myself:
Code:SELECT Products.BrandFROM ProductsWHERE (((Products.Product)=[Me]![frmFoodSub].[form].[iProduct]))GROUP BY Products.BrandORDER BY Products.Product;
And I keep getting this error message when the combobox is in focus on my form:
You tried to execute a query that does not include the specific expression 'Products.Product' as part of the aggregated function.
I have no idea what started this error. It was all working fine and then all of a sudden the error started appearing and I can't remember making any changes to cause it.
After adding a radio type field in the form called Blocco (with yes or no values, working like the existing field "Bozza") I've modified the code adding something to update the db:
The page itself is loaded with no errors but when the form takes action I get this: Syntax error (missing operator) in query expression 'True Articoli.Bozza = False'.
why? I've just used the same code that worked with the field "Bozza" (which is a checkbox field too)
Access and receive the following message after trying to run a query: Syntax error (comma) in query expression, followed by the formula I wrote on the Query Builder. I use 4 tables to run the query, but only need to trim some stuff from one of them. This table is called BD_lamosa_corregida and have already selected Expression on the Total row in Design View. This is the formula:
The formula is trying to trim the left side of an expression (Part description) which may contain a code number in parenthesis or not, it might also have this parenthesis separated by a space or not.
I'm experiencing a error when I go to add a filter, "Syntax error in string in query expression "MyFieldName""..If I go to to the table where the field is located, I CAN apply a filter.However, If I throw this single field on a form and switch to datasheet view, all of a sudden I can not filter it.
Additional info: whenever I go to build a query with the field, it throws brackets around this field and no others... this is weird.
Code: SELECT MyTable.field1, MyTable.field2, MyTable.[problemfield] FROM MyTable
Sub PickRandom() Dim db As DAO.Database Dim tdf As DAO.TableDef Dim fld As DAO.Field Dim rst As DAO.Recordset Dim strSQL As String Dim strTableName As String
' 1: Create a new temporary table containing the required fields strSQL = "SELECT tblWoodrow Odom.ReviewTopic,tblWoodrow Odom.TeamMember1, tblWoodrow Odom.TeamMember2, tblWoodrow Odom.TeamMember3, tblWoodrow Odom.TeamMember 4" & _ "INTO tblTemp " & _ "FROM tblWoodrow Odom;" DoCmd.SetWarnings False DoCmd.RunSQL strSQL (HERE I RECEIVE AN ERROR MESSAGE. WHAT I NEED TO DO HERE SO IT CAN RUN PROPERLY? ) DO I NEED TO DO A QUERY STRQSQL STATEMENT?) DoCmd.SetWarnings True
' 2: Add a new field to the new table Set db = CurrentDb() Set tdf = db.TableDefs("tblTemp") Set fld = tdf.CreateField("RandomNumber", dbSingle) tdf.Fields.Append fld
' 3: Place a random number in the new field for each record Set rst = db.OpenRecordset("tblTemp", dbOpenTable) rst.MoveFirst Do Randomize rst.Edit rst![RandomNumber] = Rnd() rst.Update rst.MoveNext Loop Until rst.EOF rst.Close Set rst = Nothing
' 4: Sort the data by the random number and move the top 25 into a new table strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy") strSQL = "SELECT TOP 25 tblWoodrow Odom.ReviewTopic,tblWoodrow Odom.TeamMember1, tblWoodrow Odom.TeamMember2, tblWoodrow Odom.TeamMember3, tblWoodrow Odom.TeamMember 4" & _ "INTO " & strTableName & " " & _ "FROM tblTemp " & _ "ORDER BY tblTemp.RandomNumber;" DoCmd.SetWarnings False DoCmd.RunSQL strSQL DoCmd.SetWarnings True
' 5: Delete the temporary table db.TableDefs.Delete ("tblTemp") End Sub
I'm trying to run a very basic iif statement to correct hourly data for sorting. Basically, a trading day runs from 8am - 8am, so I need to adjust the hours to ensure that 1am on the 15th trading day (really the 16th on the calendar), comes after 9am on the 15th trading day (which will actually be the 15th on the calendar).
Here's what I've used. It's driving me bananas, because it keeps telling me that there's a syntax error (comma) in the query expression, but I can't understand why?
