I am trying to create a field that displays a count on all records which are 1 or above.
I was using =Sum(IIf([financecompany]="Black Horse",1,0)) to count individual companies but i am trying to use the value field instead.
I searched and found a thread using =Sum(IIf([amounttofinance]>0,1,0))
but this gives me an invalid syntax message?.
Any help would be greatly appreciated.
I have 3 fields with numeric values in them. I just want to know which one is greater (dmax or something) and lowest (Dmin or something)
Example
Field1
6
Field2
5
Field3
7
How do I dmax 3 fields? this would return 7
Dmin would return 5
I have a field in an MS Acess query that has records like - 1,000; 534; 769.13; 0; 654; 0, etc. It can also have few null records.I want to calculate the average of all records which are greater than zero. Ignore zero values or null values.
View 4 Replies View RelatedHi everyone!! I need some advice regarding the following query:
This is a big database with many tables and queries etc. but for simplicity lets say i have database with 2 tables (table1 and table2) both contain invoice numbers. table2 contains one record while table1 contains many. Ok simple so far but here comes the question, how can I write a query that selects all records from table1 that are greater than the value in table2, hope that makes sense? I've been trying to slog this out on my own but dont really know where start so any help will be appriciated
Thanks Steve.
Hey, cool forum. going to be really useful!
Have an assignment due in a few weeks and basically its a gym senerio.
Got a lot going so far, macros tables and forms. reports shouldnt be a problem.
Now i'm not sure this is actually expected but i'm sure i'll get high marks if i include it.
anyway:
there are 3 memberships, gold silver and bronze, gold can take 7 classes max and bronze 2 so how would that query work?
I can make it so no more than 7 member ID's are entered into a field but how will i make the queary recongnize the difference between gold and bronze?
would it be something like group by Membership and type criatera Gold and then in member ID have <=7?
if i did this how can i work that into a form?
anyway, know that might not make sense, or may make perfect sense but i just dont get it! either way thanx for reading, any suggestions would be much appriecated
I have an SQL statement that I finally have working after much help from this web site. But I want to enhance it to be able to search between two dates, I have on my search form txtStartDate and txtFinish Date. I want it to read > or = to txtStartdate or txt< or = to txtFinishDate. This is my code at present. Can anyone help please?
Private Sub cmdSearch_Click()
Dim strSearch As String
If IsNull(Me.txtExerciseName) Then 'this is to ensure user keys/select at least 1 criteria; add on rest of the controlnames as desired
MsgBox "Please enter at least one criteria"
Else
quot = """"
If Not IsNull(Me.txtExerciseName) Then
strSearch = strSearch & "SELECT tblTechnicalIncidentReport.Exercise_Name,BoxNo,Fau lt, Catagory,Time_Reported,Date,Brief_Description,TopT en,TFRNos FROM tblTechnicalIncidentReport " & _
"WHERE Exercise_Name LIKE " & quot & Me.txtExerciseName.Value & "*" & quot & _
"AND BoxNo LIKE " & quot & Me.txtBoxNo.Value & "*" & quot & _
"AND Fault LIKE " & quot & Me.TxtFault.Value & "*" & quot & _
"AND Catagory LIKE " & quot & Me.txtCatagory.Value & "*" & quot & _
"AND Time_Reported LIKE " & quot & Me.txtTime.Value & "*" & quot & ";"
End If
Me.Results.Form.RecordSource = strSearch ' Update the record source, this line is to be placed at the end of all your criteria's if end if codes
' Requery the subform
Me.Results.Requery
End If
End Sub
I managed to cludge around my earlier deciles query so now have the break levels though have run into a brick wall.
I have 2 tables (simplified below)
tbl1:
decilerank (basically a record number)
value
eg
1 150
2 160
3 170
tbl 2:
value
name
What I want to be able to do is be able to select all the names where the values lie between 150 and 160
algorithm is select tbl2 where tabl2.value > tbl1.decilerank[1].value and < tbl1.decilerank[2].value
and so on
Anyone able to help?
Edited to add:
Nothing links the tables.
Basically I am trying to use the data from one table as input into a query against the other.
eg select person where age is > 20 and < 30
where 20 and 30 are stored as seperate records in another table.
Hi,
I have a query setup, using a caculated field, to determine the greater of 2 dates, from 2 separate fields. Here is my calculated field:
LastContactDecide: IIf([Date1_Last_Contacted]>[Date2_Last_Contacted],[Date1_Last_Contacted],[Date2_Last_Contacted])
This works fine, except when there is a null value in either Date1_Last_Contacted or Date2_Last_Contacted. How do I get around this ?
Thanks !
hi,
im having trouble setting up a query in a sports day database i have to complete for school. in a form i have a combo box which allows the user to select an age group. a query is then performed using the selected age group and a report generated. the agre groups are under 13s, under 14s, under 15s, under 16s and open (open is anyone over 15 years old). the query for under 13 to under 15s works fine, however im not sure how to get the 'open' query to work. in the combo box i have the value for 'open' set as '>15', but access doesnt seem to like this and no records are selected from my tables, even though i have entries who have an age greater than 15.
does anyone have an idea how i can solve this? i would prefer to use just one query which gets an age group from a form rather than have an individual query for each age group.
thanks!
Hello All
I'd appreciate some help with a (for me) pretty complicated query Im attempting. Im trying to achieve 1 answer if the source is less then 7, and another answer if it is greater.
I've been searching through archived posts and picking up tips to the extent that I think im in the right ball park, but Im making a mistake with invalid syntex.
in plain english Im trying to calculate
If:
date range from bookings1 is 7 or less
I want to multiply the result of the date range
By the daily hire rate (from a different table)
If:
date range from booking! is over 7
I want to multiply the result of the date range
by the daily hire rate
then multiply that answer by 0.8
(to achieve a 20% discount on the total)
Cost:IF([Forms]![Bookings1]![End Date]-[Forms]![Bookings1]![Start Date]<7)*[Car]![Daily Hire Rate] IF ([Forms]![Bookings1]![End Date]-[Forms]![Bookings1]![Start Date]>7)*[Car]![Daily Hire Rate]*0.8)
I've tried quite a few variations on that and it reads right to me, but im obviously missing some vital formatting.
I know that criteria I had for calculating the date range, and multiplying that by the daily hire rate works because I've tested that and saved that version
Any help on this would be greatly appreciated
Thanks
George
I have a real estate database.
I have a table of;
Purchaser's Requirements tblApplicantRequirements
Property Price tblPropertyPrice
Property Bedrooms tblPropertySummary
I am trying to match the bedroom and price requirements within the Purchaser's Requirements table to their respective fields within the Property Price and Property Bedrooms.
My query is thus;
SELECT tblApplicantRequirements.ClientID, tblPropertyPrice.PropertyID, tblPropertySummary.Address2
FROM tblApplicantRequirements, tblPropertySummary AS tblPropertySummary_1, tblPropertySummary INNER JOIN tblPropertyPrice ON tblPropertySummary.PropertyID = tblPropertyPrice.PropertyID
WHERE ((([tblApplicantRequirements]![PurchasePrice])>=[tblPropertyPrice]![PropertyID]) AND (([tblApplicantRequirements]![Bedrooms#])<=[tblPropertySummary]![Bedrooms#]))
GROUP BY tblApplicantRequirements.ClientID, tblPropertyPrice.PropertyID, tblPropertySummary.Address2;
The problem is that the query is returning houses that fit one requirement (ie. they might be lower in price than the applicant is willing to spend) but not the other (ie. they may have 3 bedrooms when the applicant requires 4).
Any help to fix this much appreciated.
Thanks, J.
usingSELECT TblCustInfo.Name, TblCustInfo.Addr, TblCustInfo.City, TblCustInfo.State, TblCustInfo.Zip, TblCustInfo.SrvAmt, TblCustInfo.CID, TblCustInfo.BLCAT, Sum([TblCharges.Chargeamt]) AS SumOfCharges, Sum([TblPayments.Creditamt]) AS SumofCredits, ([SumofCharges]-nz([SumOfCredits])) AS RunBalance, TblMemo.InvmemoFROM ((TblCustInfo LEFT JOIN TblMemo ON TblCustInfo.State = TblMemo.State) LEFT JOIN TblCharges ON TblCustInfo.CID = TblCharges.CID) LEFT JOIN TblPayments ON TblCustInfo.CID = TblPayments.CIDGROUP BY TblCustInfo.Name, TblCustInfo.Addr, TblCustInfo.City, TblCustInfo.State, TblCustInfo.Zip, TblCustInfo.SrvAmt, TblCustInfo.CID, TblCustInfo.BLCAT, TblMemo.Invmemo, TblCustInfo.CancelHAVING (((TblCustInfo.BLCAT)="m1") AND ((TblCustInfo.Cancel)="n"))ORDER BY TblCustInfo.Zip;is there a way to return only records resulting in greater than zero from the ([SumofCharges]-nz([SumOfCredits])) AS RunBalance part ???
View 14 Replies View Relatedhey guys i have the following query but the greater than part isn't working it is still showing all records
SELECT AREATBL.AREA_DESC AS AREA,
IDEPTBL.IDEP_DESC AS DEPARTMENT,
ITEMTBL.ITEM_DESC AS ITEM,
ITMATBL.ITMA_PRICE_1 AS RRP,
IIf([ITMATBL]![ITMA_PRICE_1]<>0,(([ITMATBL]![ITMA_PRICE_1]-([SCSTTBL]![SCST_LAST_INV_FINAL_COST]/[ITEMTBL]![ITEM_CTN_QTY]))/[ITMATBL]![ITMA_PRICE_1])*100) AS GP,ITEMTBL.ITEM_NUMBER
FROM
((ITMATBL INNER JOIN (ITEMTBL
INNER JOIN IDEPTBL ON ITEMTBL.ITEM_IDEP = IDEPTBL.IDEP_NUMBER)
ON ITMATBL.ITMA_ITEM_NUMBER = ITEMTBL.ITEM_NUMBER)
INNER JOIN AREATBL ON ITMATBL.ITMA_AREA_NUMBER = AREATBL.AREA_NUMBER)
INNER JOIN SCSTTBL ON (ITEMTBL.ITEM_NUMBER = SCSTTBL.SCST_ITEM_NUMBER)
AND (AREATBL.AREA_SYSC_NUMBER = SCSTTBL.SCST_SYSC_NUMBER)
WHERE
(((AREATBL.AREA_NUMBER)=[Forms]![FRMGP]![Combo16])
AND ((ITMATBL.ITMA_PRICE_1) Is Not Null
And (ITMATBL.ITMA_PRICE_1)>0)
AND ((ITMATBL.ITMA_ACTIVE_YN)="Yes")
AND ((ITMATBL.ITMA_SEND_TO_TILL_YN)="Yes")
AND ((IIf([ITMATBL]![ITMA_PRICE_1]<>0,
(([ITMATBL]![ITMA_PRICE_1]-([SCSTTBL]![SCST_LAST_INV_FINAL_COST]/ [ITEMTBL]![ITEM_CTN_QTY]))/[ITMATBL]![ITMA_PRICE_1])*100))
>[Forms]![FRMGP]![txtTARGETGP]))
GROUP BY
AREATBL.AREA_DESC,
IDEPTBL.IDEP_DESC,
ITEMTBL.ITEM_DESC,
ITMATBL.ITMA_PRICE_1,
IIf([ITMATBL]![ITMA_PRICE_1]<>0,
(([ITMATBL]![ITMA_PRICE_1]-([SCSTTBL]![SCST_LAST_INV_FINAL_COST]/ [ITEMTBL]![ITEM_CTN_QTY]))/[ITMATBL]![ITMA_PRICE_1])*100), ITEMTBL.ITEM_NUMBER;
thanks
I have a function that I would like to max out at .2 and I don't know how to go about doing that.
Currently it is
Equip Inf: (([Equipment]*(0.03*(2006-[Letting Date]))+[Equipment])/([Equipment]))-1
and then I multiply (1+[Equip Inf])*[Equipment] to get my inflation. What I want is for Equip Inf to never be greater than .2 or 20%
What is the best way to do this?
I'm a new italian member.
I have a problem:
How can I show hours, in a field, greater 24 h (p.e. 35:15) ?
Thanks
Babaiaga
In my forename field I have entries with just the initial. I would like my query to filter out anything less than 2 characters. Does anyone know the criteria format?
View 3 Replies View RelatedCode:
---------
If (Me.Variance_From_Budget < 99999 And Me.Variance_From_Budget >= -99999)
---------
This code is working when variance is less then -100,000, but does not work when variance is greater then -100,000.
What I need to accomplish is an alert if a query finds any records. In other words, When the main page is loaded a query will run in the background. This query is looking for any records that need to amended within the next 30 days. What i would like to do is if the query finds any records that need amending an alert "form" or conditional formatting in a field with turn it red as an indicator that attention is needed.
My problem is how do i write a code to see if the record set from the query is greater than null???
any ideas would be greatly appreciated.
Ricky
I tried the Crystal Reports forum, but no responses. Can anyone see what I'm doing wrong with the below statement in Crystal Reports 8.5. I'm
trying to select reecords that exceed 7 days based on a Crystal Reports parameter. The parameter has the format of 2/27/2006 (Discrete value). The 'LastRouteDate' has a format of 'datetime'. Thanks ! ! !
{?Select_Date} - DateValue({Applications.LastRouteDate}) > 7
Hi everyone.
What I currently have is 2 columns in one table 1 is required and 1 isnt.
However what i need to do is work out the sum value of the greater amount from each line.
IE
A B
1 2
1 0
2 1
which would give a total of 5 as its adding B1+A2+A3
I tried creating a 3rd columb and entered this IF Formula but apparently access doesnt support If formulas
=If([claims]![Mileage1 IR]>[claims]![Mileage1],[claims]![Mileage1 IR],[claims]![Mileage1])
my plan was to get the "IF column to show me the higher amounts which could then be totalled up.
Any ideas where I may be going wrong?
Kev
Hi,
The query statement below is meant to extract the number of References with a type code of 'M' on the table tblHDRef, then present it under a column named 'Modified':
Modified: DCount("[tblHDRef].[HD Ref]","[tblHDRef]","[tblHDRef].[HD CodeType]='M'")
This works fine - but how can I add further criteria to this query statement to only count references with a HD Date greater than 03/02/2008?
I tried:
Modified: DCount("[tblHDRef].[HD Ref]","[tblHDRef]","[tblHDRef].[HD CodeType]='M' and [tblHDRef].[HD Date]>#03/02/2008#")
but this didn't seem to work at all. Am I missing something?
Cheers in advance.
I have a table.
BackupTbl
The table has 2 fields I want to compare.
Limit
Usage
I obviously want to know when they are over their limit. Tried using expressions but I either return all results or none.
I tried a bit of SQL
SELECT * From BackupTbl
WHERE Limit < Usage
This returns all fields.
Im trying to create a record set that compares a quantity value in the recordset to a Value in a temporary table that holds the most recent remaining quantity. I have the following code in the after update of a text box, but it does not trigger. I want it to run after a value is entered into the text box. Is the code wrong or the location im putting it incorrect?
Code:
Private Sub txtQty_AfterUpdate()
Dim rs As DAO.Recordset
DoCmd.OpenQuery "qryQuantitySoFar"
Set rs = Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
[Code] ....
I am using a database and need a field capable of storing times that will be greater than 24 hours.
I understand there is no "ready made" way of dealing with this in Access, so what's the best way?
The catch is I am using VBA code in Excel to populate the table, the value comes from a cell (in Excel formatted as "[h]:mm").
I would like to know how to make a checkbox indicade when there is something typed in a column.
Example:I have a table containing books with columns such as title, autor, etc. And I also have a column "notes" where I can make comments about the book.
But when I'm printing out the table in a form or report, I would just lite to indicate that there is something written about the book, and not write out the whole comment.
So how do I tell my checkbox (or other indicator sugestions) to indicate when lengt of column > 0?
Is there a way to place greater than and less than parameters within vba access? I have tried so many ways for hours today trying to meet my objective.
Objective1: When the user clicks vbno on a message box display.. I want the user to have a constraint in the text box between 0 and 100, and the value cannot be 0 nor 100, simply between.
Background: This same text box has different constraints, where when yes is clicked. The value is 100. The textbox also has a must be 0 constraint when nothing is typed.
Objective2: I would like, when the user clicks the next record button, to have an error message when the user's entry is not between 0 and 100 once vbno is clicked.
I was attempting to use the option of calling a subroutine, however, access would not give the user enough time to enter data before an error message is displayed.I even tried to see if I could call the vbno function during the click event for new record. I do not know how to do that.