General :: Return Based On Criteria - Records With Less Than 5 Characters Or Blank
Aug 28, 2013
I have a table with Zip CODE field. I uploaded my information to this table form an excel file. There were over 120K rows of data so no way i could do this in excel.
So I found that some of the ZIP codes are either blank or have less than 5 digits.
Can I build a a query with the ZIP code field that will return all zip code records with less than 5 characters or blank. I'd like to repair these zip codes and a update query will be my next step.
I repeat excel is not an option because when I try to sort by the Zip Code column my computer explodes (okay not literally) ....
View Replies
ADVERTISEMENT
Jul 18, 2013
I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..
See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.
How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...
View 3 Replies
View Related
Sep 5, 2013
I have a field in Access 2010 called Section Number which generally holds two characters. The characters can be numbers, letters, or a combination. Examples are:
01
X1
KA
40
Sometimes this field holds three characters, such as:
01a
02b
I want to write a query that returns only the Section Numbers that contain two characters. The instances where the field holds three characters are too numerous and changeable to use a parameter that says, for example: not "01a" and not "02b"
Is there a way to write a parameter that means "not Section Numbers of three characters" or "not Section Numbers of more than 2 characters"?
View 2 Replies
View Related
Mar 13, 2007
OK, I have been searching around on the fourm for over an hour now...I give up. If this is some where else, I am sorry.
I have a database of maintenance data. There are several columns that are usually filled in, some records have some columns blank. They are formated text because they hold letters and numbers (see pic).
I am using a form to query the table...no problem. The form has text boxes the user filter down the data
The problem comes are with the results of the query. Any record that has a blank column is not retuned. I am using "Like" so that the user can enter in partial codes. I know "like" won't return "null" records.... Help!
Things I have tried:
1)IIF(form field is blank, return table field, else use like command to filter) - returns nothing!
2)Like "*" & [Forms]![Fleetwide_data_Request]![MAL_CD] & "*" returns all records without blanks (i.e. missing data)
what else can I do?
Thanks
View 5 Replies
View Related
Jun 5, 2014
I have a report that I am trying to complete based on several queries. I am trying to count the number of records based on certain criteria and using the following DCount.
=DCount("[Calculated time]","IPG1","[Calculated Time] <= 0.04" And [Ship-to party] In ("SN00207PJZ","SN09162XXX","SN09324XXX"))
I want to count the number of IPG1 records that are under .04 and have the Ship-to party of the ones listed. I have tried everything that I can think of to get it to work but can's seem to get it to. I figure it's something easy but I don't see it.
View 4 Replies
View Related
Sep 15, 2005
Maybe it's the day's 'brain drain', but I need to set a criteria in a query whereby it will select answers in a field that are a specific number of characters in length.
i.e., answers that are 5 digits long (without knowing any of the digits)
Russ
View 2 Replies
View Related
Dec 6, 2013
I have two tables of data. One contains balances for a set of accounts (one unique balance per account, per date). The other contains transactions for a subset of the accounts (multiple records - or possibly none at all - per account, per date)
I'm trying to write a query to return the sum of the transactions from the Transaction table, for each unique account + date combination which is present in the Balance table.
Here is my SQL :
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Sum(tblTransactions.Amount) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;
This works fine with one major problem; if there are NO transactions for any given account + date in the Transaction table, I get no record for that combination in the dataset.
What I need is for the query to return a 0 in those situations (i.e. I should have as many records in my queried dataset as there are in my Balances table, but SumOfTransactions may be 0 for some of those balances)
I've tried the following but it has no effect on the outcome :
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Nz(Sum(tblTransactions.Amount),0) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Sum(Nz(tblTransactions.Amount,0)) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;
View 4 Replies
View Related
Dec 3, 2014
I have a form that is bound to a query that returns all records matching a set criteria (namely, the field ApprovalDate is null); the idea is that the user clicks on a command button which gives information, and if the user chooses to continue (vbYes), then the field ApprovalDate has its value set as the current date, and the record is saved.
Next, I have put in DoCmd.Requery to requery the form and effectively go to the next record wanting approval - this appears to work fine, however I need to add something to the code that handles a null return from the requery - preferably a message box and then have the form close.
View 5 Replies
View Related
Mar 14, 2015
I have a form with a field within it called ID number, another table contains the ID number with a person's name next to it. I was wondering how I can make it so that in the form, when the ID number is entered, the name of the person also shows next to it and does so automatically for each different record. I am pretty sure that a subform is needed however it doesn't seem to work for me so I must be doing it wrong. How would I do this?
View 1 Replies
View Related
May 15, 2013
All. Using access 2010. I have a query that returns 92 records. When I put in the criteria for one field to leave out records with “approved” which totals to 9 records, the query only returns 10 records. It is not returning the records that are blank(not null) for that field. I want those records. Why is this happening and how can I get the blanks for this query?
View 2 Replies
View Related
Jul 24, 2013
When I add a record to table A (using a form) the first field I enter is a date. That date will then determine which records I see in my drop down list from table B (via a query).
Some records in table B have EffectiveFrom and EffectiveTo dates recorded - because they are now obsolete, other records have no dates recorded in these fields because they active.
How can I view and then select from the drop down only those records in table B which are effective/valid to the date entered in table A?
View 1 Replies
View Related
Apr 18, 2013
I'm having an issue getting my query to omit records with a blank field - in fact, it omits all records.
What I'm trying to do is:
I have a list of customers, with phone and email addresses. I want to filter via query for only customers with their email address's entered.
Here is what I have:
IIf([Forms]![AdvancedReporting]![Check230]=-1,"*",Null)
View 14 Replies
View Related
Apr 24, 2013
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
View 7 Replies
View Related
Apr 19, 2015
I want to return the characters to the right of the space but I'm getting back rubbish.
This is the type of data in the field AccountName
Z-Yachting Barcos y Apartamentos S.L 031
Zusi Ltd ST5
Leaders BR3
And this is the result I want
031
ST5
BR3
What I'm getting is:
os S.L 031
ST5
ers BR3
This is my current formula.
Right([AccountName],InStr(1,[AccountName]," ")-1)
View 2 Replies
View Related
Oct 28, 2013
I am currently using
Code:
Private Sub Form_Open(Cancel As Integer)
Const cstrPassWord As String = "open"
Dim strPassWord As String
strPassWord = InputBox("Password:")
If Not strPassWord = cstrPassWord Then
Cancel = True
End If
End Sub
to password protect a form within my database. it is working great however i was wondering if there is any way to make the characters appear as **** when entering the password?
View 9 Replies
View Related
Mar 25, 2006
I am not sure if this possible, the methods I have looked up on the Internet take ages for the query to open but I'll give it a shot.
What I need to do is count (and increment) how many records are returned in a query for each person.
So if 1 person returns 4 unique records, in these records it shows 1,2,3,4 in each record.
The way i have tried is by using DLookup to check the next record and evaluate it using IIF but this takes about 25 seconds to return circa 50 records.
View 5 Replies
View Related
Sep 26, 2005
Dear all,
I would like to seek your help on this problem.
I would like to do a search for my records based on 2 different criterias. If found, the function should just return a boolean value so that I can act further based on the boolean value.
The reason I'm doing this is that I have 2 similar tables that contains EventID and AttendeeID and these 2 tables cannot have the same EventID and AttendeeID. So before I can allow a record to be inserted in the 1st table, it must check that there is no record in the 2nd table that has the same EventID and AttendeID. If there is, then i cannot allow the record to be inserted in the 1st table.
The same thing applies when I want to insert a record in the 2nd table. It must now check the first table.
Recordset.find seems to be only catering to 1 criteria, not 2.
Pls help. Codes are most welcome too.
View 3 Replies
View Related
Aug 16, 2005
I have a table that stores records by "return date". I would like to create a delete query that removes all records that fall outside of the date range entered on a specific form (frmMainMenu).
I can deleted the records that match the form criteria, however that is not what I need. The criteria used to delete the records was:
Between [Forms]![frmMainMenu]![from date] And [Forms]![frmMainMenu]![to date]
Let's say that the frmMainMenu contain the date range of 8/1/05 to 8/10/05, however the table contained data from 6/1/05-8/14/05, I would like for the query to delete all records that have a date 6/1/05-07/31/05 and 08/11/05-08/14/05.
Any assistance would be greatly appreciated. Thanks in advance for your help![/SIZE]
View 1 Replies
View Related
Sep 20, 2005
Hello all,
I'm having some difficulty obtaining the answers I want from my query. I'd have been more comfortable using filters in excel to find the answers, but my dataset is too large in this instance.
I devised a query which I thought should achieve the results, but based on a subset analysed in Excel the answers are significantly different.
My table has about thirty fields, the query I've produced is this:
SELECT AH_orphans.Product, AH_orphans.Comments, AH_orphans.ESP, AH_orphans.[ESP Parts],
<snip, all other fields in table>
FROM AH_orphans
WHERE (((AH_orphans.ESP)="n") AND ((AH_orphans.FRU)="y") AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a")) OR (((AH_orphans.ESP) Is Null) AND ((AH_orphans.FRU) Is Null) AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a"));
What I'm hoping for is the subset which match the following conditions:
- ESP field is not "Y". Possible values Y, N, null
- FRU field is not "N". Possible values Y, N, null
- Only those records from EntitlementName which include the substring 'uptime'
- Only those records from Account which start with the subtring nation
- Only those records from Type which include the substring primar
- Only those records from [Is a parent?] which equal #N/A.
Asking the same questions in Excel I get around 1.5k records. If I use the quoted query on the same dataset in Access I get precisely 11.
Thanks for reading this far! If anyone can spot the flaw/s in my query, or indeed advise that I'm going at it completely the wrong way I'd be very grateful of some advice.
Cheers,
Alex
View 4 Replies
View Related
Jul 10, 2007
hi,
i am querying a single table that contains card numbers and corresponding transaction codes (amongst other things). a single card number can have multiple transaction codes.
i want to select all card numbers that have both 0100 and 0802 transaction codes.
if i use a select query with ="0100" Or "0802"as the criteria, it returns all card numbers that have either 0100 or 0802 transaction types. if i then change the query to crosstab and group by card number, it is clear that some cards have both transaction types.
when i try to change the criteria to ="0100" And "0802" it returns nothing at all.
i'm sure the solution is something really simple - any idea what i'm doing wrong?
View 7 Replies
View Related
Nov 30, 2012
I am wanting to delete a record based on 2 criteria. ive had a look on the net and it looked so simple. I tried
PHP Code:
CurrentDb.Execute _ "DELETE FROM tblRecommendedProducts " & _ "WHERE ClientDetailsID = " & [Forms]![frmClientSale]![ClientDetailsID] And "WHERE ItemsID = " & [Forms]![frmClientSale-Retail]![ItemsID], dbFailOnError
View 14 Replies
View Related
Jun 3, 2013
I am trying to create a delete query that, for a given person, deletes records in Table B that do not have a corresponding record in Table A.
Here are the relevant tables:
tblStates holds StateID, StateName, and RegionID (RegionID is a FK to tblRegions).
tblPeopleStates is a junction table between tblPeople and tblStates.
It lists states assigned to people. It has 3 fields: PersonStateID, PersonID, StateID.
tblPeopleRegions is a junction table between tblPeople and tblRegions.
It lists regions assigned to people. It has 3 fields: PersonRegionID, PersonID, RegionID.
For a given PersonID, I need to delete records (i.e., states) in tblPeopleStates whose RegionID is *not* in tblPeopleRegions.
For example, pretend that tblStates shows that State IDs 1, 5, and 6 are all in Region ID (i.e., all have a RegionID = 10).
If Joe (PersonID = 200) has StateIDs 1, 5, and 6 in tblPeopleStates, but doesn't have a record for RegionID = 10 in tblPeopleRegions, I need to delete his three records in tblPeopleStates (i.e., the ones where StateID = 1, 5, and 6).
PersonID will be found on [Forms]![frmMain]![subform1].[Form]![subform2].Form]![PersonID]
View 8 Replies
View Related
Jun 16, 2014
This seem to be simple for single criteria but i am not able to do it multiple. I want to restrict user to customer list they r able to see in form combo box drop down list.
users are in employee TBL
employee group criteria is in tbl_Groups which contains value Admin,Read Only, manager etc
employee r grouped through tbl_AssignUsersToGroups.
customer r grouped in customerType TBL. i.e class1, class2 etc
created EmployeesClassTypeRestriction TBL to store restrictions for employees
Restrictions criteria:
1-employee can view only specific class which includes all customers in that class
2- employee can view only specif customers in a class only.
3-employee can view only specif customers in a ANY class.
4- No criteria No restrictions and can view all customers in all classes( For Admin )
Tried to used Tempvar criteria in customer combo box qry but it works for above point 1 only.
Attached accdb sample also.
View 5 Replies
View Related
Sep 21, 2015
I need developing a new delete query criteria to add onto existing delete duplicate queries for deleting duplicate values. I need the new delete duplicate query to take precedent over the other two so that those values retained in the first query are kept retained after going through the second and third delete queries. Attached is a document laying out the request.
View 1 Replies
View Related
Oct 21, 2006
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
Any help would gratefully be appreciated. Thanks
View 5 Replies
View Related
Sep 14, 2013
I have an AfterUpdate event where I want a list box to be populated based on three different criteria based on a table in my database
1. Complete = False AND
2. Supplier on form = supplier in table AND
3. Status in table = "SUPPLIER_RFQ FOLLOW-UP" OR "SUPPLIER_RE-RFQ TO OTHER SUPPLIER"
I am having trouble with the last OR statement criteria, i cannot get this to return values correctly. Here is my code:
Me.cboSupplier.RowSource = "SELECT DISTINCT [Consolidated_Master_Req_Pool].[RFQ Contact] " & _
"FROM Consolidated_Master_Req_Pool " & _
"WHERE consolidated_master_req_pool.Complete = FALSE AND [Consolidated_Master_Req_Pool].[RFQ Supplier] = '" & Nz(Me.cboStatusRFQ) & "' And [consolidated_master_req_pool.Status] = '" & "SUPPLIER_RFQ FOLLOW-UP" & "' OR [consolidated_master_req_pool.Status] = '" & "SUPPLIER_RE-RFQ OTHER SUPPLIER" & "'" & _
"ORDER BY [Consolidated_Master_Req_Pool].[RFQ Contact];"
View 2 Replies
View Related
