Recordset Indicies & Null Values

Mar 15, 2007

I'm using VBA & ADODB to maneuver through a recordset that contains a single record.

All variables are defined per Option explicit, and are all properly initiated.

When I run the SQL statement in SQL view of an Access Query, I get a single record with 7 fields. Each field contains the correct (non-null) value.

When I run the below code, I get message boxes that contain "*field name* = " -- the value is null.
Code: Dim rs as ADODB.Recordset Set rs = New ADODB.Recordset rs.Open sqlstr, conn, adOpenDynamic, adLockReadOnly If Not rs.EOF Then rs.MoveFirst For i = 0 To 6 MsgBox rs.Fields(i).Name & " = " & rs.Fields(i).Value Next i rs.close End IfOh, and the kicker is: this code worked yesterday.

So what gives? My connection must be OK, otherwise the query would be bombing at the .Open command. I'm obviously receiving results, because it's delivering message boxes. But where did my values fall out? Like I said, I'm receiving results when I run the SQL outside VBA...

<edit>

Just in case you're interested, here's my SQL. My table contains a Float for turnaroundTime, and the Level is restricted to Lvl1 ... Lvl7.
Code:SELECT [Lvl1Qry].[TheAvg] AS Lvl1Avg, [Lvl2Qry].[TheAvg] AS Lvl2Avg, [Lvl3Qry].[TheAvg] AS Lvl3Avg, [Lvl4Qry].[TheAvg] AS Lvl4Avg, [Lvl5Qry].[TheAvg] AS Lvl5Avg, [Lvl6Qry].[TheAvg] AS Lvl6Avg, [Lvl7Qry].[TheAvg] AS Lvl7AvgFROM (SELECT AVG([myTable].[turnaroundTime]) AS TheAvg FROM [myTable] WHERE [myTable].[Level] Like "Lvl1*") AS Lvl1Qry, (SELECT AVG([myTable].[turnaroundTime]) AS TheAvg FROM [myTable] WHERE [myTable].[Level] Like "Lvl2*") AS Lvl2Qry, (SELECT AVG([myTable].[turnaroundTime]) AS TheAvg FROM [myTable] WHERE [myTable].[Level] Like "Lvl3*") AS Lvl3Qry, (SELECT AVG([myTable].[turnaroundTime]) AS TheAvg FROM [myTable] WHERE [myTable].[Level] Like "Lvl4*") AS Lvl4Qry, (SELECT AVG([myTable].[turnaroundTime]) AS TheAvg FROM [myTable] WHERE [myTable].[Level] Like "Lvl5*") AS Lvl5Qry, (SELECT AVG([myTable].[turnaroundTime]) AS TheAvg FROM [myTable] WHERE [myTable].[Level] Like "Lvl6*") AS Lvl6Qry, (SELECT AVG([myTable].[turnaroundTime]) AS TheAvg FROM [myTable] WHERE [myTable].[Level] Like "Lvl7*") AS Lvl7Qry; </edit>

View Replies


ADVERTISEMENT

Modules & VBA :: Invalid Use Of Null With No (obvious) Null Values

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

Setting Date Field In Recordset To Null

Nov 15, 2014

Error 1: Setting Date Field in Recordset to null

Error 1: Setting Date Field in Recordset to null " data conversation error 3421 "

Solution: If the field is null set it back to itself .

Here is a simple dummy example i wrote to demonstrate the solution ( look for the bold text in side the code )

Code:
Sub Event_btnSaveEndTime ()
dim strEndDate as string
With Form_frmMainForm
strEndTime = .txtEndTime.Value

[code]....

View 3 Replies View Related

Field Criteria: Is Null; There Are Null Values In That Field; No Records Are Returned

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

Using A Recordset To Fill Text Boxes With Field Values

Jul 13, 2005

Hi guys,

This is my first post of hopefully many, and I hope to be able to keep visiting and helping others in the future.

I'm building a database at the moment which I've done basing forms on Queries as I'm very familiar with doing so. However, one form in the database if a bit different.

The form in question is for shipping off refurbished units at the warehouse, by adding a SHIP ID to the record for each unit scanned in. The form before creates a recird ubdexed by a SHIP ID in the SHIPPING table, along with some other info such as delivery address and ship date. Each unit will be assigned this SHIP ID by the child form I wish to create, by scanning the Serial Number against it.
Two actions are done on the child form: Read in Serial Number of unit, find record in MAIN table and display key fields such as the units status (i.e. "WIP" or "REPAIRED" into text boxes on the form Allow unit to be shipped (by assigning the SHIP ID to the unit's record in the main table, only if the STATUS field shows as "REPAIRED"
Now, because I want to scan units into the form's Serial Number textbox and do the STATUS field check, then assign the SHIP ID; I need a recordset, right? I've inherited development of datdbases which used recordsets in their forms before, but have never had to create one from scratch. Also I wasn't able to take a copy of the source code of those databases before I left my last company.

Can anyone give me some info on what the bare minimum is to create a recordset, and to use it to save the record once I've made changes.

For your info I would consider myself an Access intermediate, and I've worked with it in various roles and versions for around 5 years.

Thanks,
Gareth

View 6 Replies View Related

I Want To See Null Values......

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

Null Values

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

Null Values

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

Null Values

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

Not Getting Null Values

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

Null Values

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

Null Values...

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

Sum Of Null Values

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

Null Values

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

Deleting The Null Values

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

Crosstab With Null Values

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

Null Values In Query

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

Converting Null Values To Zero

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

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]))/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 Related

Nz - Null Values In Queries

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

Hiding Null Values?

Jul 18, 2005

Hi, when doing an Access query is it possible to hide null values?

Thanks

View 1 Replies View Related

Null Values And Like Statements

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

Null Values In Crosstabs

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

Count Null Values

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

IIf Statements And NULL Values

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

IF Statements With NULL Values

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







Copyrights 2005-15 www.BigResource.com, All rights reserved