Hi,
this is a quickie with regards to query. When I execute a query I get my result back along with a blank record.
How is this prevented ?
my user has a mouse wheel which takes them to the blank record if they move it.
Thanx in advanz.
Niall.
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
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...
:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)
I have a query with three sub queries, all returning a number from different tables. But when any one of the sub queries returns a blank, the entire main query is blank.
How do I stop this from happening? I have tried NZ() on the sub queries and on the main query, but the blank still happens.
Hello, I have a table (Projects) with original projects and amended projects. All information regarding each project is inputted in the fields for original projects. Only limited fields are inputted in amended projects;new loan amounts or a new closing date may arise infrequently for amended projects. When there is a new closing date for an amended project, and it becomes part of a query all other fields are blank for that record, How do I populate the original project data into the blank fields of an amended project. I frequently have to create queries based on the fiscal year of closingdate field and I need to know the commitmentdate (field) and other dates that are in the original projects records. I hope this is not confusing thanks in advance. :confused:
View 1 Replies View Relatedhi all
i have created a database but didnt insert any default value at the begining and now i have blank cells in the table rather than £0.00
how would i go about querying the blank cells and then populating them with 0.00
thanks in advance
chris
I am running a query that is used to show a count of certain values. For example: The number of different Allegations.
The values would be grouped and counted:
Damage Claim 3
DUI 2
and so on. This works as it should in the query, but I noticed that the query has a blank in the first row.
Is this due to some records not having any values in the Allegation field?
This is what is looks like:
(Blank) 0
Damage Claim 3
DUI 2
I have searched for an answer and spent some hours but still not getting it.
I have played about with the query joins and still nothing?
I have never had this problem with my database in 5 years (well not that i have noticed).
Some records are fine others are triplicated and quite a lot are blank?
This is the SQL for the query.
SELECT Employee.EmployeeID, Employee.FirstName, Employee.Surname, Employee.Address, Employee.[Town/City], Employee.Region, Employee.DateofBirth, Employee.Nationality, Employee.MaritalStatus, Employee.PassportNo, Employee.ExpiryDate, Employee.Nextofkin, Employee.Relationship, Employee.NOKAddress, Employee.ContactNo, Certificate.CertName, EmployeeCert.Level, Trade.TradeName
FROM Certificate INNER JOIN (Trade INNER JOIN ((Employee INNER JOIN EmployeeCert ON Employee.EmployeeID = EmployeeCert.EmployeeID) INNER JOIN EmpTrade ON Employee.EmployeeID = EmpTrade.EmployeeID) ON Trade.TradeID = EmpTrade.TradeID) ON Certificate.CertID = EmployeeCert.CertID
WHERE (((Employee.EmployeeID)=[Forms]![Employee]![EmployeeID]) AND ((Certificate.CertName)="IRATA"));
Basically i have to solve this problem.
I have a report that is based on this query and if the employee is not displayed correctly instead of the employees Name and Surname i am getting "#Error" ?
Many thanks in advance
I have a query that gets data from a table now not all fields are filled in, in this table, and the query only shows data that has all fields filled and i need it to show them all
View 3 Replies View RelatedI have the following calculated field in a query:
LoanNo:IIf([Stats].[LoanCnt]>1,[Stats].[LoanCnt] & " Loans",[Loan].[LnNo])
Within its query, it correctly displays the loan number associated with a particular loan.
However, when I run a second query
SELECT LoanNo
FROM qryLoanInfo
it comes up blank for every record (other fields are OK)
Does anyone have any idea what might be happening?
Hey
I have a form where the user can choose from a drop down menu, values like Name= James.90
Date=1/10/07.
Then it would return the record with James.90 And That date in it
But my problem is you have to choose a value for both boxes. Is there a way to ignore it if the field is left blank.
My current code is
SELECT timesheet.WeekEnding, timesheet.FName, timesheet.Company, timesheet.Project, timesheet.CTR, timesheet.Comments, timesheet.Total, timesheet.Project_Title
FROM timesheet
WHERE (((timesheet.WeekEnding)=[Forms]![FormBeta]![Week]) AND ((timesheet.Company)=[Forms]![FormBeta]![Company]) AND ((timesheet.Project)=[Forms]![FormBeta]![Project]));
I have two query's. One that shows a list of buildings with post inspection dates "Q_PI_Qrtly_Insp_Ct_Range_Count" and one that shows a list of buildings that all have posts "Q_PI_Qrtly_Insp_Ct_Range_Blank". What I am trying to do is create a union of sorts that will give me a list of all the buildings with the count of the post inspections performed and if there were no post inspections performed then list those buildings with a zero or blank.
Even if I have to have two seperate lists that would be OK but I can not figure out how to get a list of buildings where no post inspections were performed. I've attached a sample of the database. In other words my "Q_PI_Qrtly_Insp_Ct_Range_Blank" query doesn't work.
I have some expressions built to calculate totals in a query. Here is the first
Engineer Total: [Engineer Hrs]*[Engineer rate].
This works when a user adds hrs to a table and the rate is a default number.Then a total from 4 totals similar to the Engineer total are added together here:
Total Rate: [Designer Total]+[Quality Total]+[Coordinator Total]+[Engineer Total].
Works perfect if all 4 Hrs fields have data. The problem is not every entry will have hrs in each field..when this happens the Total rate does not calculate. So long story short, how do I make the Total rate bypass the blanks or do I need to make the Engineer total force a zero on blanks?
Hi guys,
I have been running the same database on numerous systems for 5 years and the user clicks on a button and using this code;
DoCmd.OpenForm "Employee", acNormal
Simple !, this always opened this form blank, without applying filters, ready to accept a new employees details.
But now this no longer works, for no apparent reason?
What happens is that it opens with the form fields filled with the first person in the Databases details instead of being blank.
The only thing that could have changed is that i converted the database to 2003?
Any ideas?
Thank you in advance
I have a simple dbase that lists individual staff members' qualifications, skills and hobbies.
One person may have 20 qualifications and another may have 1 or none.
I have a separate field for each qualification.
Is it possible to write a query that only returns fields that are not null in each record so that the subsequent report is not full of blanks? I know I can use Can grow - Can shrink on the report but I'd rather eliminate blanks via a query if I can.
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 1 Replies View RelatedI'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.
I have two tables: Table #1) [Request Table] Customers request our company to stock a certain Part Number with a minimum value. Ajax - P/N "NP4i" - Min "30".
Table #2) [Sales History] All sales purchased and the "Qty-Total" purchased. For simplicity Fields are limited.I created a CrossTab Query that displays from [Request Table] P/N's requested/ min values (Row Headings) then showing P/N's purchased [Sales History] Value="Qty-Total" Sum and
Column=Format([Invoice Date],"mmm/yyyy") group by, showing last twelve months.
Ex: Customer name, Part Number, Minimum, May/2013, Apr/2013, Mar/2013 and so on.
AJAX, NP4i, 30 4 21 5 and so on.
The Crosstab query list only requested Part Numbers that have at least One sale wihin the last twelve months.If there are NO sales, the crosstab query will NOT show this Part Number.My boss wants to see the requested Part Numbers that were never purchased along with the ones purchased.He wants to address the NO sales, yet they requested we stock that particular Part Number for them.
Is there any criteria I can use to force the Part Number field on the [Request Table] side to populate even though there were NO sales within the last twelve months?
I've set up a simple query that returns 6 columns of data.This query then shows on a sub-form elsewhere in my DB.The problem is that the query always appears with a horizontal slider. Allowing the user to scroll across to see the other columns in the query...The problem here is that there ARE no other columns of data. They are just empty. I want to restrict the output of this query to ONLY show the 6 columns that i have specified.
I have tried deleting the columns in Query Designer, then save the Query. But every time i re-open it half a dozen or so blank columns are stuck on the end.
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".
I have a button that opens a form based on a query, the query is filtered based on an input field. This works great except when the user typos what they put into the input field. Since the query pulls no records it just opens a blank form. I want it to not open the form and instead open a message box telling the user the data they entered is not valid and to try again.
View 1 Replies View RelatedWhen entering information into a blank form, I would like to be able to continue entering information to another additional blank form after my last entry. Is there a way to continue to a blank form after entering information into the previous blank form? I would just like to continue without having to close the entire form and then reopening another form.
View 7 Replies View RelatedHello.
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
Hi all,
I am having a problem getting my query to work properly. I have read through this query section but it just got me more confused. I know some have used IIF function but it didn't work for me.
here's my code:
SELECT tblEmployee.UserName, tblODF.ODFNumber, tblQueue.Queue, tblStatus.Status, tblODF.ODFScanDate
FROM tblStatus INNER JOIN (tblQueue INNER JOIN (tblEmployee INNER JOIN tblODF ON tblEmployee.EmployeeID = tblODF.EmployeeID) ON tblQueue.QueueID = tblODF.QueueID) ON tblStatus.StatusID = tblODF.StatusID
ORDER BY tblEmployee.UserName, tblStatus.Status, tblODF.ODFScanDate;
I want everything to show even if one of the fields is blank.
Thank you
Hi!
I've written a querry - and the results that come back dont look right. Some rows have data in and others dont.
Has anyone seen something similar?
Does this mean that the data are probably incorrect?
Any advise?
A.