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
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] ....
I have a table which has duplicate values and i need to delete them, how do i do that?the table fields are
field1 field2 field3 Field4
1 a aa bb
1 a aa bb
1 b bc ab
2 a aa bb
2 a aa bb
I want to delete the ones which are bb in Field 4 and keep only bb value, how do i do this?
Thanks
I have an unbound text box. I put a value, run an append query and append the value to the table. I want to know if I can delete the value in the text box automatically when the append query excutes. I am using a macro to run the append query.
Thanks.
How do I delete values in an array? I want to reuse that array but first I need to delete its values so I would start with Arr(0) rather than where I stopped off with the last loop at Arr(10) [assuming the last calculation inserted until Arr(9)]
Here is my code:
Set rst = CurrentDb.OpenRecordset( _
"Select * from dbo_ProductStructure where ChildProductNbr Like '*" & txtPartNumber & "*'") 'search associated fields with user input
While rst.EOF = False
ReDim Preserve Arr(i)
Arr(i) = rst.Fields("ParentProductNbr")
i = i + 1
rst.MoveNext
Wend 'end of while loop
x = Arr
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?
Hi guys,
I have a question I've been trying to figure out with no luck. I have 2 fields, companyname and zip, these 2 fields may have duplicate values or they may be uniquei.e.
companyname zip
billybobs 68135
dilberts 68137
billybobs 68135
what I need to do is delete all of the duplicate records where both companyname and zip match. Is there a way I can do that with a query?
Any help is greatly appreciated
Thanks
James
Im trying to delete values from a table based on the selected values of a listbox. The listbox values have a hidden column which related to the ID on the table to which I am trying to delete from (if that makes sense).
If I use the following code:
Code:
Set ctl = Me.Results_listbox
For Each varItem In ctl.ItemSelected
CurrentDb.Execute "Delete * FROM Table1 WHERE Table1.ID = " & ctl.ItemData(varItem)
Next varItem
I get an error stating Object doesn't support this property or method.
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
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
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
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!
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
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
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.
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?
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
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???
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
View 2 Replies View Relatedi 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
Hi, when doing an Access query is it possible to hide null values?
Thanks
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 RelatedHi 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!
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.