Show Hours Greater 24
Dec 7, 2006I'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
ADVERTISEMENT
Storing Time Greater Than 24 Hours
Nov 28, 2011I 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").
Reports :: Sum Time Greater Than 24 Hours And Keep The Format
Aug 1, 2013I have a field in my report [Idle]
This has a time in a HH:MM:SS format (so 01:38:23)
What I need to be able to do is sum this greater than 24 hours and keep the format of HH:MM:SS - ideally I need to do this in the form field that I'll be using to sum it.
Show Button In Form If Count Of Query Greater Than 0
Mar 5, 2013I have a list of companies that need to return paperwork, we are then scanning that paperwork and placing in a directory and listing that it was received in a table in Access. I want the button to view this scanned in item if the qry for that paper work has arrived and the count is >0.
I run a command on a single form that if a qry is >0 it shows the button and I added that to the GotFocus event in the form.
Me.CheckSuitability.Visible = (DCount("[ID]", "qry_Suitability_Papers_Needed") > 0)
But this is a Continuous Form and I would like the buttton to show next to the company if the count is >0.
General :: Show Count Of Text Boxes That Have Value Greater Than 0 At Footer Of Report
Oct 19, 2013I have a report and at the footer i want to show how many of the txt boxes i have are >0
i tried
=Count([txtbox]>0)
I know that this is quite simple but i just cant get it.
Queries :: Show Sum Of Total Hours
Nov 4, 2014I am trying to build a query to show the total sum of hours a staff member has done in a certain department and the amount of sleep ins they have done. Each staff member has a unique ID. In the query I can get all the information but it shows on two lines dependent on department. I want this to show on a single line. Each employee will never have more than two departments. The query is called "QryForForum".
View 2 Replies View RelatedQueries :: Only Show Specific 250 Hours
Jun 13, 2014I created this Vehicle Maintenance and Parts database. In the 'Vehicle Parts Maintenance' button in the frmMenu form, you are supposed to choose the vehicle on the left side then with the drop down on the top right pick what maintenance type you want. Right now when you pick 250 hours it shows all parts attached to the vehicles not only the parts that are supposed to be changed at that maintenance cycle.
The whole database is attached.
Queries :: Subtract 8 Hours Of Work From Result Hours
Nov 29, 2014I want to subtract 8 hours of work of the Result of employee work hours/
There is attachment...
Greater Than?
Oct 23, 2007Hi 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.
No Greater Than
Apr 6, 2008Hey, 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
Less Than Greater Than
May 26, 2006I 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
Greater Than 1 Record, Less Than Another
Aug 30, 2005I 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.
Greater Of 2 Dates ?
Jan 10, 2006Hi,
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 !
'greater Than' Problem
Sep 8, 2006hi,
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!
Greater Than Query
Feb 16, 2007Hello 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
Greater Than ... Less Than .... 2 Fields
May 27, 2007I 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.
Query Greater Than Zero
Feb 17, 2008usingSELECT 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 RelatedGreater Than Question
Mar 19, 2008hey 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
Sum IIF Field Greater Than 0
Dec 8, 2006I 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.
Creating A Function That Cannot Be Greater Than .2
Dec 5, 2006I 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?
Greater Than One Character In Query
Mar 13, 2007In 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 RelatedModules & VBA :: Less Than Or Greater Value Is Not Working
Feb 18, 2014Code:
---------
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.
Query Record Set Greater Than Null???
Sep 7, 2005What 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
Selecting Records Greater Than Seven Days Old
Feb 28, 2006I 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
Query Problem - Greater Value Required
Jan 11, 2008Hi 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
DCount With A Date Range Greater Than X
Mar 3, 2008Hi,
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.