How To Use IIf With Three Variables
Aug 15, 2006
Hi, here is a query which returns rolling sum for past5days.The resulting table has threee columns,namely,
MyDateFiled ,MyValue,Past5DaySum
SELECT tblData.MyDateField, tblData.MyValue,
(SELECT SUM(a.MyValue)
FROM tblData As a
WHERE DateDiff("d",a.MyDateField,tblData.MyDateField)
Between 0 and 4)
AS Past5DaySum
FROM tblData;
Someone help me in modifying the above query ,so that ,a Type(1,2,3) can be assigned to each Past5DaySum
based on the following criteria.Is,it possible to use IIF function for three variables ?,so as tp get a new table(Mydate,MyValue,PastDaySum,Type)
Type,LeanPeriod,GoodPeriod
1, A<5 , A<20
2, 5<=A<=10, 20<=A<=40
3, A>10, A>40
LeanPeriod=Jun to Nov
GoodPeriod=Dec to May
View Replies
ADVERTISEMENT
May 13, 2007
Hi there, is it at all possible to use variables in sql? Please consider the following snippet:
... (WHERE [DateDue] < Date())...
The idea is to return a recordset where the DateDue is prior to the current date. But sometimes I need the opposite, ie a recordset where DateDue is in the future: ... (WHERE [DateDue] > Date())...
Right now I have 2 long queries which are exactly the same, except for the operator < for the first case above and the operator > for the second.
I of course did not want to repeat the queries so I tried:
Dim op As String
op = Me.txtOp 'txtOp is invisible and gets its value (either "<" or ">" ) thru
'a button click
... (WHERE [DateDue] & op & Date())...
I also tried:
... (WHERE [DateDue] & forms!frmInvoices!txtOp & Date())...
But to my "surprise" both failed.
Can it work? what should I correct or do differently here? I might need this kind of trick for other routines too, so I'd like to avoid copying the same query, just for the operator change.
I hope one of you seasoned sqlers can tell me more about it, I appreciate.
Regards,
Jaime
View 10 Replies
View Related
Mar 3, 2006
I want to create a field named ErrorCases that sums the rows meeting the following conditions: The row has a ValidityReason = 2 and for the same row ExpValidity is either null or 3. I want a count of the rows meeting these condtions.
Here is the formula I have constructed that does not work.
ErrorCases: Sum(IIf(([ValidityReason]="2" And [ExpValidity] Is Null) Or ([ValidityReason]="2" And [ExpValidity]="3"),1,0))
Any suggestions?
View 2 Replies
View Related
Jan 14, 2005
I am trying to incorporate the following code into my code:
Code:If Not QueryExists("Q_ABC") Then Set qdf = db.CreateQueryDef("Q_ABC") Else Set qdf = db.QueryDefs("Q_ABC") End If
Difference is that I want to use a variable to provide the query name...
Code:dim queryname as variantqueryname = "Q_ABC" If Not QueryExists(queryname) Then Set qdf = db.CreateQueryDef(queryname) Else Set qdf = db.QueryDefs(queryname) End If
but this does not work... but neither does this...
Code:dim queryname as variantqueryname = "Q_ABC" If Not QueryExists("queryname") Then Set qdf = db.CreateQueryDef("queryname") Else Set qdf = db.QueryDefs("queryname") End If
nor does this...
Code:dim queryname as variantqueryname = "Q_ABC" If Not QueryExists queryname Then Set qdf = db.CreateQueryDef queryname Else Set qdf = db.QueryDefs queryname End If
Can someone please show me how this is to be done?? I feel that it must be something simple...
Thanks a lot (in advance)!
View 5 Replies
View Related
Oct 28, 2007
The following code recognizes the value of 'TheYear' after running the line of code (ie. "#11/13/2007#"). The problem is it doesn't seem to recognize it in the SQL, but rather asks for a value when I reach the SQL code line. How can I get it to substitute automatically? THANKS!
DoCmd.SetWarnings False
TheYear = "#11/13/" & Year(Now()) & "#"
DoCmd.RunSQL "Update [Employees Trained List] SET [Employees Trained List].[This Years Training Date] = Null WHERE (([Employees Trained List].[This Years Training Date])< TheYear );"
DoCmd.SetWarnings True
View 1 Replies
View Related
Mar 9, 2006
I have started holding variables in text boxes on forms - (variables needed at module level for that form).
Is this a correct thing to do.
The benefits I have are not loosing the value should some error occur - and being able to reference it from another form.
Thanks
Paul
View 3 Replies
View Related
Mar 6, 2008
I am currently making a project as part of my Computing course. The project is a running diary, where users can log in and record their running times(Amongst other things). As part of the system i am going to include a calorie calculator, to do so i have to work out the BMI (Body Mass Index). To do this you have to divide the weight by the square of the height. The weight and height are stored in the tables.
I am planning on creating a form for this conversion which will refer to a query. When the users log-in their ID is stored in a Public Variable called "UserID".
What i would like to do is query the database for their height and weight using this variable, but i don't know how to go about doing this.
Does anyone have any suggestions?
Many Thanks
View 6 Replies
View Related
Nov 2, 2005
Hi
Currently i use one variable as indicated below from a continuous sub form field to open another form
Dim linkcriteria As String
Dim frmRoom As String
frmRoom = "frmRoom "
linkcriteria = "[HeadCloseID] = " & Me.HeadCloseID
DoCmd.OpenForm frmRoom , , , linkcriteria
however, after restructuring some tables, i need to open the form with respect to 2 variables instead of the previous one.
hence my question is can, how, and what code would i use to do this
many thanks in advance
View 3 Replies
View Related
Feb 13, 2006
I have an access form called forma. It has a command button. When I click a command button, FormB opens up and FormA closes. The FormB also has a command button. When I click the command button of Form B, it closes and opens FormC. Like this there are total 30 forms. What I need is. I want to count the no. of clicks of the command button in total. Actually each button is a correct option of A multiple choice question. I tried to declare a public Variable called total in the declareation of the first form and tried to use it directly in the succeding forms. It does not capture the values. How can I increase the value of the varialbe declared in the first form even after the form closes and capture till end. Actually I also should be able to send the value in a table. Please Help Me.!
View 2 Replies
View Related
Jul 10, 2007
Just updated to Access 2007 from 2003 and my app does not pull in any environment variables. =environ("variablename")
Anyone else have this problem?
View 5 Replies
View Related
Feb 3, 2008
I know this is probably obvious but how do I pass through form variables to sql server I currently have:
exec QStudent @_param_cmbYear="0708", @_param_SelID="S", @_param_SelForename="d", @_param_SelSurname="S"
this executes correctly SelID being a student ID or partial ID also allowing for surname forename partial search. I thought it would just be:
exec QStudent @_param_cmbYear=[Forms]![Attendance and Lateness Main]![cmbYear], @_param_SelID="S", @_param_SelForename="d", @_param_SelSurname="S"
to switch acad year to a form variable and repeat for the others but it errors on trying to save the pass through. Thanks for the help.
View 1 Replies
View Related
Mar 5, 2008
hi all, Newbie to access and am making an asset tracking database for work. I have written a sub that opens a hidden form and queries it. I want to get a value from that hidden form and stick it into a combo box on my origianl form. I have got most things to work. The problem i am having is using a variale in a control form i.e. [forms]![ & strVar & ]![Field] here is the code i have so far
Private Sub Form_Search(strFname As String, strField As String, strFField As String, strWhere_Next As String)
Dim strWhere As String
strWhere = strField & " = " & "" & strFField & ""
strWhereA = "[Forms]![" & strWhere_Next & "]![ALLOCATEDTO]"
DoCmd.OpenForm strFname, acNormal, , strWhere, , acHidden
Me.ALLOCATEDTO.Value = strWhereA
DoCmd.Close acForm, strFname
the strWhereA shows the correct field but is shown as [Forms]![Formname]![ALLOCATEDTO] in my combobox, not the actual value of that form. Please help. I know this is probably a simple syntax error but am now at my wits end!!!
Thanks in advance!
View 6 Replies
View Related
Dec 5, 2005
Anyone know how to reference a global variable as criteria for a query?
I am currently using invisible text boxes on an intermediary form as criteria for my query and it works fine, but this solution seems inellegant and I'm looking for something a little smoother. I just need the correct syntax to reference a variable in the criteria section of a query.
Thanks!
View 1 Replies
View Related
Jan 29, 2006
I have a table Names containing Recnum and Last Name. I have a table Grades including Recnum, Names and Grades. I have a Report which Through a query Reports the grades and averages for each name. I would like to be able to query for an individual name using an entry form. How do I get the name from the form into the Criteria in the query grid.
View 1 Replies
View Related
Mar 21, 2006
I've built a pretty extensive database for work thanks largely to the folks on this board. But I'm stuck. I have a report to track inspections and it works fine, but I'd like to filter it down by AFSC (a coded expression the AF uses to denote career fields, i.e. 2A5x1x is Aircraft Maintenance.) The report's queries come from a couple of sources to retrieve the requirements for the report. For instance that AFSC has to start w/ "2A" and the labor code needs to start w/ "1" and not be "120". No problem. The problem I'm having is that the first 'x' (from the AFSCs below) is based on skill level (can be a 1, 3, 5, 7, or 9) and the last x is sometimes there and somtimes isn't.
For this particular report I've broken down the AFSCs down to three different formats. They are as follows:
2A5x1x (i.e. 2A531B or 2A571)
2A5x3x (i.e. 2A533A, 2A55B, 2A57C)
2A6(or 7)x1(or 2,3,4,5,or 6)
The report is designed to show who is due an inspection and it works fine. But I need to try to make it a little more user friendly so that all the various workcenters can trim it down to view just their career fields. Right now it reads from a query.
Here's the SQL for it.
SELECT qryPEDueUnion.[Main Assessee], [Employee List Table].[Employee RANK], [Employee List Table].[Employee NAME], [Employee List Table].AFSC AS FilterAFSC, [Employee List Table].[Labor Code] AS FilterLaborCode, qryPEDueUnion.[Inspection Type], Last(qryPEDueUnion.Date) AS LastOfDate
FROM qryPEDueUnion LEFT JOIN [Employee List Table] ON qryPEDueUnion.[Main Assessee] = [Employee List Table].EMP
GROUP BY qryPEDueUnion.[Main Assessee], [Employee List Table].[Employee RANK], [Employee List Table].[Employee NAME], [Employee List Table].AFSC, [Employee List Table].[Labor Code], qryPEDueUnion.[Inspection Type], Left([AFSC],2), Left([Labor Code],1)
HAVING ((([Employee List Table].[Labor Code])<>120) AND ((qryPEDueUnion.[Inspection Type])="PE") AND ((Last(qryPEDueUnion.Date)) Between DateAdd("m",-19,Now()) And DateAdd("m",-15,Now())) AND ((Left([AFSC],2))="2A") AND ((Left([Labor Code],1))=1))
ORDER BY Last(qryPEDueUnion.Date);
Let me know if you need more info. I'm new to posting on this board and haven't quite found out how to attach files....Plus this db is pretty big and I'd have to fool around w/ a copy of it first to make it small enough to attach.
View 3 Replies
View Related
Apr 11, 2006
Hi, I am writing a script which will retrieve all of the tracks relating to whichever cd the user has chosen. The script is being written in asp and the line which sends the variable to access looks a bit like this:
sqlQuery3 = "up_getAlbumTrackInfo " & productID
Set rs3 = dbConn.Execute(sqlQuery3)
If possible could you tell me how to retrieve this value from access as I have become lost.
At present the SQL code in the query looks like this
SELECT tblTracks.trackName, tblTracks.TrackNumber FROM tblTracks
WHERE tblTracks.productID = (** variable would go here **)
Many Thanks
Tim
View 1 Replies
View Related
Jul 7, 2006
I have a query that has four date variables:
Admit Date
Discharge Date
Report Begin Date
Report End Date
I can calculate the number of days with the DateDiff function when the "Admit Date" and "Dischage Date" are within the range of the report with no problem.
The problem arises when the "Admit Date" is prior to the "Report Begin Date". In this case, the difference needs to be "Report Begin Date" and "Discharge Date".
To make this more complicated, in some cases there is no "Discharge Date", so the calculation needs to look at the first two senerio's and then calculate the days based on the "Report End Date".
Expr2: IIf([Admission Date]<[Beginning of Report],DateDiff("d",[Beginning of Report],[D/C Date / Time]),DateDiff("d",[Admission Date],[D/C Date / Time]))
Expr2: IIf([Admission Date]<[Beginning of Report] And [D/C Date / Time]>[End of Report],DateDiff("d",[Beginning of Report],[D/C Date / Time]),DateDiff("d",[Admission Date],[D/C Date / Time]))
Expr3: IIf(IsNull([D/C Date / Time]) And [Admission Date]<[Beginning of Report],DateDiff("d",[Beginning of Report],[End of Report]),DateDiff("d",[Admission Date],[End of Report]))
expr4: IIf([Admission Date]>[Beginning of Report],DateDiff("d",[Admission Date],[D/C Date / Time],0))
Above are failed attempts to get the formula right.
Can anyone assist me in getting this to work correctly.
Thanks
Dan:mad:
View 9 Replies
View Related
Feb 22, 2007
Dear All,
I am trying to perform a SELECT query in access vba to show me customer account numbers in a msgbox.
I get a Run-time error '13': Type mismatch.
I would appreciate any help. Please see below for examples of my code.
'CustomerID is a Global Variable that gets it's value from a DLookup that gets triggered after a combobox has been selected.
The combobox does the following
'===START==================
Private Sub AfterUpdate_cboCustomer
Dim iCustID as Integer
iCustID = DLookup("ID", "tblCustomers", "Customer='" & Me.cboCustomer & "'")
iCustID = CustomerID
'Returns the Customer's ID Perfect
'=========START==============
Dim strSQL As String
strSQL = "SELECT AccNo FROM tblAccNo WHERE CustID" = CustomerID
DoCmd.RunSQL(strSQL)' Errors Here
'=======END==================
End Sub
'========END=============
View 4 Replies
View Related
Aug 2, 2007
Hi
We have a special reduced rate that a client must prepay 12weeks before or sooner if a date is put in the [OverRideDate] field
I have been trying to construct a Query / If statement or should I use case statement to work out the actual payment date. Taking in to consideration all our funny little variables like
I will try to put the variable into words
[DateOfArrival]-84Days unless [OverRideDate] & Not Less than today
then check its not a [ClosedDates] from the tbl_ClosedDates
Then if it’s a Sunday –1 day as the accounts lady doesn’t work on a Sunday
Their simple????
Attached file with sample variable data
BalanceDate.mdb
Ta
FAB1
View 1 Replies
View Related
Feb 10, 2005
I have a form with 25 related sets of text-box controls:
Product1, ProdDescr1, ProdQty1
Product2, ProdDescr2, ProdQty2
... Product25, ProdDescr25, ProdQty25
I need to loop through all 25 to check that the ProdQty for each is correct, and if any of them are incorrect, I'd like to set the BackColor to Red and the ForeColor to White for that particular set of controls. Rather than coding all 25 checks, I'd like to use variables to loop through the 25 sets
I understand how to create an incrementing Integer variable, but what is the correct syntax for combining that Integer variable with the String variables in order to perform the Back/Fore color commands?
View 2 Replies
View Related
Jun 12, 2005
The following code is only for testing the usage of control variable. I want to use set statement to assign the textbox object reference to the varialbe myControl.
When I clicked the button, the system always tells me 'the object reference missing'. It seems the set statement doesn't work, but I don;t know the reason. could anyone of you please spot it out for me?
Many thanks
Option Compare Database
Public myControl As control
Public frm As Form
Set frm = Forms!Form1
Sub Command4_Click()
Set myControl = frm!Text0
myControl.SetFocus
End Sub
Sub Command5_Click()
Set myControl = frm!Text2
myControl.SetFocus
End Sub
View 3 Replies
View Related
Dec 5, 2004
All,
I've become aware that if I create a variable in the select statement like this in Access:
SELECT Table1.ID, Sum([A]+[B]+[C]) AS TotSum
That I cannot reliably use said variable later in the same statement:
SELECT Table1.ID, Sum([A]+[B]+[C]) AS TotSum
FROM Table1
GROUP BY Table1.ID
ORDER BY TotSum DESC;
It will ask me to "enter a parameter value" for TotSum. When I do, in this example, it still sorts in correctly, but in a larger more detailed query it gets a little confused. I instead have to re-use the equation like this:
SELECT Table1.ID, Sum([A]+[B]+[C]) AS TotSum
FROM Table1
GROUP BY Table1.ID
ORDER BY Sum([A]+[B]+[C]) DESC;
Is there a way around this? It seems inefficient to recompute the sum 2 times where I think I only need to do it once.
Any input or explanations?
-BT.
View 7 Replies
View Related
Nov 20, 2007
I'm into one of the subforms that will be using the public variable from the main form and am not having success passing the data from the main form over. on the subform, i created a textbox (txt_currentyear) w/ this in the control source field: =[WrkYear] & " Golf Outing"
WrkYear was the defined in a module as:
**********
Option Compare Database
Option Explicit
Public WrkYear As String
**********
I have created a listbox w/ a few years in it (current_year_listbox). this has =[WrkYear]=Me.Current_Year_listbox.Value in the after update field. i loaded the main form, selected the year, went into the subform to see if it passed the data along w/ no luck.
do i need to call or reference that module in every form or report before i can use the data from it? right now, i just get an empty field on the subform.
View 5 Replies
View Related
Feb 6, 2008
I have a telephone database with call detail records for every call. In my criteria selection, I am selecting only inbound (CallType 2), and (outbound CallType 3) call records. I need to count the total number of records in each CallType. I have very little experience using a gui type database management software like Access, (I use to use DB2 on an AS400). I am trying to create a variable like you would in VB and use it in Access put can't figure out how to do that.
Little background on the database project: In our customer service center, I need a report that displays the total number calls for each customer service rep, inbound, and outbound calls, for a specific date range. CallType 1 = intercompany calls (which I want to exclude), CallType 2=inbound CallType 3= outbound.
View 1 Replies
View Related
Feb 21, 2008
I need to have my form give he user a Start time and then an End Time.
How do I store the variables (stirng, integer?)
How do I subtract the End Time from the Start Time to get a total time??
I then need to Multiple the resulting time frame by a Rate to calculate total value...
Any thoughts?
THanks
View 3 Replies
View Related
Jan 13, 2014
I need to get an average of 2 variables eg.
target low eg 4
target high eg 8
target (to have the avg) result 6
I have been doing the following but dont work ..
=Avg([Target Low].[Target High]) result #error
=Avg([Target Low]+[Target High]) result 12
=Avg([Target Low]&[Target High]) result 48
=Avg([Target Low]:[Target High]) will not work
View 1 Replies
View Related
