IIF Function As Query Criteria
Nov 14, 2007
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: -
=IIf([Forms]![LevyReportDialog]![DialogCombo]="SAL","True","*")
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.
View Replies
ADVERTISEMENT
Jun 3, 2012
Is there someway within Access itself or the documentation to see a complete list of:
Functions such as sum, median, average
Operators such as "and" "or"
View 2 Replies
View Related
Aug 16, 2014
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.
Code:
=IIf(IsNull([Forms]![FormName]![FormControl]),"",[Forms]![FormName]![FormControl])
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.
Code:
"" Or Is Null
View 3 Replies
View Related
Aug 11, 2006
I have the following IIF function in the criteria of a date field.
IIf(Weekday(Now())=6,Between Now()-2 And Now()-10)
I want to return dates between 2 and 10 days ago, if today is Friday, but this doesnt return any values at all.
Is the statement above correct ?
TIA.
Mark
View 3 Replies
View Related
Feb 5, 2014
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?
View 3 Replies
View Related
Aug 10, 2007
Dear Access Query Expert
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.
Thank you so much.
View 1 Replies
View Related
Oct 10, 2007
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] & "'")
View 1 Replies
View Related
Jan 31, 2015
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
Me.DAYS_TRAINED.Value = DLookup("Days", "TRAINED LH A-PILLAR", "ID=" & FTM_NAME)
So I think I don't understand the multiple criteria part.
View 3 Replies
View Related
Oct 8, 2013
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.
View 4 Replies
View Related
Oct 7, 2014
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:
=DCount("[EncounterID]","tblEncounters","[LineName] = ' " & "Forms![frmCalendar]![cmbLineName] = " ' ")
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.
View 4 Replies
View Related
Dec 11, 2013
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies
View Related
Mar 18, 2013
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
'IIF(Tbl_SKG.Value_Grp = 1,sum(Tbl_Advisor_raw.ValuePay),0)'
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,
[Code] .....
View 9 Replies
View Related
May 15, 2013
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.
View 11 Replies
View Related
Jan 20, 2014
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
View 8 Replies
View Related
Aug 10, 2015
I have the following code
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.
View 8 Replies
View Related
Jun 25, 2014
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.
View 14 Replies
View Related
Jul 22, 2014
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.
View 3 Replies
View Related
Oct 21, 2006
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
Any help would gratefully be appreciated. Thanks
View 5 Replies
View Related
Aug 20, 2007
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
StoreSubjectSalesSales %
1516Fiction56431.5-24.15%
1516Audio Unabridged1650.8-231.04%
1516History / Military History10081.1-29.99%
1516Role Playing / Graphic Novels14773.9-20.27%
1516Mystery13152.6-19.84%
1516Audio Abridged1785.9-141.84%
1516SciFi / Fantasy27535.3-7.93%
1516Juv Audio/Video1580.6-100.13%
1516Biography8103.6-15.89%
1516Sports7910.8-15.64%
1516Current Affairs / Law8141.9-14.34%
1516Reference7183-16.22%
1516Juv Non-Bk4585.9-25.02%
1516Science / Tech2961.4-33.98%
1516Movies / TV / Music / Dance3395.3-29.46%
1872Fiction307344.3-7.49%
1872Business134307.5-13.48%
1872Psych / Self Improvement100650.4-10.05%
1872Audio Unabridged29165.9-27.32%
1872Cookbooks57463.3-13.56%
1872Computers59235.7-12.37%
1872Regional59883.4-11.22%
1872Health & Fitness64713.8-10.29%
1872Maps19358.4-27.66%
1872Current Affairs / Law47927.1-11.08%
1872Travel Foreign42583.7-12.27%
1872Religion / Bibles80255.6-6.07%
1872SciFi / Fantasy67641.4-6.49%
1872Study Aids / Notes38299-11.24%
1872Games41745.1-9.79%
View 2 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Mar 5, 2007
Please help, the attached code works fine in Windows XP but twhen run on a Windows 2000 pc it fails on the create object, any ideas?
Function GetXmlFilename()
Dim objDialog
GetXmlFilename = ""
Set objDialog = CreateObject("UserAccounts.CommonDialog")
objDialog.Filter = "XML Files|*.xml|All Files|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "C: emp"
If Not objDialog.ShowOpen Then
Exit Function
End If
GetXmlFilename = objDialog.Filename
End Function
Thanks in advance
View 2 Replies
View Related
Jan 17, 2006
I have written a simple function that I would like to use in a query. The function would return a value for each record where the parameters equals 2 different fields of the same record.
Private Function FctConvertInch(LineDim As String, LineShape As String)
'To Convert a String to a value and then convert that value from mm to inches
'Lines dimension = 00.0000x00.0000 if line is flat
'Lines dimension = 00.0000 if line is round
Dim StNum1 As String
Dim StNum2 As String
Dim DbNum1 As Double
Dim DbNum2 As Double
If LineShape = "round" Then
'"CDbl" convert a string to Double number
FctConvertInch = CDbl([LineDim]) / 25.4
ElseIf LineShape = "flat" Then
StNum1 = Left([LineDim], 7)
StNum2 = Right([LineDim], 7)
DbNum1 = CDbl(StNum1)
DbNum2 = CDbl(StNum2)
FctConvertInch = (DbNum1 / 25.4) & "x" & (DbNum2 / 25.4)
End If
End Function
I have put the function in a new module, and have try to use the function in a query with the fields LineDim and LineShape , I've also added the field FctConvertInch(LineDim,LineShape) but it doesn't work. Am I using the function the the correct way?
View 10 Replies
View Related
Jul 25, 2006
i want to write a query where i ask it to update a table where the text is
like "Ack. Stamp"
And the lenght is less than 15
how do i do this
View 1 Replies
View Related
Dec 14, 2006
Hi to all,
I have a table consisting of 12 columns of data type number. for each row i want to display a count wherever the value of a row is greater than 100, that is, how many times the value greater than 100 appear in the 12 columns.
PLease help???
Thanks
View 1 Replies
View Related
Jul 5, 2007
I have a field in a query that I want to select the value from a field, but if the value is null, then to select the value from the same named field in a different table.
Basically the whole point is to use the given description unless one doesnt exist, where the default one will be inserted instead.
Cheers,
View 5 Replies
View Related
Jul 12, 2007
I have a field I want to calculate using IIF. There are about 40 possible values, thus I have about 40 nested IIFs. Because I get "query is too complex" when I try to nest all 40 IIFs, I've had to break this down into 3 fields. I then have to do a little more manipulation to end up with the 1 field I originally wanted. All that works fine, however, I'd like to be able to calculate the value in 1 field instead of 4 fields.
I'm not too good with VBA, but looking at some examples, I tried creating the following function (this is just a small sample, but if I'm on the right track there will be about 40 of these "Step" variables and ElseIfs):
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Function IfThen(BYStep As String)
Dim test2 As Integer
Dim Step070 As Integer
Dim Step075 As Integer
Dim Step080 As Integer
If
BYStep = "07.0" Then
test2 = Step070
ElseIf BYStep = "07.5" Then
test2 = Step075
ElseIf BYStep = "08.0" Then
test2 = Step080
Else: test2 = 0
End If
End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In my query I have:
IfThen([BYStep]) AS Test2
It took me awhile to get to the above where when I ran the query I didn't get a message about the wrong arguments. Now, I don't get an error message, but the field Test2, comes up empty for every record.
Is there a way to do what I'd like to do (having the 40 if/then/elses in a function, and then using that function in 1 field in the query)?
View 14 Replies
View Related
