I've found several threads on this subject, but I still can't get it to work, so please forgive me if I seem to be duplicating a question.
I'm making a very simple db. It has only 2 tables:
MATERIAL (one)
TEST RESULTS (many) with left join.
I need to report average test results (by MATERIAL) weekly, even if there were no tests for a particular product that week, ie;
MATERIAL A = 100
MATERIAL B = 90
MATERIAL C =
MATERIAL D = 105
When I try to query, I only get results for A, B and D. It seems to be ignoring the MATERIAL table and looking only at the TEST RESULTS table.
I'm sure it's something simple that I'm overlooking but how do I get C to show, even if it has no results.
Here's my query SQL.:
SELECT DISTINCTROW [MATERIAL Query].MATERIAL, Avg([TEST RESULTS].V50) AS [Avg Of V50]
FROM [MATERIAL Query] LEFT JOIN [TEST RESULTS] ON [MATERIAL Query].MATERIAL = [TEST RESULTS].MATERIAL
WHERE ((([TEST RESULTS].[TEST DATE]) Between [Forms]![Date Range Form]![Start Date] And [Forms]![Date Range Form]![END DATE]))
GROUP BY [MATERIAL Query].MATERIAL;
It might be an easy one but I just wasted the past hour deciphering through my code in order to solve the run-time error '94' that I'm getting when trying to execute the following code:
Code: Private Sub cmdUpdateDates_Click() '################################### 'This sub aims at combining the timesheet date and the start and end time into the fields [Start Time] and [End Time]. '################################### Dim intCounter As Integer intCounter = 0 Dim rs As ADODB.Recordset
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
I want query to show all results including any null values because at the moment it just misses the null values out.
So for example instead of the query saying that Fruiterer A has 5 apples, 0 oranges and 4 bananas it just says that Fruiterer A has 5 apples and 4 bananas. Therefore because oranges was a null value then it simply makes no reference to oranges.
I have two tables called table1 and table2 with following fields:
Table1: Name NameId
Table2: Name NameID Visits
where name and nameid is same but visit can me 0 to 230. name in table2 is same as table2 but only showing visit 1 or higher. How can I make new table that can list all name, nameId and visits.
I can create new table but it shows only table2's record. so all I need is table1 and table2 record if names are missing in table2 then put 0.
Hi all I have 3 linked tables in a vehicle database. 1 keeps a record of Service Due date, 1 has the Fleet numbers and the last contains Vehicle Condition reports. They are linked by the Fleet numbers. I have a query that returns the Services due between 2 dates. What I would like is to have the same query return any outstanding Vehicle Condition items as well for any Vehicles due for servicing. Currently, I can include the field in the query but if there are no current items for a vehicle in the Vehicle Condition table then the query does not return that vehicle as a result. Any suggestions would be appreciated.
Now I want to count the number of sales completed from these totals. However, as Dept 3 has no sales completed for October it does not show in the query. So I get
Dept 1 - 7 Dept 2 - 2 Dept 4 - 6
Any idea how to get Dept 3 - 0 to show up in the query?
I am trying to build a query that combines several fields. The situation is as follows....
1. If a is > b then i need a 2. If b is > a then i need b 3. If a is null then b is not null 4. If b is null then a if a is not null 5. If a and b are null then '999999'
I have everything taken care of up to If 4. The problem i am having is that when create the sql language for 5 it is adding 999999 to the filed when step 3 or 4 are the case. See languange below.
Any help would be greatly appreciated.
IIf(MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg]>MSC.[TDS<2500_S_GW_NR_Model_mg/kg],MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg]) & IIf(MSC.[TDS<2500_S_GW_NR_Model_mg/kg]>MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg],MSC.[TDS<2500_S_GW_NR_Model_mg/kg]) & IIf(MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg] Is Null,MSC.[TDS<2500_S_GW_NR_Model_mg/kg]) & IIf(MSC.[TDS<2500_S_GW_NR_Model_mg/kg] Is Null,MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg]) & IIf(MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg],MSC.[TDS<2500_S_GW_NR_Model_mg/kg] Is Null,'999999') AS MSC_SGW
I have a series of values within a table that includes a number of null values. I would like to include this field within a query, however if the value is null I would like to show a 0, if the field is not null, I would like it to show the original value.
I have tried to use the IsNull in an iif formula without luck.
PS: If I was conducting this in Excel I would use the following =IF(J3="",0,J3)
I have a query set up to sum 2 fields to a 3rd field, for example, I have field1, field2, and field3. Fields 1 and 2 are data from a table. In Field3 I have an expression: Field3: [Field1] + [Field2] This works fine except when either Field1 and/or Field2 contain no data, then the sum does not work. Any ideas how to correct this? I realize this may not be the best way to accomplish what I am trying to do but this is a small part of a very large application and i would like to change as little as possible since everything else is working ok. Thanks very much.
hello friends, I have written an insert query but sometime it does not work properly and leave some column blank. I want to write a query which should check for the blank columns and delete these records.thanks in advance
I currently have a crosstab query that compares data for two years. The results look like this.
month 2004 2005 January 98% 95% ..... Decemeber 98
What I am getting at is that the query returns null values for the fields that do not have data recorded for the months in 2005 because we have not reached them yet. Is there a way for me to make sure that at least the value 0 is entered in the null value areas?
I need to create a query with the MTD field I have created the MTD and it works!!! but now I need it to show all of the PartNum fields even if null. do I need to convert all nulls to zeros? if so how do I go about this?
Here are the fields in the query
First Field: Field: PartNum Table: Part Total: Group By
Second Field: Field: UnitPrice Table: OrderDetail Total: Group By
Third Field: Field: NumOrdered Table: OrderDetail Total: Sum
Fifth Field: Field: CurrentYear: Year([OrderDate]) Table: (blank) Total: Group By Criteria: Year(Date())
Sixth Field: Field: CurrentMonth: Month([OrderDate]) Table: (blank) Total: Group By Criteria: Month(Date())
When I run the query I only get 2 parts and their total. but I want all parts to show up even if they have not been ordered in the month. the parts that have not been ordered should say $0.00 in the MTD field.
Hi all, need some help. In my query I have 4 fields, a weekly labour cost, weekly material cost, weekly plant cost and a weekly summary. The problem I have is that my weekly summary shows a null value on some weeks as not all the weekly costs have a value assigned to them.
I have been told there is a way in the query to convert Null values to zero so that the weekly summary field doesn't show a blank cell.
Could someone please explain to me how this is done? Thanks
Hi all,i am running a query that (as part of it) returns a total time.. using a formula i found on this site. this formula uses a start and end time and works wonders. works!IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60my problem however is an option that i need my users to have to enter a time not based on a start end time, but a "OtherTime" if u will. (ie they have 4 hours instead of writeing 1:00 - 5:00) already changed this from default value null to 0... (which i didnt wanna have to do cuz it messes up the query)Doesnt Work(IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60)+[OtherTime]i want the total time in the query to show the 4 hours, but it is leaving it blank. i am guessing that it is because my start end times are blank(null) is there a way to add a if statement into a query expression that will bypass this? Tried, but thats invalid in a query expressionTotalTime: If IsNull [StartTime] Then =[OtherTime]Else IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60+[OtherTime] end if
i have checked the forums and i know they kind of answer this question, but im not really sure where i am ment to write it!
I have two queries, which a third query subtracts the 1st query and 2nd query values and gives the end value. If the 2nd value is null, i get a null value at the end, as i need to change this to zero instead. i know i need to use nz (as seen in microsoft access help, and other places on this forum) but im not sure exactly where to put this on my query, and in which query.
I am using a query with Like statements that looks at a field in a form. The form defaults to "*" so that all of the records will show. You can search with the normal wildcards. The problem I have is that the records containing Null values don't appear. I was trying to see if I could use an IIF statement in the criteria of the query, but have had difficulty. What is the proper way to have all records show if * is the default, but only the specific criteria if something is entered using wildcards?
Within my query I have a field that I need to do a nested IIf Statement that has a NULL value. The name of the field is "smokingcessation". The possible responses are a "1" = Yes, "2"= No, "3"=Unknown and there is also the possibility of a "no answer" at all (blank). I've got the following statement to work so far but need to include the possibility of a blank/no answer response. My statement so far is as follows and is working:
How would I do a statement that would take into account the existing possibilities plus that of a blank (NULL) answer and leave the field blank in that case? In otherwords, I need to see a "Yes", "No", "Unknown" or a blank field.
I've written a query that is assigned either a pass/fail depending on certain criteria. Here is the query:
Pass/Fail: IIf([jc_actlevel]="Yes" And [jc_diet]="Yes" And [jc_medications]="Yes" And [jc_followup]="Yes" And [jc_sympworse]="Yes" And [jc_weightmonitor]="Yes","Passed","Failed")
I want this query to only show me records that have a "yes" or "no" value but some on the records have neither a "yes" OR "no" (just a NULL value)and are showing up in the query as "failed" which is throwing off my count of how many records met the pass/fail criteria (see attached).
How can I NOT show records that have a NULL value in my query? Thanks for any help.
Hi, I have a problem with one of my query, the query has 2 tables, the secondary field from Table2 is linked to the primary field of table1. The primary field Doesn't allow null Value The Secondary Field does allow null value which means that the Data in table 1 only concern Some of Table2's Data. If I run the query with the two tables I can only see the records with the secondary field with no Null Value. Is it possible to get the query Showing All the records of table1 2 and Table1 even if Table1 has no values?
SysID PersonData " " " " AddressData " " " " " " " " " TelephoneData " " " " Year (this is 1 or 2)
I'm taking each component Person, Address, Telephone info and making a compacted table of each (i.e. lots of Jane Does etc, appear in both years) Each table with have a URN
I then need to update the big table with the URN so move onto another part of getting this data into a usable format.
Basically what follows is my question on how to get the URN's back into the table:
Is there a quicker way (or a way I'm not seeing) of doing the following.
I have a set of data 'tblDatabaseTable, say:
ID Field1 Field2 Field3 Field4
In the database.
I have an imported file which will be tblImportTable, same deal without the ID:
Field1 Field2 Field3 Field4
I need to compare the data on each table:
SELECT * FROM tblDatabaseTable INNER JOIN tblDatabaseTable ON tblDatabaseTable.Field1 = tblImportTable.Field1 AND tblDatabaseTable.Field2 = tblImportTable.Field2 AND tblDatabaseTable.Field3 = tblImportTable.Field3;
Works great as long as all the fields have values in.
Is there a way to compare the tables and get results as:
Database Ian MacConnell null
Import Ian Macconnell null
Match!
without doing something like:
SELECT IIf(IsNull([Field1]),' ',[Field1]), IIf(IsNull([Field2]),' ',[Field2]), IIf(IsNull([Field3]),' ',[Field3]) FROM DatabaseTable
SELECT IIf(IsNull([Field1]),' ',[Field1]), IIf(IsNull([Field2]),' ',[Field2]), IIf(IsNull([Field3]),' ',[Field3]) FROM ImportTable
before comparing them???
Phew, hope that all makes sense, very long day and I'm........
