I am using a function to set criteria in a an query. It reads the selected values from a combobox on a form and passes the appropriate value into the sql criteria.
E.g., the sql criteria is set to : like fnCountry()
And the function fnCountry() is something like;
If SelectedCountry = "All" Then
fnCountry = "*"
else fnCountry = SelectedCountry
end if
This works fine for a single selection (SelectedCountry = Africa) but doesn't work if I try to combine multiple selections into the criteria string.
E.g. SelectedCountry = Africa Or Italy
So the criteria would need to be
Like "Africa" Or like "Italy"
How else can i build this criteria with multiple values?
I am trying to aggregate IIF functions to give me the total in separate columns (fields) according to the criteria applied however I am getting an error message "You tried to execute a query that does not include the specific expression
as part of an aggregate function, and I cannot find why, The query is as follows:
SELECT
Tbl_Advisor_raw.Month, Sum(Tbl_Advisor_raw.ValuePay) AS ValuePay, Sum(Tbl_Advisor_raw.Salary) AS Salary, Sum(Tbl_Advisor_raw.NetRevenue) AS NetRevenue, IIf(ValuePay>0,(ValuePay/Salary),0) AS pcSpend,
I want the Query Criteria to pull its value from a control on a form.The form control either has data or is null. (My problems occur when the form control is Null). The field in the table either has data, is null or is blank.
Code: =IIf(IsNull([Forms]![FormName]![FormControl]),"" Or Is Null,[Forms]![FormName]![FormControl])
This works for the records with fields that are blank.
This works for the records with fields that have data.
Code: Like IIf(IsNull([Forms]![FormName]![FormControl]),"*",[Forms]![FormName]![FormControl])
This works for the records with fields that are Null or Blank if i drop the iif function but then i lose the ability to pull criteria data from the form control.
I have two date fields in a table. I need to find the days between these dates and, if its greater than 7 days, I want to display the record in a report, so far, I have found a hand function that allows me to enter two dates and it returns a long data value representing the number of days in between the two dates. (google "I've developed the following code to count the business days between 2 dates." and its the second one that starts as SOLVED I made no mods to it as it does what i need it to do.
So, i added this to its own module within the data base for use within a query. My test query basically displays the unique ID, the start date and the end date and then displays the values returned from the function. here is the SQL:
SELECT [Main Table].[Unique ID], [Main Table].[Start date], [Main Table].[End Date], (Business_Days_Between([End Date],[Start date])) AS [Days between] FROM [Main Table] WHERE ((([Main Table].[Start date]) Is Not Null) AND (([Main Table].[End Date]) Is Not Null));
When ran, this Query works... However, when I enter a criteria like =2 or > 7, it says data type mismatch. I have even attempted the CInt() function to make sure its formated as int but i continue to get the same error.
Looking to have a count function which calculate data in sense like if records found on 1-jan-2014 the it give answer as 1, same as records for 2-jan-2014 it should return 2 and so on .. in short the criteria must look and give same number for same dates starting from 1
SELECT Count(T_STUDENTS.studentsID) AS res1yes, T_COACHING.COACH1res, T_COACHING.COACH1port, T_COACHING.COACHworkgroup FROM T_STUDENTS, T_COACHING WHERE (((T_STUDENTS.res_vrijstelling)=False) And ((T_STUDENTS.stud_year)="1") And ((T_STUDENTS.stud_coach)=forms!F_cboCOACHING!cboCOACHING)) GROUP BY T_COACHING.COACH1res, T_COACHING.COACH1port, T_COACHING.COACHworkgroup;
It runs fine, untill the moment the count is 0. At that point it breaks the form. How can i tell access to deal with this? Somehow i need to replace the count result with 0 if there are no records that meet the criteria.
I have this linked table query from a OBDC and I need to be able to filter out specific dates in that query. The dates in the table were in text format and I converted the dates using the CDate function. I wanted to filter the query to a single date and always I get the Datatype mismatch in criteria expression error.
However, filtering dates does work only when there are other specifications in the criteria fields (e.g. if I specify a date and and name). My SQL code in error looks like this:
SELECT purch_hist.PUITM AS ITEM, purch_hist.PUPO AS PO, purch_hist.PUQTY AS QTY_RECEIVED, CDate([purch_hist.PURDT]) AS RECEIPT_DATE, itmcnt.ITBYR AS BUYER, purch_hist.PUCST AS UNIT_COST, vendor.NVNO AS VENDOR NO,
[code]...
This query works fine with a non converted date field, however the dates I need are in text format and need to be converted since I do not have permissions to edit the tables.
I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().
The field on which this function is to enter as a criteria is another calculated date function called Due.
When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.
I am trying to use an IIF function in each of three yes/no fields in a query to select records where one of the yes/no fields (as determined by a dialog form) = true, regardless of the value of the other two fields. Each record can contain any combination of Trues and Falses in these three fields.
The field to be evaluated will be determined based on a combo box on a dialog form. For example for the SALproc field the criteria cell contains: -
There are corresponding functions in the criteria cell for the otehr two fields
So when I am reporting for SAL I can include all records where SALproc = true, regardless of whether the other fields are true or false.
I can get these functions to work in three separate text boxes on the dialog a form, and was feeling quite clever, but I can't get them to work as query criteria. I get an error message saying it is typed incorrectly or too complex.
I have also tried using the IIF functions in text boxes on the dialog form, and then using the value of those text boxes as criteria in the query. I get the same error message.
I can also get the query to select the records I want by manually entering True for the SALproc field and * for the other two yes/no fields, so the concept of using some combination of true, *, * as criteria appears to be valid.
I would really appreciate some help on this, I suspect I am not far away from making it work.
I'm assuming that it is actually possible to use an IIF function as a query criteria ... ...
In the meantime I am going to try creating functions containing the IIF functions and use those as criteria.
I have created a query which has a function call as the criteria for one of the numerical fields. The function returns a string expression such as .....
1) 132 OR 142 OR 156 2) 132 OR 142 3) 132
..... into the criteria section of the numerical field.
Unfortunately, the query doesn't work if the criteria is generated by the function call. However, if I hardcode the criteria (don't send a function call but directly write 132 OR 142 OR 156) the query works.
I am puzzled and I am not sure how to solve this problem.
I have a form where I am using two combo boxes the second combo box fields are being based on what is selected from the first comb box. When I enter the DLookup criteria into the text box I am trying to lookup based on the selection of the combo boxes it returns nothing. Here is what my DLookup looks like: DLookup("[BranchNumber]", "[tblAllADPCoCodes]", "[ADPCompany]= '" & [cboADPCompany] & "' And [LocationNumber]= '" & [cboLocationNo] & "'")
I can't seem to get past this expression/criteria problem.
[Actual] is my field I want to grab data from [L3-4-5] is my main table [CDATE] is a form control where the user enters a date [Quarter] is a number field and want it to equal 1 [Partname] is a text field and string it to equal 1
My current expression is:
=DLookUp("ACTUAL","L3-4-5","ID=" & [CDATE] And "[QUARTER]=1" And "[PARTNAME]='1'")
Although my other expression on another form works.In my if code statement I have
I'm currently trying to build in access a replica of an atrocious search function in excel.
I have a list of data quite simply in 5 columns and i want to filter through this data about (10000 rows).
My form has 5 data points.
The first is Product Name this is a string (i've looked up a lot of codes to search strings and even partial strings but no one seems to have done what i need).
- Basically i need it to search for any / multiple parts of the string entered. - for example if someone enters apple trees june i need it to look for cells containing those three words in any order, even conjoined for example "appletreejune" would still return or "apples on a tree in june". - This is attached to a single col called Product Name.
Based on this search i need it to look for data in a col called mark type (which is selected by a drop down)
Then by Market Context (also a drop down)
Then by a start and an end date, however, only one of the values (start or end) needs to be between the start and the end dates listed in the start and end date columns in the table.
I am working with MS Access 2003. I have a form (frmCalendar) with a textbox (tbDay) and a combobox (cmbLineName)
I want to count the number of records based on the criteria from cmbLineName, and show the result in tbDate. Inside the Control Source of tbDate I put in the following:
The problem is that the result in tbDate is always 0; regardless of what is chosen in cmbLineName. I know that the record count should not be 0 for all criteria.
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code: TRANSFORM IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT SELECT PT_LEVEL.INF_YEAR, PT_LEVEL.INF_MONTH, PT_LEVEL.UNIT
I'm looking for someone that knows how to link check boxes on a form to the "show" check-box in a query. Basically, all I'm trying to do is have the end-user tell the query which fields to show in the output. If checkbox "A" is checked on a form prior to kicking off the query, I'd like field "A" to show in the query output...and so on.
I have a query driving a report that fills in the on time or late based on comparing the release date to the production due date. The code works except for if we haven't released it yet and the release date is void. I need it to then check versus today's date and then populate the late/on time. But I need this to take a back seat to if both fields are populated. Here is the code I am currently using and I have attached a screen shot of the query with the 2 boxes highlighted in red.
On Time / Late Eng: IIf([Released to Prod]<=[Prod Due Date],"On Time",IIf([Released to Prod]>[Prod Due Date],"Late",""))
I have a table which has information the count of students in classrooms around the university and I need to summarize the table by Faculty. Therefore, all I want to do is a count of students for each faculty i.e. Art and Design, Business and Law etc.
The query i put together is: SELECT Count([tbl_Audited Classroom for Week 02].Faculty) AS CountOfFaculty FROM [tbl_Audited Classroom for Week 02] HAVING (((Count([tbl_Audited Classroom for Week 02].Faculty))="BAL"));
So when I run the query I get the error message 'Data Type mismatch in criteria expression'. The Faculty field is a text field, so I don't know if that would make a difference.
I am trying to , create a field using the IIf function that will display a $250 bonus for agents every time they sell a car for at least $20,000. Otherwise, the function will return a 0. Name the field Bonus. I put in Bonus: IIf(SalePrice >= "20,000" Then + "250",True, 0) and it is saying that I have an improper syntax.
In a query, I'd like there to be a column with the date of the most recent approval (max date) for each document. Using the following formula in the Expression Builder gives me the error below.
"The expression you entered has a function containing the wrong number of arguments"
How can I get the most recent date a document was approved by any approver?
BTW - I initially thought I could just use the date the record was last updated. However, sometimes people remove their approval. This causes the LastUpdated date to be wrong (for this purpose), since it then reflects the date they removed their approval, not the most recent date that someone gave it.
How do I use the DateValue function with a query that pulls data between dates? Here is my code below. I'm getting an error that my expression is too complex or incorrect.
Code: SELECT IIf([tblAUCodes].[BusinessLine] Like '*CMES*',"CMES",IIf([tblAUCodes].[BusinessLine] Like '*HOUS*',"CMES",IIf([tblAUCodes].[BusinessLine] Like 'CTO',"CTO",IIf([tblAUCodes].[BusinessLine] Like 'Shareowner*',"WFSS","SPS")))) AS LOB, Count(tblPACSTemplate.temID) AS CountOftemID, tblPACSTemplate.Type, DateValue([ApprovedDate]) AS ApproveDate
I am currently designing a dashboard for my team which works on projects, I have a main table which has main fields which i use for my dashboard
Project Type,Project ID (Primary Key), Submitted by, Project Start date, Project end date, Status
The tricky part is that the raw data has projects which got started couple of years back some got closed in the previous fiscal year but some are still being worked upon..I was able to put in a where clause to only display projects whose close dates either have null value ( projects which are still being worked) and where the close dates are >= 1/1/2013. My Dashboard needs to have the below fields
Project Type, Count of Projects, Count of Closed Projects, Average days to complete, Count of Active Projects & Average Active days
I was able to get it correct except the last field Average Active days as it is displaying Average no's even against the project type which doesn't have any active projects or where the active project count is zero.
SELECT TblWO.[Operational* Categorization Tier 2], Count([TblWO]![Operational* Categorization Tier 2]) AS [Count], Avg([TblWO]![SLA in Days]) AS [Internal SLO in Days], Count([TblWO]![Closedate]) AS [No Closed Projects], Round(Avg([TblWO]![Closedate]-[TblWO]![Work Order Submit Date Time]),2) AS [Avg Close Days], [Count]-[No Closed Projects] AS [No Active Projects], Round(Avg(Date()-[TblWO]![Work Order Submit Date Time]),2) AS [Active Avg Days] FROM TblWO WHERE ((([TblWO]![Closedate]) Is Null Or ([TblWO]![Closedate])>=#10/1/2012#) AND (([TblWO]![Status])<>"Cancelled" And ([TblWO]![Status])<>"Rejected")) GROUP BY TblWO.[Operational* Categorization Tier 2] HAVING (((TblWO.[Operational* Categorization Tier 2])<>" "));
I have a query that I'm working on through Access 2010's design view. I'd like to add a criteria to the query where it only shows results with the employee name column matching a global variable I created that stores the name of the currently logged in employee.
Here's my vba code that declares the global variable and the public function i'm trying to pass as criteria in the query:
Global gbl_loginName As String Public Function returnName() As String If IsNull(gbl_loginName) Then returnName = "test" ' dummy account created for development only Else returnName = gbl_loginName End If End Function
and here's the SQL code from Access's design view:
SELECT [Entry of Hours].WC, [Entry of Hours].[Employee Name], [Entry of Hours].[Set Up Time], [Entry of Hours].[Run time], [Entry of Hours].[Traveler Number], [Entry of Hours].[Entry Date], [Entry of Hours].[Quantity Finished], [Entry of Hours].Notes, [Entry of Hours].WPS,
[Code] ....
when I try running the query, however, I get this error:
'returnName' is not a recognized built-in function name
Is there a problem with using public functions in Access' design view?
