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 Replies
ADVERTISEMENT
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
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 RelatedQuery 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
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
Queries :: 2 Fields Greater Than Less Than Query
Feb 10, 2014I 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.
Query To Return Record Set Where 1 Column Is Greater Than Another
Jun 29, 2005Creating a query in Design View, how does one create a query in the "Condition" field that tells the Access to return the record set where the contents of one column is greater than the other?
Table Name=SURVEY_RAW_DATA
Column Name=TOTAL_EXP
Column Name=TOTAL_NX
There are obviously many more columns than those listed above in the table, but these are the two I want to compare. Also the two columns contain numbers.
In SQL I would write it like this:
Select * FROM SURVEY_RAW_DATA
WHERE TOTAL_EXP > TOTAL NX;
Thanks in advance,
Ken
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.
Queries :: Criteria In Query Is Greater Than Or Equal To 15% But It Gave Values Less Than 15%
Nov 24, 2013same as the title goes, why i can't query those figures greater than 15% only? How am i suppose to query them right?.Here is my database you cant check query1 and query 2. I am using Ms Access 2010.
Link:
www dot dropbox dot com/s/n3iuev1c5e0dfby/StockMonitoring.accdb
xD: StockMonitoring.zip
Escaping The # Character In Query
Dec 21, 2005Hello.
I'm using a filter that contain # as one of the characters that the field might contain.
I get some very inetersting results.
using
"select fld1
from tbl1
where fld1 not like 'D#%'
"
Lets' say that the values for fld1(text type) are :
Dodo
D# cc
The query above will return the 2 records, it is "egnoring" the filter.
How can I solve this problem?
Thanks a lot
Roy
General :: How To Get Last Character Of The String In Query
Nov 5, 2012How to get the last character of the string in query?
I have a table name PlateNo and I want to get the last character for registration purposes.
Ex. ZMD-123 - I want to get the number three(3) and if its possible every time the user types the last digit in criteria it will show all plate number ending in the entered number.
Adding Wildcard Character To Query Criteria
Aug 11, 2005I've got a query that does exactly what this (http://www.access-programmers.co.uk/forums/showthread.php?t=89564&highlight=null+records) chap got his to do.
However I want to add a "*" character to the criteria to allow for searches with partial matches. Here's the criteria that works:
[title]=[Forms].[Search]![Title] Or [Forms].[Search]![Title] Is Null
However when I try the logical extension:
[title]=[Forms].[Search]![Title] & "*" Or [Forms].[Search]![Title] Is Null
this doesn't work, and nor does:
[title]=([Forms].[Search]![Title] & "*") Or [Forms].[Search]![Title] Is Null
WHAT DO I DO? I'M TIRED AND I CANT THINK STRAIGHT! :eek: thanks and sorry for being so dumb!
Using A Question Mark As A Character Not A Wildcard In SQL Query
Jan 25, 2005Hi,
In SQL in MS Access a '?' '*' '%' are all used as a wildcards.
I need to be able to retrieve all messsages which contain a '?' within the message.
i.e. If a message contains the following characters : why?
I want to retrieve the message as a '?' is part of the message.
When I write the code :
SELECT User.User, Message.Message
FROM Message INNER JOIN [User] ON Message.[Message ID]=User.[Message ID]
WHERE Message Like '*?*';
Access takes the '?' as a wildcard and retrieves all the messages in the database. Is it possible to get round this and use '?' as a specific character.. not a wildcard?
Please help
Query Data Beginning With Alpha Character Only
Dec 1, 2006I am wanting to filter data in a table that only begins with an alpha character.
Ex. 233333
23444
03226
AAV233
Thus my result should only show the last character above "AAV233"
Please help. Thanks in advance.
Replacing A Character Depending On What Surrounds It (SQL Query)
Sep 19, 2014The following is a simplified version of a puzzle I'm faced with, but if I can solve the simplified version I'll be able to solve the real thing.
Take the following column:
MyField
1-2A-B
YZ-A6
53-75-XC
How within a query can I replace the hyphens with spaces but only where they appear between two numbers? So this is the result I'm after:
MyField
1 2A-B
YZ-A6
53 75-XC
(note that the letters and numbers could be any values in any positions.)
Exporting Query Data To Excel And Hyphen Character
Jul 12, 2005Hello all,
I have always been wondering why this happens, and now I have a problem with it.
When I export a Table/Query from Access to Excel (be it with Right CLick -> Export...or TransferSpreadsheet) there is an apostrophe character (') appended to the front of some/each cell in Excel. You cannot see it immediately, but when you click on the cell, there is this character. WHY? Does anyone else have experience in this? :confused:
Thanks in advance.
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 ... 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.
Greater 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?