Addition With Null Values
Feb 28, 2007
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]))/60
my 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 expression
TotalTime: If IsNull [StartTime] Then =[OtherTime]Else IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60+[OtherTime] end if
View Replies
ADVERTISEMENT
Apr 17, 2006
hello guys
i have values in two unbound textboxes and now i would like to assign the sum to a bound textbox. Also need AfterUpdate event too.
Can any expert help?
Thank you
View 4 Replies
View Related
Jul 5, 2013
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
[Code] ....
View 1 Replies
View Related
Nov 16, 2007
I think the title pretty much sums it up....
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.
The query is refusing to return any results...
Any ideas?
View 10 Replies
View Related
Aug 23, 2005
Easy one for the experts here I hope:
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.
Please help, many thanks,
Paul
View 2 Replies
View Related
Sep 26, 2005
Hello,
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.
Viral
View 2 Replies
View Related
May 24, 2006
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.
Regards
Craig
View 2 Replies
View Related
Oct 30, 2006
Hope someone can help with this. I'm sure it's dead simple but it's driving me insane!
The scenario is a number of departments who are processing something (sales for example). So, for October
Dept 1 - 20 (Sales)
Dept 2 - 10
Dept 3 - 2
Dept 4 - 7
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?
Any help greatly appreciated!
View 3 Replies
View Related
Dec 14, 2006
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;
As always, thanks in advance.
BeckieO
View 3 Replies
View Related
Sep 28, 2007
Hi all.
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
View 2 Replies
View Related
Oct 3, 2007
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)
View 5 Replies
View Related
Jan 14, 2008
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.
View 2 Replies
View Related
Jan 16, 2005
I'm trying to get my head around null values.
data required is no. Allow zero length strings is yes.
txt2 is a memo field
in a report on page event, I'm trying to get the following code to work.
If IsNull(txtg2) = True Then
Me.lblg2.Visible = False
Else
I've tried this too:
If Me.txtg2 = Null Then
Me.lblg2.Visible = False
Else
It doesn't seem to work. Any ideas?
View 8 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Aug 9, 2005
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?
View 2 Replies
View Related
Dec 4, 2005
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
Fourth Field:
Field: MTD: Sum([NumOrdered]*[UnitPrice])
Table: (blank)
Total: Expression
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.
Any Suggestions???
View 2 Replies
View Related
Mar 30, 2006
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
View 6 Replies
View Related
Sep 12, 2007
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.
Basically:
(qryCountpartNo.CountPartCode)-(qryTotalquantityallocated.sumofquantity) = qryTotalFree
both countpartcode and sumofquantity may be null at any time
Any help would be greatly appreciated!
Thanks in advance,
Emily
View 14 Replies
View Related
Jul 18, 2005
Hi, when doing an Access query is it possible to hide null values?
Thanks
View 1 Replies
View Related
Aug 8, 2005
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?
View 5 Replies
View Related
Apr 24, 2006
Hi there, instead of blank fields in my crosstab query, and hence my report, i would like a standard comment, such as 'No Booking'.
My crosstab is something like this:
.........A...........B..............C............. D
1.......X
2.......X..........................X
3...................X............................. .X
(Ignore the dots, obviously)
Currently, when i try to open the report based on this query, it fails unless each column has data in it for at least one record.
I've tried using the Nz function but haven't been able to make it work. Thankyou in advance!
View 8 Replies
View Related
Jan 26, 2007
Hello,
I am using the following code to count records in a table:
Dim myCount
'Looks the data with criteria 1 in table
myCount= Nz(DCount("[Myfield]", "[mytable]", "[myfield]='1'"), 0)
'Returns sum in text box
Me.[txt1] = myCount
My table will also contain no records in the myfield. Is there a way I can count null values? Thanks.
View 1 Replies
View Related
Apr 10, 2007
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:
IIf([smokingcessation]=1,"Yes",IIf([smokingcessation]=2,"No","Unknown"))
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.
Thanks for any help in advance.
View 6 Replies
View Related
Jul 23, 2007
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.
View 1 Replies
View Related
Aug 4, 2005
Hi,
I am calculating two fields (qtyorder-qtydesp) in a stored procedure.
The second field is from another query (view) which may not have a record relating to the main record (stored procedure).
The problem is the second field is simply left blank but I need it to be 0.
It makes the calculation in turn result in a blank field, i.e. 1-0 should equal 1 but because of the blank field it returns a null value.
I found that the good old Nz function wont work with stored procedures, is there any alternative?
Thanks,
James
View 5 Replies
View Related
Nov 9, 2005
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?
View 1 Replies
View Related
