Queries :: Multi-field Query Not Working When Some Fields Are Null Value
May 22, 2013
Access 2010. I've had to learn it at work because our DBA was let go and I was the only one willing to give it a go. Read a book or two and picked up some stuff on the internet.
Here's my problem:
I have a simple table -Employee with 4 fields. FirstName, LastName, Office and JobTitle. I have form called Form1 that has 3 control fields cboJobTitle (a combo box that is populated by a query that finds all the unique values of that field in the Employee table), cboOffice (same as above) and txtName (a text box to allow user input) that are used as the criteria for a multi-field query triggered by a button at the bottom of the form. The idea being that you could do a search using this form to find all the employees in one office or all the accountants in one office, or any other combination.
The main search query has the following criteria for each field -
Like [forms]![Form1]![cboOffice] & "*"
Like [forms]![Form1]![cboJobTitle] & "*"
Like [forms]![Form1]![txtName] & "*"
It works great...until I enter a record where one of those fields may be null, such as if I leave the JobTitle blank.
If I have two employees in an office in one city and then do a search for all the employees in that office, it only returns one record and ignores the one that has the null value in the JobTitle field.
View Replies
ADVERTISEMENT
Feb 7, 2015
I have a question about errors on null value.I have made a small database for tryout, it has to be implemented in another one.And the small database is working.I have one table where there is one field called BatchInput.I scan a barcode into it and let two query's breaking it apart. I scan this batch into the table field
BatchInput: 20 MAY 2004H149-082-79 A4147011A05
Then I have my first query (Qrybreak1) extracting the date and deleting H14
Date: Left([BatchInput];11)
PartCertNr: Right([BatchInput];Len([BatchInput])-14)
The result is this:
Field date: 20 MAY 2004
Field PartCertNr: 9-082-79 A4147011A05
The second query (Qrybreak2) I look for the first space:
space: InStr([PartCertNr];" ")
Then with the result I cut it into two pieces
PartNumber: Trim(Left([PartCertNr];[space]-1))
CertNumber: Trim(Right([PartCertNr];[space]+2))
[code]...
And query (QryResult) even wont start, giving a popup with Invalid procedure call..How could I handle Null on the part where there is no space after the partnumber (missing Certnumber)?
View 7 Replies
View Related
Oct 2, 2014
My training database requires me to identify each training record in the tblEmpTrainHist table as either "Compliant" or "Delinquent". I thought a simple calculation in my query:
RecordStatus: IIF([DateReceived]<=[DateDue],"Compliant","Delinquent")
would do the trick. However, I did not consider the records where the employee has not yet completed the training and the field [DateReceived] is Null. There are two considerations: those employees who have no [DateReceived], but have not yet reached the DateDue (Compliant); and those employees who have exceeded the DateDue (Delinquent).
View 5 Replies
View Related
Jun 15, 2014
I have a multi-select listbox (called PotentialItems) which behaves perfectly unless one of the fields has a null value in it, in which case I get an error 13, "Type mismatch" in the following code. It is valid business-wise for column 9 to be null.
Code:
Dim varItem As Variant
Dim dblTargetPrice As Double
With Me.PotentialItems
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
dblTargetPrice = Nz(.Column(9, varItem), 0) '<----- Error here
End If
Next
End With
View 5 Replies
View Related
May 16, 2013
On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.
View 12 Replies
View Related
Oct 30, 2013
Database query. I need the query to count the records of a field and display a number for the records of the field. For instance, one field is [Genre] and the other is [Show]. The query needs to list the Genres along with the number of shows for each genre. I've been able to just use the query design and add the genre field and I can add the show field, use totals count which gives me the genre counts the number of shows. My problem is the null. Some genres don't have a show listed so the genre doesn't even show up in the result. If I could get the the genres that have null shows to result a 0 it would be perfect.
View 5 Replies
View Related
Apr 5, 2013
I have two tables containing (let's say for simplicity) questions and attachments (pictures). I am trying to perform a union query to join all the questions and pictures into one report, but it won't let me union the attachment because 'the multi-valued field 'TableA.Pictures' cannot be used in a union query'.
I have searched and searched for a solution (and got kind of close) but i still can't get it to work. The best I can do is union everything like below, which gives all the questions as desired, but says #Error in the pictures column:
SELECT TableA.*
FROM TableA
Union
SELECT TableB.*
FROM TableB;
(Note tables A and B have the same structure, several yes/no and open text questions as well as one attachment field. )
View 8 Replies
View Related
Jun 27, 2014
Why update query isn't working when trying to update an multivalued field. In this table, there are 647 records and only 9 of which already have a value set in the "AssociatedProject" field. More than one AssociatedProject is associated with a vendor, so this field allows for multiple entries. I've looked at all the information on update queries and updating a multivalued field, but it will only update the existing 9 records. It does not recognize the remaining ones (they have no values yet).
Here's the SQL:
UPDATE Tbl_Vendor SET Tbl_Vendor.AssociatedProject.[Value] = 3
WHERE (((Tbl_Vendor.AssociatedProject.Value) Is Null));
View 9 Replies
View Related
Apr 23, 2014
I got a table salaries master I want to extract some info out of and calculate some values. The formula below doesn't work, I'm pretty sure it has to do with [pay period] being a text field. Is there no way to make it work?
'Gross monthly Salary: IIf([Salaries Master]![Pay Period] = "weekly";[Salaries Master]![Daily Wage]*5*52/12,IIf([Salaries Master]![Pay Period] = "monthly";[Salaries Master]![Daily Wage]*20))'
View 10 Replies
View Related
Dec 15, 2004
I have a database that I maintain the history of our football league in. What I am trying to do is count each team and the number of wins in each year
The query works fine except in the cases where the team had ZERO wins. Then of course it returns nothing and screws up the other queries when there is a hole for a year.
2 Tables in the database
INFORMATION
TEAM
YEAR
(other fields not relevant)
SCORES
TEAM
YEAR
RESULT (either WIN, LOSS or TIE)
(other fields not relevant)
I want to count the number of wins and return it to this query EVEN IF THE NUMBER OF WINS IS ZERO (0).
When I run the query I receive
The query returns this:
2000 Westside 6
2001 Westside 3
2003 Westside 1
2004 Westside 3
In 2002 Westside was 0 wins -11 losses and the query not finding any WINS in 2002 did not return a record.
How do you get the query to show
2002 Westside 0
==========================
Current SQL Query
SELECT Information.Year, Information.Team, Count(Scores.Result) AS CountOfResult, Scores.Result
FROM Scores INNER JOIN Information ON (Scores.Year = Information.Year) AND (Scores.Team = Information.Team)
GROUP BY Information.Year, Information.Team, Scores.Result
HAVING (((Information.Year)>2000));
View 6 Replies
View Related
Aug 22, 2014
I am attempting to filter records using a multi-select listbox, but all records are being returned. Here is my code.
Private Sub btnKeyboxCount_Click()
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] ....
View 1 Replies
View Related
Dec 23, 2013
Why is this update query not working, I'm trying to update a date field stored in a table.
The new date is passed from a txtbox on a form to the update query!!!
SQL code
UPDATE TblDietPlantemp SET TblDietPlantemp.MealDate = [Forms]![FrmSwitchBoard]![txtCusDate];
View 3 Replies
View Related
Sep 24, 2014
I have an append query created to add files to a table from an imported excel file. The table to which I am appending (SubTBL) has a field names Observation. This field is not required. There is a relationship to another table (ObservationTBL) which has 3 records. The information I am trying to append includes 7000 records, about 4000 have observations assigned and they match the information in the ObservationTBL. The remaining 3000 have blank records for observation. When I run the append query, only those with observations are appended. The rest are not added because of "key violations". I removed the relationship between the ObservationTBL and the SubTBL and the append query runs. Then when I try to re-do the relationship it fails "Violates referential integrity rules".To be clear:
1. The records without an observation are blank. (There are other fields in this append that have blank fields also and they are not causing any problems)
2. The field observation is NOT set to required.
Is it not allowing Null records?
View 12 Replies
View Related
Jun 23, 2014
i have a form with a control for "repair type" and a subform that has a query that searches a table holding vehicle repair records. on the form the user can enter up to 5 repair types, [repair1], [repair2], etc.. Then the control on the form holds each type of repair to search by. Since the user could use one or all five repair fields and put repair types likes brakes in field one on one record and field 5 on another record, how do i search to just pull out those records that have the specified search criteria?
If i put in [form]![control] in each of the 5 criteria in the query it comes back with no records, because it it looking for that type in every repair field rather than once between the five fields?
View 2 Replies
View Related
Dec 4, 2013
I have a subform query which includes EventName, Date, and Tags. Tags is a multivalued field.
What I need to do is allow people to filter this subform using the inbuilt filter, and then use the filtered data to populate a graph.
I am doing this by taking the Filter property from the form and then using VBA to build an SQL statement with the right data, and using that on the graph. It works fine when people filter on the EventName and Date fields.
However, when someone filters the Tags field, it fails. The filter property gets set to something along the lines of
([Lookup_Tags].[TagName]="whatevertagtheypick")
This syntax seems not to be supported when I put it into the SQL statement.
View 1 Replies
View Related
May 30, 2006
Hi I Like to know how to aquire the correct results:
Table:
ID - Personalinfo - location - records - date
102 - Dave Blogs - Bristol - Queen - 12.12.03
103 - Brian Smith - Plymouth - SKA - 12.11.03
102 - Dave Blogs - Bristol - Madona - 12.12.03
By selecting the ID I want to show all listings for that ID.
ie:
102
Dave Blogs
Bristol
Queen 12.12.03
Madona 12.12.03
Im going round in circles
View 4 Replies
View Related
Mar 17, 2014
I am looking to collate marketing data from different areas of our DB into a Marketing Hub. We have to collect various feedback at different times, if a client on a programme completes a course, they give feedback for that course.13 weeks after a client completes the programme we have to get them to complete a destination survey. On both these forms it asks would they be interested in doing any more courses. This data is then stored in the Courses table and Projects table respectively.
I want to create a query to become the record set for a new form which only shows those students that have stated they would be interested in doing other courses.Five of the options or Yes/No fields, and one is a text field (which courses, other essentially).How does one do a query that only shows records where any one of 6 fields is not null?
Code:
SELECT Students.ID, Students.[First Name], Students.[Last Name], Courses.[Sage Accounts], Courses.[Sage Payroll], Courses.[First Aid], Courses.[Food Hygiene], Courses.[CV Services], Courses.[Interview Skills], Courses.[Which Courses]
FROM Students INNER JOIN Courses ON Students.ID = Courses.ID;
View 2 Replies
View Related
Jun 12, 2013
I have a query that performs some calculations, these calculations feed into another query and produce a final value. If there are no null values everything works perfectly. But when there are null values, I get errors.
What I have tried:
1) I tried to implement the Nz function for each field of the formula that is not calculated but from user input.
2) I tried to implement the Nz function for just the field that calculated the last result before feeding into the next query, but it didn't show the 0 value I gave.
3) I tried to use UPDATE TABLE SET in SQL, but I don't think I was using it right. I tried to use it for all records but always got an error when it ran.
The first two above result in showing no values at all, even if there are some. The third didn't work. How to easily update null values in the query to 0? All I could think of was to somehow use Criteria or SQL.
View 14 Replies
View Related
May 16, 2013
I am trying to create a clean database and code to generate a report.
I am trying to count the number of null fields in one of my queries:
However, because of this expression, I cannot carry other fields with it. So the end result looks like:
But I would really like it to spit out the following information:
Total Not Fixed: 241
Department: Sustaining Eng
is there a way to create an SQL query to simply add data: I have tried the following:
Code:
ALTER TABLE qrySustainingEngNotFixed2 ADD Dept TEXT(25)
Insert Into qrySustainingEngNotFixed2 (Dept) Values (Sustaining Eng)
SELECT TotalNotFixed, Dept
FROM qrySustainingEngNotFixed2;
The above isn't working. Keep in mind that I want this is just for display purposes. I pondered making a custom table and then making a Union Query, but I'm trying to do this all in one SQL statement.
View 1 Replies
View Related
May 14, 2013
I have got a db and that has 10+ ref tables, and i need to show everything from those tables in one, and these tables includes Attachment field. I have tried union but did not work.
How can i achieve this?
View 4 Replies
View Related
Sep 22, 2014
I have a form called PAF_Assignment and on this form there are many textboxes for editing. After all fields are entered, the user clicks on a button and this button populates the PAF_Issued_Date field with the actual date.
Then I want to disable (enable=False) all fields on this form once there is a date on PAF_Issued_Date as I don't want any user to make any changes after submitting it, although the user would still be able to read the information submitted.
I have tried the following...
PAF_Assignment Form AfterUpdate Event
If Me.PAF_Issued_Date=Not null Then
Me.FieldName1.Enabled=False
Me.FieldName2.Enabled=False
Me.FieldName3.Enabled=False
[Code] ......
However this is not working, there is no error message or anything but the fields remain enabled with the date...
View 3 Replies
View Related
Feb 24, 2015
I have two tables: tbltasks & tblsafety
Both tables have a date field in, one table keeps tracks of my tasks the other the expiry date of some safety checks.
I currently have a timed pop up that looks at dates within a table that are within 30 days from now, if there are any dates the reminder pops up.
The problem is I want the pop up to look at two sepearte columns in two different tables, so I figured it would be easier to create a query combining these dates and just ask the pop up to look at that query date column.
I want to create a query that very simply lists all the dates in one column combined from both tables.
View 3 Replies
View Related
Jun 10, 2013
Trying to run a query using criteria to populate the query by looking at information from a field on a form, if from is closed I need that criteria to look at the table and return all date in table.
View 14 Replies
View Related
Jan 8, 2014
What is wrong with this query?
=IIf(IsNull([OffPeakDay],IIf(IsNull([OffPeakHour],Sum([Import]-[Export]),0)))
What I want is to sum the field (Import minus Export which are numeric) on the form if the OffPeakDay AND OffPeakHour fields are null.
View 11 Replies
View Related
Sep 24, 2013
I have a query which returns a calculated percent. I have ordered that in descending order, and now want to see the top 50. So (In Access 2010) I entered 50 into 'Return'. But it returns all of the records!
Is this because pct is a calculated field? How can I correct this? The SQL seems to be correct.
Code:
SELECT TOP 50 HeciFail1.POHECI, HeciFail1.POQTY, HeciFail1.FAILQTY, IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) AS PCT
FROM HeciFail1
ORDER BY IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) DESC;
View 1 Replies
View Related
May 22, 2015
I have a query which returns, among other things, a number of boolean fields. In some cases, there will be a genuine True or False value in each of these fields; in others, it can and should be Null (e.g. as a result of a 'failed' LEFT JOIN of some description, where there is no associated record in the joined table which fulfills the criteria)
So something like this :
Code:
SELECT [tblTable2].[fldBooleanField]....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, I will be writing the result of the query to a text file and here's the problem. I want to show a numeric value for a genuine True / False (i.e. -1 and 0 respectively using the standard boolean conversions in Access) and a blank for any Null values.
So I tried this :
Code:
SELECT CInt([tblTable2].[fldBooleanField]) AS fldBooleanField....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, currently when I look at the exported recordset in Notepad, I am getting 0's for both False and Null values (and -1 for True)
How I can adapt my query to keep Nulls...null? And convert the genuinely present boolean values to integer form?
Only thing I can think of is to use (untested) :
Code:
IIf([tblTable2].[fldBooleanField] Is Null, Null, CInt([tblTable2].[fldBooleanField]))
But there's a number of boolean fields in there, all requiring the same treatment.
View 14 Replies
View Related