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

Is It Possible To Use Variables In Sql?

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

Sum IIf And Three Variables

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

Using Variables

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

SQL Variables

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

Holding Variables

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

Query Using Variables

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

Linkcriteria Using 2 Variables?

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

Global Variables

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

Environment Variables In 2007

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

SQL Pass Through Form Variables

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

Can Variables Be Used In A Form Control??

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

Global Variables As Criteria

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

Using A Form For Criteria Variables

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

Multiple Variables In Query

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

Passing Variables To Queries

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

Calculate Days When There Are 4 Variables

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

SELECT SQL Query With Variables

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

Payment Date With To Many Variables?

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

Refer To Controls Using Variables

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

Programming With Control Variables

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

Query Syntax & Use Of Variables

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

Passing Public Variables

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

Variables And Record Counts

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

Working With Time Variables

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

Forms :: How To Get Average Of Two Variables

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







Copyrights 2005-15 www.BigResource.com, All rights reserved