Criteria In Query Leaving Blank Table
Apr 30, 2007
I am trying set up some criteria for a create table query. I am using multiple fields from different tables, I am trying to delete certain feilds if they meet the criteria. I am using iif([letter c_last]=[letter a_last],null,[letter c_last]) in the criteria for letter c last, but when I run it a box appears to enter criteria for the last name field? I want it to look at the field not for data to be entered manually. I hope this makes sense. Any help would be greatly appreciated.
View Replies
ADVERTISEMENT
Aug 9, 2007
Hi
Bit of an Access beginner and am trying to sort something out for work - not sure why they've asked me!
I've created a query to search on a couple of items using drop down boxes on a search form I created. This bit of it works fine, I used this site http://www.fontstuff.com/access/acctut08.htm and copied what he had done. This is fine.
I now want to add a date search to the same query. I know I can use Between [..] AND [..] but if I leave the boxes blank it finds no records. I'd like it to search and include all.
Ideally I want to include 2 extra text boxes on my form that I can put a to and from date in (or not put a date in and it find everything).
Hope that makes sense, please can someone do me an idiots guide?
Many thanks
Phil
View 5 Replies
View Related
Sep 6, 2006
I have a form that allows users to search the database for records based on there criteria. The form allows the user to search, through combo boxes, by variables. The first is MAT, the second is Relocation Area. The search works fine when the user selects options form the combo boxes. However sometimes it is neccesary to leave the location field blank and only search by MAT. When the location combo box is left blank no records are found. Is there anyway around this. The SQL code for the query is below:
SELECT [Extract Data].[ID], [Extract Data].AREA, [Extract Data].[MAT], [Extract Data].[Relocation Area 1], [Extract Data].[Relocation Area 2], [Extract Data].[Relocation Area 3], [Extract Data].[Grade Score]
FROM [Extract Data]
WHERE ((([Extract Data].[MAT])>forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 1])=Forms![Data Search]!SearchArea)) Or ((([Extract Data].[MAT])>Forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 2])=Forms![Data Search]!SearchArea)) Or ((([Extract Data].[MAT])>>Forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 3])=Forms![Data Search]!SearchArea));
Any help would be much appreciated
Nick
View 3 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
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
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
Feb 4, 2014
I've set up a form with a button to open a report based on the current name on the form. The idea is that as you look through the different pages on the form you can open a report for whatever one you're on and print it.
In my query I have a name field where I put the criteria: Forms!Formname.Textboxname
By clicking the button on my form I'm able to generate a report based on the name that appears in that textbox. It works great when I initially put it in but if I close the form (or query, or report) and open it back up it is blank.
Is this even possible with a text box? It seems like it when I open it, it has nothing to go off and that's why it's blank. I just don't know how to fix that.
View 7 Replies
View Related
Aug 7, 2013
I use this on most queries where I need to return all results if the form field is left blank. Works like a charm every time...
Like [Forms]![frm_main_menu]![Week] & "*" Or Is Null
except for this time..I need to filter by week number (52 weeks in a year)...problem is if I enter week "1" I also get weeks "10, 11, 12, 13, 14, 15, 16, 17, 18 and 19".
View 4 Replies
View Related
Oct 8, 2007
Hello.
I have a query that is pulling data based on a date in a table. For some records, that date field is empty. For those records, the data should be pulled based on the date of 1/1/06. I tried doing this
=IIf([Signed SLA Received] Is Null,1/1/06,>=[Signed SLA Received])
It doesn't like this at all, it brings back no data. I also tried putting 1/1/06 in quotes, but it didn't make any difference.
This is probably a totally incorrect way to get this done, but I don't know how else to try it.
Thanks!
Stacey
View 3 Replies
View Related
Aug 28, 2013
I'd like to know how to add a blank filed (memo), called "comments" that is not pulled in from any existing table, in an existing maketable query which is getting it's fields from different linked tables , so that the destination table that comes out of this query, has all those data along with an extra column for me to add comments.
View 5 Replies
View Related
Jun 13, 2014
I am creating a table, using an INTO statement.
I require a blank numeric field, which users will be updating via a form.
How do I make the field numeric, as my current script makes it a text field
'' as Ticket_No
And you can't cast/convert text to numeric ....
View 2 Replies
View Related
Mar 14, 2014
I am trying to achieve the following - I want to query a table to see if a record exists with a particular field blank. If so, I would like to prompt the user for data.
In real world terms, when assigning an item to a user I would like to first make sure that the item is not already assigned to somebody else. I have 4 fields, UserName, Item, IssueFrom, IssueTo. So when an item is assigned to a user, the first 3 fields are populated and the IssueTo remains blank, until that item is assigned to somebody else.
At the minute I have nothing in place to prevent a user from assigning the same item to multiple users and having multiple records for the same item in the table.
View 7 Replies
View Related
Jun 13, 2014
I am creating a table, using an INTO statement.
I require a blank numeric field, which users will be updating via a form.
How do I make the field numeric, as my current script makes it a text field
'' as Ticket_No
And you can't cast/convert text to numeric ...
View 1 Replies
View Related
Dec 16, 2014
In a database am building, I want to run a query with the criteria dependant on which field the user populates in a form.
The form has a number of fields that the user can select from including our reference number, the client's reference number and the site address.
I would like the user to be able to select the site address using a wildcard so that they can enter a part of the address such as "This Street" instead of "45 This Street" and the user be presented with all of the records matching "This Street".
I tried using the criteria:
Code:
Like "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*"
Which works perfectly as long as this field is populated. If this field is not populated, entering details in any other field bring up every record in the database.
Code:
IIf(IsNull([Forms]![SearchJobs]![SearchAddressLine1]),Null,[Forms]![SearchJobs]![SearchAddressLine1] & "*")
The full sql of my query is:
Code:
SELECT Jobs.JobsSalesEnquiryRecordNumber, Jobs.JobsJobStatus, Jobs.JobsEnquiryDate, Jobs.JobsTakenBy, ClientDetails.ClientDetailsURN, ClientDetails.ClientDetailsName, ClientDetails.ClientDetailsAddressLine1, ClientDetails.ClientDetailsAddressLine2, ClientDetails.ClientDetailsAddressLine3, ClientDetails.ClientClientPostCode, Jobs.JobsClientJobNumber, Jobs.JobsAlternativeClientJobNumber, Jobs.JobsClientContact,
[Code] ....
View 12 Replies
View Related
Aug 15, 2013
I have a single table with customer information, one of the fields is a date field "LastContacted".
I'm creating a search form with 2 date fields (txtDate1 & txtDate2) to search a date range of the LastContacted field, and I need to write this into the query that the search form uses.
I have written this using Nz so that it can still return results if the search boxes are left blank:
Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)
This seems to work and it returns lines from the table where there is a date entered. However some of the fields in the table have no entry in the LastContacted field. How to code this query so that it also returns lines where the LastContacted field is blank in the table?
I have tried:
like "*" & (Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)) & "*"
but this returns errors when I try to run it.
I'm using Access 2010.
View 14 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
Nov 18, 2013
I have a list of employees and sort criteria. for example
empID....Criteria
1234......T
1234......F
1234......T
1234......F
1235......F
1236......T
1236......F
1236......F
1236......F
1236......T
1236......T
1237......F
The output I am looking for is a count of the number of times T appears by an employee, BUT is there is no record it would return 0
E.G.
empID......Count
1234...........2
1235...........0
1236...........3
1237...........0
I can get it to return:
empID......Count
1234...........2
1236...........3
using Count and the criteria Where Criteria="T" but not returning zeros.
View 2 Replies
View Related
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 3 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
Nov 29, 2005
I do a query to remove "." from a list of my servers. This works great, but for every name on the list without a "." I get an error in the output. Is there any way to remove these errors from the output, so I only see the data that has been properly pruned down?
here is the query i'm running
sort: Left([AgentName],InStr([AgentName],".")-1)
View 2 Replies
View Related
Aug 22, 2006
Im really stumped.. and I really need some help, Here is my problem,
I have a field with a whole bunch of regular peoples names in it (user_names). Home Developer names are also in this field, it has to be this way to show if the home is still owned by the developer or moved into. What im trying to do is build a query that takes out all of the developer names (by takes out i mean does not change the table at all, just temp so i can perform some calculations on the data).. Im having a problem with this. I have a developer_name table that looks like this
Autonumber Developer
1 Walts
2 Peets
3 Donalds
When i set up my query under field i put user_names and under criteria i have used all variations i can think of pertaining the following code
<>[DeveloperInfo]![DevName]
from what I understand it should return all values that are not developer homes, it doesnt work though, it leaves some in there. if i change the code from <> to = it takes out all the user names and leaves just the Developer names as it should, unfortunatly i need it to take out the developer names not leave them in.... Any ideas?
Thanks
Greeny
View 9 Replies
View Related
Feb 21, 2006
This is, I think a very easy question: What would be the apropriate form event to use when leaving a record on a single form (When you press next record for example)
View 3 Replies
View Related
Nov 5, 2007
Hi
I am trying to set up a number of queries to return completed jobs with a specified time frame, usually monthly. So rather than have to type the same dates in arround 20 times I tried to use the data from a table in the Criteria. This did not work any suggestion would be much appreciated.
Attached is a screen shot showing a fragment of the query and the dilog box I get when it runs.
Thanks Marcus
View 3 Replies
View Related
Dec 6, 2011
if there's a way to apply a list of items as criteria in the order listed? Say I've got a list of sales reps and I want a macro to run a query repeatedly, populating one rep from the list at a time in a field called Sales Rep.
View 8 Replies
View Related
Sep 5, 2007
I made a Table with one record per person which is entered initially and contains a sub-table which is a list of the drugs they are taking. As people are notoriously bad at spelling drugs they select them from an alphabetacised drop down list held in another table. The idea being that they check the spelling before entering them on the main table for any new drugs. The subjects each come back and have a number of measurements taken on a number of visits.
So the main table and the drugs table have a one to many relationship and the main table and the visits. In both cases the tables are linked by the same unique identifier which is compulsory and unique in the master table. The data is input using a master form and sub-forms.
I havent done any of the input but when I looked at the two sub-tables they each have a number of records which are blank in the identifier feild.
How has this happened? How do I stop it happening and is there any way of finding out which record these visit data and drugs should belong to?
View 5 Replies
View Related
Feb 27, 2006
Hey guy im kinda new to this program so excuse me if my question is simplistic:I built a form for making Purchase orders. In the main form...a user enters a bunch of text/num info (name, address, ect)into the table. After that i have it tab to a subform..where a user can add items by line (as many as they like). All good till here: Once i go to click out of the subform...i get message "You cannot add or change a record b/c a related record is required in table 'UCT_Req'"My relationship has a 'one-to-many' link (the req number to the many line items). I have auto-numbers counting up in each table.My main problem seems to be attaching the entered line items to a specific record in the main Purchase order form...and then opening a fresh form afterwards. Any hints?Thanks folksSTeve
View 2 Replies
View Related
