Using A Function In A Query For If/then/else
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 Replies
ADVERTISEMENT
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
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
Jan 24, 2006
How do you use the IIf function in a query if you want to specify more than one paremeter?
eg. IIf(MotherBirth="Australia", "Oceania", "Other") works but what happens if Oceania applies to Australia, Fiji and New Zealand?
I tried IIf(MotherBirth="Australia" or "new Zealand" or "Fiji", "Oceania", "Other") but this doesn't work...
What am i doing wrong?
View 1 Replies
View Related
Jan 30, 2014
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error
View 2 Replies
View Related
May 18, 2006
Hey guys, can anybody help
here is what I have:
Job
JobId-----PK
Name
Position
PositionId----PK
Name
Activity
ActivityId-----PK
Name
JobTask as you can see the relationship between Job and Task is M-M
JobId-----PK
TaskId-----PK
PositionTask as you can see the relationship between Position and Task is M-M
PositionId-----PK
TaskId-----PK
ActivityTask as you can see the relationship between Activity and Task is M-M
ActivityId-----PK
TaskId-----PK
Task
TaskId-----PK
TaskName
TaskCompetency as you can see the relationship between Task and Competency is M-M
TaskId-----PK
CompetencyId-----PK
Competency
CompetencyId-----PK
CompetencyName
Keyword
Priority
I have a query like this:
The user will input the JobId, PositionId, ActivityId.
I want to return all the TaskName ,CompetencyId, CompetencyName
Where the following:
The highest priority within the same keyword
Basically group by keyword then pick the highest priority I have done this:
SELECT Max(Competency.Priority), Competency. Keyword
FROM Competency INNER JOIN ((ActivityTask RIGHT JOIN (positionTask RIGHT JOIN (JobTask RIGHT JOIN task ON JobTask.TaskID = task.TaskID) ON positionTask.TaskID = task.TaskID) ON ActivityTask.TaskID = task.TaskID) INNER JOIN TaskCompetency ON task.TaskID = TaskCompetency.TaskID) ON Competency.CompetencyID = TaskCompetency.CompetencyID
WHERE (((JobTask.JobID)=[job])) OR (((positionTask.PositionID)=[position])) OR (((ActivityTask.GroupID)=[Activity]))
GROUP BY Competency.CompetencyKeyword;
It works fine, it returns the all the keyword, and its highest priority.
But I can’t return the Competency.CompetencyId, Competency. CompetencyName with the aggregate function Max
View 2 Replies
View Related
Sep 19, 2007
I'm trying to use sum() along with UPDATE as shown below
UPDATE ALLDATA INNER JOIN [Total Data] ON ALLDATA.PanNumber=[Total Data].PanNumber SET ALLDATA.PLAForMonthPrevYear = sum([Total Data].PLAForMonth), ALLDATA.CENVATForMonthPrevYear = sum([Total Data].CENVATForMonth), ALLDATA.PLAUptoMonthPrevYear = sum([Total Data].PLAUptoMonth), ALLDATA.CENVATUptoMonthPrevYear = sum([Total Data].CENVATUptoMonth)
WHERE [Total Data].Month=Forms![FORM REPORT]!Combo3;
IT IS THROWING ERROR
PLS HELP ME
View 1 Replies
View Related
May 11, 2005
I was wondering if anyone can point me in the right direction please.
I have created a new module in my Access database (in the module object view) and I can see it in the module view. I am also able to call it in the expression builder under Functions.
Unfortuantaly if I create a query I am unable to use it. If I create it for the first time and open the query and use it, it works lovely. But if I exit my database and go back in, it does not work. I can still view it in the modules and it is still in the expression builder under functions.
My function is as follows;
Public Function GetLabourCost(WorkCodeID, PayRate, Hours) As Currency
Select Case WorkCodeID
Case "7800"
GetLabourCost = [PayRate] * 2 * [Hours]
Case "7500"
GetLabourCost = [PayRate] * 1.5 * [Hours]
Case "1000"
GetLabourCost = [PayRate] * [Hours]
Case "2000"
GetLabourCost = [PayRate] * [Hours]
Case "7000"
GetLabourCost = [PayRate] * [Hours]
Case "8000"
GetLabourCost = [PayRate] * [Hours]
Case "9000"
GetLabourCost = [PayRate] * [Hours]
Case "9500"
GetLabourCost = [PayRate] * [Hours]
Case "9999"
GetLabourCost = [PayRate] * 0 * [Hours]
Case "9550"
GetLabourCost = ([PayRate] * [Hours]) / 2
Case "9560"
GetLabourCost = [PayRate] * 0 * [Hours]
Case Else
GetLabourCost = 0
End Select
End Function
In my query I have called it as;
Expr1: GetLabourCost([WorkCodeID],[PayRate],[Hours])
I would be grateful of any advice.
Thanks
View 1 Replies
View Related
Aug 9, 2005
HOw to use the format function . I had tried to use it but does not work for me .
when i click properties of the particular column in a query and go to format tab , i type in mm:dd:yy( i want to change the format of date ) and then execute the query but nothing happens.
can someone help me ?
View 5 Replies
View Related
Aug 31, 2005
I have designed an attendance database, with fields for personal details and fields for the days of each month.
I need to have a query that will ask for a certain date(i.e. find the specific field) and then search on specific criteria (i.e. ON SITE) to see which staff are available.
What is the best way of doing this? Any help would be greatly appreciated
View 4 Replies
View Related
Apr 5, 2006
Hi all,
Another quick Q - am a newbie at Access!
I am performing the following query:
.Open "SELECT DISTINCT([Date]), SUM([Batch Qty]) As [Batch] FROM [" & TableName & "] " & _
"WHERE [Date] BETWEEN #" & sDate & "# AND #" & eDate & "# " & _
"GROUP BY [Date], [Batch Qty]", cn, , , adCmdText
Data Produced:
29/03/200610
29/03/200620
29/03/2006100
30/03/200660
30/03/200680
30/03/20065
30/03/20065
How do I Sum the Totals for the Dates? So it produces:
29/03/2006130
30/03/2006150
TIA
View 1 Replies
View Related
Sep 5, 2006
Hi all,
I have a local table that has data imported from a spreadsheet
and we have discovered that one of the fields brings in trailing spaces. Since the Access DB has some querys to manaipulate the data I thought I would just create a qry that would trim the value of that field but I am getting error messages when I try to run the query.
Basically it is a update query with the field value written as:-
Code: Trim([Code])
I am leaving the update field in the qry blank and it complains about it not having a destination field which is understandable so how do I go about trimming the value of a field in a query. Is it even possible ?
Thanks in advance,
Mitch....
View 4 Replies
View Related
Nov 11, 2006
Hi All,
I made a function to calculate the difference in hours between two times.
Function TimeDiff(Time1 As String, Time2 As String) As Double
'This function is used to calculate the difference in hours between two times.
'It is capable of handling a finish time that is after midnight.
TimeDiff = 0
If Not IsNull(Time1) And Not IsNull(Time2) Then
If CDate(Time1) > CDate(Time2) Then
TimeDiff = ((CDate(Time2) - CDate(Time1)) + 1) * 24
Else
TimeDiff = (CDate(Time2) - CDate(Time1)) * 24
End If
End If
End Function
I have a query where there are 2 sets of times in fields (Start1, Finish1, Start2, Finish2).
When I add a calculated column with the following expression:
Expr1: TimeDiff(Start1,Finish1) + TimeDiff(Start2,Finish2)
it calculates in the query correctly.
However, when I made a new function that incorporated this function, it gave me the #error result in the calculated field.
Function OrdinaryHours(Status As String, Start1 As String, Finish1 As String, Optional Start2 As String, Optional Finish2 As String) As Double
'Initialise
OrdinaryHours = 0
'If employee is NOT a casual
If Not Status = "CT" Then
If Not IsMissing(Start2) And Not IsMissing(Finish2) Then
OrdinaryHours = TimeDiff(Start1, Finish1) + TimeDiff(Start2, Finish2)
Else
OrdinaryHours = TimeDiff(Start1, Finish1)
End If
End If
End Function
Does anyone know why this would #Error. I have the TimeDiff function initialising to = 0 before doing anything...I am stumped why it returns #Error when it should return 0 in the case of something going wrong.
Brad
View 5 Replies
View Related
Dec 5, 2006
Hello,
I have a query problem and I have no idea how to solve it.
In the query there are 4 fields:
1) ID 2) PageRank 3) PageRankNormalized 4) PageRankValue
1) ID is the Primary Key
2) In the PageRank field there are values from 1 to 10
3) In the PageRankNormalized there is an expression PageRankNormalized: (3^[PageRank])
4) In the PageRankValue there is another expression PageRankValue: ((3^[PageRank])*100)/ Max ( [PageRankNormalized] )
Now when I run the query I get the following error message:
You tried to execute a query that does not include the specified expression 'ID' as part of an aggregate function. (Error 3122)
What I am trying to do is to calculate the PageRankValue (4) through an expression that divide ((3^[PageRank])*100) by the maximum value shown in the calculated field PageRankNormalized (3)
------------------SQL------------------------
SELECT [UNIVERSITIES by name].ID, [UNIVERSITIES by name].PageRank, (3^[PageRank]) AS PageRankNormalized, ((3^[PageRank])*100)/Max([PageRankNormalized]) AS PageRankValue
FROM [UNIVERSITIES by name];
---------------------------------------------
I am stucked and I am not sure how to use this Max function in a calculated query.
Thanks for your help !! :)
FF
View 1 Replies
View Related
Mar 19, 2007
hello
i have a query summing sales data by month.
what i need to do is be able to list in this query the sales summed for same period last year.
I have some other criteria but this is the just of it.
trying to use a dsum function in the query itself and using product id and date range derived from fields that calc starting date last year and ending date last year for time period.
example , sum this years jan and feb 2007 sales and also have the dsum function sum jan and feb sales of 2006 in a field right next to the 07 values.
I can not get the dsum function to work???
any
ideas????
i enen tried creating a temp table with the 07 sales data summed and values for each period in seperate fields ie date1 = 01/01/06 and date2 = 1/31/2006 and the same for feb. The dsum did not work that way either.
If i hard code the dates into the dsum it works by i would like it to be more dynamic based on the date1 and date2 values.
View 1 Replies
View Related
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 9 Replies
View Related
Nov 20, 2007
I started a thread in the VBA section about a problem with a function I'd written. I think it's more to do with how I'm implementing it in a query.
http://www.access-programmers.co.uk/forums/showthread.php?t=139066
Please help.
View 4 Replies
View Related
Jan 19, 2008
Can the replace function be used in a query?
I have used this
=Replace(Format([SITELATSEC],"00.0"),".",",") in a report and it works fine.
I have the following expression in a query that is output to a report.
LAT: "Latitude: " & [SITELATDEG] & "º " & [SITELATMIN] & "' " & [SITELATSEC] & """ "
I need to be able to display [SITELATSEC] with a comma instead of a decimal point. I tried
LAT: "Latitude: " & [SITELATDEG] & "º " & [SITELATMIN] & "' " & =Replace(Format([SITELATSEC]00.0"),".",",") & """ "
and got an error message about too many commas.
On a more general note: Has anyone ever actually finished a database - ie got to the point where no changes needed to be made? 18 months after this started, and I'm still tryng to accomodate what my manager wants!
View 3 Replies
View Related
Jan 21, 2008
Hi Folks,
Does anyone know how I can use the Proper Function in an append query.
I have a field whereby all the data is in UPPER case, but I only want the first character of each word to be upper case.
I have looked up the Proper function, which should do the job, but when I apply it in the specified field I get an "Undefined function "Proper" in expresssion message
my expresssion is as follows: salutation: Proper([strSalutation])
Any assistance would be greatly appreciated.
John
View 6 Replies
View Related
Mar 15, 2008
Hi, i know ive asked this in another forum, but i just read a thread on a query function which may be able to deal with this so thought id post it here:
the function i just saw:- Expr1: IIf([Sold],="","0",[Sold])
this does nothing to help my problem though so any ideas?
i have three fields; [boughtin], [sold] and [instock]. [boughtin] comes from one query and [sold] from another.
[instock] = [boughtin] - [sold]
because [sold] only has a value once an invoice has been created for that item, it means that if you try to view an item that hasnt been sold yet, you see how many have been bought in, but no sold value and then because of this, no stock value. (you just get an '#error' value in the stock box.
basically what i need is this:
if both [boughtin] and [sold] are present, then the calculation is done and a [stock] value is shown.
if only [boughtit] is present then the stock value simply shows the [boughtin] value.
[boughtin] is located on one subform (frm_stocksub) and [sold] is one another (frm_stocksubsub).
i need a code to put into the after update event of the main combo on the main form so that it detects this and then displays the approriate value once both queries have run.
a simple idea of what i want is:
If [Sold] has no value Then
[InStock] = [BoughtIn]
Else
[InStock]=[BoughtIn]-[Sold]
End If
Obviously there is far more detail in terms of locating the particular text boxes, but i hope it gives you an idea of what i want.
Thanks
Jared James
View 2 Replies
View Related
Apr 2, 2008
I am using CDate to convert a date text string into a date value, which is then listed in ascending order in the result.
If I leave the default format as 'dd/mm/yy' the data is in the correct date ascending order when the query runs but if I use -
Format(CDate(etc, etc),"dd mmm yy")
The query is in alpha/numeric order as if the value is a text string and not a date value.
Not entirely sure why it is doing this when all that I have changed/added is the 'Format' function.
View 5 Replies
View Related
