Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Visual Basic





See Related Forum Messages: Follow the Links Below to View Complete Thread

Updating Date Field With Null Value (date Picker)
Hello,

I am using VB and access. For date I am nsing date Picker. How can I update a date with null value?

I am using the code update the date field:

DateColumn=#" & Format(DTP1, "mm/dd/yyyy") & "#

When I try to udate the field with null value I receive error: syntex error in date in query expression '##'.
Please advice me how to update with null values.

Regards,
Elahi

How To Set A Date Field To Null
Can someone please tell me how to set a date or a time field in ACCESS database to a null, meaning I want to see it BLANK.

I am trying to set it to vbNull or just NULL but what I see is 12/31/1899. Is there a way to leave it blank.

Update payments set Next_date = vbNull
where C_id = 3

this way I am seeing 12/31/1899, but I want to set it to blank.

thanks.

Inserting A NULL Value Into A Date Field **** ****
Hi all,

I have a function called ConvertDate that takes a string value in "yyyymmdd" format, rearranges it with slashes '/', and returns the newly constructed string.

Here is the code:


Code:
Public Function ConvertDate(sDate As String)

Dim sDay As String, sMonth As String, sYear As String, sWholeDate As String

sYear = Mid(sDate, 1, 4)
sMonth = Mid(sDate, 5, 2)
sDay = Mid(sDate, 7, 2)

sWholeDate = sMonth & "/" & sDay & "/" & sYear

If IsDate(sWholeDate) = True Then
ConvertDate = sWholeDate

End If

End Function


Afterwards, I have an SQL stored procedure that enters the value into a field of type "smalldatetime". The problem is when I come across a person with no date of birth. I would like to enter a null value or something acceptable into the DB if there is no date.

If I try to return a Null value in the function, I get an invalid use of null error. Please help.

Sanctos

Filling Date/Time Field With NULL
Shalom,

I have a Date/Time field in a Microsoft Access 2000 database and I need to fill it will nothing (NULL). Here is my current statement:


Code:
gtSQLStmt = "UPDATE TICKET_DETAILS SET" & _
" sched_dte = '" & Null & "', " & _
" alert_user = 0" & _
" WHERE index_num = " & Val(txtIndexNum)

StdLibDB.execute_sql_sub gtSQLStmt, adoConA
When I execute this code I get the following error:

"Data type mismatch in criteria expression"

(this is on the execute_sql_sub line which executes my sql of course)

How can I put a NULL or nothing in that Date/Time field?

GrayKnight

How To Insert Blank Or NULL In A Date Field
I have a table that has 2 fields defined as Date/Time. At the INSERT INTO time, how can I leave those fields BLANK or insert a NULL in them?

I set a variable as vbNULL and assigned that to those two fields and when I looked into the table both of those fields have 12/31/1899 in them. I want one field to have a date and the other to have time in it.

I am using INSERT INTO TableName VALUES( .... ) method where I have to insert something in all the fields. Please tell me if there is a way of inserting NULL or leaving them blank or not. Because if there is none then I have to change my code and thats what I want to prevent, LOL.

Thanks.

Entering Null Into An Access Database Date Field
Hi,

I've recently changed the format of a field in my development database from text to date as i need to order the output by descending date. Unfortunately now I can't update my db with "" in the date field, it says there is a data mismatch in vb when I run a data environment command to update the db.

Any suggestions? I've tried changing the parameter type to date but this doesnt' solve it. Occasionally the field will be null and thus shouldn't have any value, so I need to get round this.

Thanks



Edited by - GraemeW on 1/20/2005 3:22:38 AM

Inserting NULL Value Into Date/time Field - Sybase -
i cannot insert a null value into a date/time field in a sybase database


Code:
'STORED PROCEDURE
sybObj.CommandText = "Data_Put"

         acci_insert(0) = "Null" 'GET IMPLICIT CONVERSION ERRORS
         acci_insert(0) = "Null" 'TRIED Null, NULL, DBnull.value, "", '' - nothing works

        'EXECUTE STORED PROCEDURE
        sybObj.Execute , acci_insert



The field has been set to allow Null values.






Edited by - AbbydonKrafts on 3/20/2007 6:21:53 AM

How To Use NULL Date Field When Calling Stored Procedure With ADO
Hello,

I am trying to call a stored procedure with ADO. For some of the parameters for this procedure, I wish to use DATETIME. My problem occurs when I want to pass a NULL value. When I set theCmd.Parameters.Item("Date").Value = NULL, I later get the following error from the err object. [Microsoft][ODBC SQL Server Driver]Syntax error converting DATETIME from character string. The table in the database allows nulls, but I can seem to add one with VB. Please help me figure out what is wrong.

Thanks.

Multiple-Step Operation Generated Errors When Setting Date Field To Empty Or Null
I have a form with a bunch of bound controls and a few are date fields. When I try to clear out a date that has been previously set in the record I get the Multiple-Step Operation Error -2147217887(8004e21) The database allows nulls for these date values, so it is OK at the DB level - looks like the ado is causing the problem.

DB is SQL Server 2000

I found the following on Microsoft's site that is directly related to my problem - looks like there is no workaround according to them.

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q247/2/02.asp&NoWebContent=1

Anyone else have any ideas? There is to much invested in using the bound controls at this point - I cannot abandon this method.

NULL Date Error In VB Code For Access
I am trying to look into an Access table. If certain conditions are met, I want the variable CompactorDate to store the date. The code works fine if the DLookup function returns a date. The program errors out if DLookup returns Null. The IF loop doesn't seem to solve the problem either. The error is: "Invalid use of Null"

Dim CompactorDate As Date
If CompactorDate = Null Then
CompactorDate = "1/11/1999 10:00:00 PM"
Else
CompactorDate = DLookup("EPCTimeStamp", "qryTempData", "EPCStatusDesc='Compactor' ")
End If

Any ideas on how I can solve the Null problem?
Thanks

How To Put Current Date In To Access Field From VB Code?
Hello All,

I am Having some trouble putting Date value in to a Access DB file with VB6.0.

Here's what my code (Just for the insertion part) looks like. Based on a timer interval, I am trying to write the date value (short format)in to my Access DB
table Named "DAQ".

------------------------------------------------------------
Private Sub Timer1_Timer()

Set DBX = New ADODB.Connection
DBX.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:My.mdb"

DBX.Open

DBX.Execute ("Insert Into DAQ(LDate) Values('Date', 'my data')")

DBX.Close

End Sub

---------------------------------------------------------------------
I tried formatting my access 2000 column "LDate" with various date formats without any success. Can somebody point out what I am doing wrong here and may be help out with the proper syntax for doing this?

I can get Access to write in the date automatically by setting the field parameters thru Access each time I send "my data" to the DB (without using "Date", or "Date()" or "Now"). But I want to learn how to add the date to a Access DB field with VB code.

Thanks.

SkyFox

Interesting Issue - Sorting By The Date Field Code For The Grid
This works good for sorting by the autonumber field (FAMILYID) but its not good for sorthing by date, can someone please tell me how can i change this to sort by the date field which is REF_DT. I substituted REF_DT for FAMILYID below and it just errors out with the error - improper use of variable. The view for adopatients is VWOUTPATIENT and i've tried to use ascending there on the date but that does not work. How can i change recid to something else so it will function any ideas would be very much appreciated.


Code:
Private Sub Command1_Click()
' ***** simple sort button *********
Call SortRtn(0, Me.grdOutP.Columns(Me.grdOutP.Col).DataField, Me.grdOutP.Col, "ADOPATIENTS")
' **** end *****
End Sub


Public Sub SortRtn(intSortMode As Integer, strColname As String, intCol As Integer, strAdo As String)
' *************** sort by the autonumber field ************
'A Public Sort Routine
'intSortMode determine sort method 0 for ascending 1 for descending
'strColName determines the column in the grid to sort by
'intCol is the column number
'strAdo determines which grid to sort
Dim recid As Long 'What record are we currently on
Select Case strAdo
Case "ADOPATIENTS"
recid = FRMOUTPATIENT.adoOutP.Recordset.Fields("FAMILYID")
If intSortMode = 0 Then
FRMOUTPATIENT.adoOutP.Recordset.Sort = strColname
Else
FRMOUTPATIENT.adoOutP.Recordset.Sort = strColname & " DESC"
End If
FRMOUTPATIENT.adoOutP.Recordset.Find "FAMILYID = " & recid
End Select
' **** end *****
End Sub

Field Returning Null; VB Not Seeing Null
Now this is a weird one and I really hope one of you can help!

In my recordset, there are two fields that I do not want to contain nothing, but they might, depending on what the user enters.

When I get my recordset from my SQL Server database, the values of Field1 and Field2 are examined to make sure they're not Null. Now the problem is this: when I check the value of the fields (through the debugger), it's "Null", but when I put an if statement to find it, it doesn't find anything!


Code:
if tRecordSet.Field1 = Null then
msgbox "NULL FOUND"
end if

Is there something else I should be putting instead of "Null" even though the value of that Field in the recordset IS Null???

FIELD WITH LENGTH ZERO, FIELD NULL
Before recording them I register I move like this the fields

rs.Fields ("te_nome ") = txtnome
rs.Fields ("te_endereco ") = txtender
rs.Fields ("te_bairro ") = txtbairro
rs.Fields ("te_cep ") = TDBMSKCEP
rs.Fields ("te_municipio ") = txtmunicipio
rs.Fields ("te_uf ") = DBCMBUF
rs.Fields ("te_telefone ") = tdbmskfone
rs.Fields ("do_sequencia ") = DBCombo1.Text

when I will record it gives the following message:

The field <name of the field> it cannot be a sequence of characters of length zero
I already tried to do the following:
rs.Fields ("te_bairro ") ="" & txtbairro

but doesn't it also work, as I should proceed?


------------------
The blessing of God enriches and it doesn't increase pains

Date Time Picker Control: How Do Set The Displayed Date To Null?
I have a front end database form with date fields using the MS dtp control. The fields are unbound, but populated using a data class. The problem is that on many occasions the date field is Null, ie no data available. On display the dtp control always defaults to a set date, usually today (this is a property function of the control). What I can't seem to do is to display no date, or to delete the date displayed. Any thoughts would be very gratefully recieved. API perhaps?

I'm trying very hard not to have to write an Active-X control based on the dtp (mainly because of multilingual support and different localisation issues regarding dd/mm/yyyy (used by 90% of the world's population, and mm/dd/yyy - used by the remaining 10% in the US - sorry just had to get that jibe in for those of us who despair at having to go in to custom formatting in Excel simply to get dd/mm/yy )

Many thanks

Every Record Has A Date Field. I Want To Find A Date And From This Date, I Want To Read Records To E
Every record has a date field. I want to find a date and from this date, I want to read records to eof. What is the code? - Thanks

HOw To Set A Field As Null
Im working with an access database through visual basic datasets.

I have a date field wich may have a value and i want to make it Null.
Does anybody now how to do this ???

Thanks

Martin

Null Value In A Field
I try to retrieve data from the database, and put it into a database, if the field has a null value then the error message will be pop up. How to prevent this?


Code:
Set rs2 = New ADODB.Recordset
ls_Sql = "select supp_code from ic_order where ord_no = '" & Combo3.Text & "'"
rs2.Open ls_Sql, gconn, adOpenStatic
If Not rs2.EOF = 0 Then
MsgBox "no records found"
Exit Sub
End If

If Not rs2.EOF Then
Combo2.Text = rs2.Fields("supp_code").Value
End If

rs2.Close
Set rs2 = Nothing

Ado Field Null
I need my string to equal the following if my db field is NULL, I've searched the board, as this should be a simple fix, but I've tried everything and can't get it to work...it must be REALLy simple, but I'm not catching it.

If adoMRS.Fields("ClearName") = NULL Then
strCD = " "
strCN = " "

strT1 = strCD & " " & strCN & " " & adoMRS.Fields("Code") & " " &adoMRS.Fields("Description")
End If

Null Field
Hi
I have an SQL server 2000 table that contains a field PDate(date type)
When i run the following query,
Select Distinct (DatePart(year,PDate)) from Article Group By DatePart(year,PDate) Order By 1

I obtain 2006 and Null.
How can I write a query that returns to me the records having a Pdate equal to Null.

thanks

How To Know The Value Of Field IS NULL Or Not
Hello,
Please tell me how to know that the value of a field is NULL or Not Null

I m using this code but it is giving error "Type Mismatch"

Set RS = New ADODB.Recordset
With RS
.Open "Select * from Tbl_Temp_Roz where Ac_Name = '" & AcName & "' and Ac_City = '" & AcCity & "'", Conn, adOpenDynamic, adLockOptimistic
On Error Resume Next
AcCity = .Fields(0)
End With

If AcCity Is Null Then
MsgBox "City Null"
End If

Please help me
Farooq

See If Field Is Null
hello everyone,

I am trying to load a treeview (ya still) and I get it to load the child no problem, but I want to load the field that is empty as a folder child. this is my code.

Code:
Dim SelectString As String
SelectString = "Select * from Routine where [Counter]= " + Label2
Set rsFiltered = dbName.OpenRecordset(SelectString)
Do While Not rsFiltered.EOF
'Add routine nodes
If rsRoutines.Fields("ParentID") > "0" Then
tvwMain.Nodes.Add "Routine" & rsRoutines("ParentID"), _
tvwChild, "Routine" & rsFiltered("counter"), _
rsFiltered("Routine"), "code", "codered"

'Else
If rsRoutines.Fields("Description") = Null Then
tvwMain.Nodes.Add "Routine" & rsRoutines("ParentID"), _
tvwChild, "Routine" & rsRoutines("counter"), _
rsRoutines("Routine"), "closed", "open"
End If
End If
rsFiltered.MoveNext
Loop
rsRoutines.MoveNext
Loop

'Code improved by vBulletin Tool (Save as...)

ok it loads child nodes if the field ParentID > 0, but the folder child I want to load is also greater than 0 so I was trying to load it by another field. I don't think this is working because it already loaded it in the previous if..then statement.

How do I change it to load if ParentID > 0 but if the Description field if empty to load it as folder child. does any of that make since?

TIA

Null Field
If in Access 2000, I have a field a text field with no data inside. Then in VB6, when I assign that field to a text box, I get a error with 'Invalid use of null'. I have to place a if statement before the assign statement to make sure the field is not equal to "", only then I will assign it. This will prevent the error but it is very troublesome if I have about 20 to 30 field to check.

Is there any option or way that I can use without having checking for empty text field ? If it just an empty field, just assign a empty string to it.

Thanks

Null Value In The Field
I am trying to pick up data from an excel file by using ado recordset . one of the column in that excel file contins some null values and some numeric values. but while picking up data it always shows null values. but when the numeric values are converted to char datatype in the excel file then the values gets picked up properly.
Please provide me with the solution of the problem

Thanking you

Field Value Being Read As Null?
Code:
If rst.Fields("Reminder").Value <> Null Then
GoTo Label1
Else
MsgBox "Current Record is not set. Complete or Delete.", vbInformation, "Record Not Set"
Exit Sub
End If

I don't get this at all. Even when the rst.Fields("Reminder").Value is set to something (and the vb highlight shows this too), it skips to and runs through the else statement.

I originally wrote this bit of code the other way round, so it ran if rst.Fields("Reminder").Value DID = Null, but it still gave me the wrong result! Any help would be much appreciated.

How To Look For An Empty Or Null Field ?
Hi,

Id like to:
Connection.execute ("SELECT * FROM TABLE WHERE blabla = Null ").

Why doesnt it work ?? Its a DateField.

Any ideas ??

NULL Field In Datagrid
Hi,
I have a Datagrid that has been formated for currency,
also the database that it enters to is formated for currency, when I clear a cell in the datagrid then move on to another cell, the first cleared cell is still NULL is there a way of making sure the NULL cell gets 0.00 entered when NULL

Update Certain NULL Field
I am doing something on client server..

I only can add record.,
Only certain field is fill ("Time_In"), ("User_Nm"), ("Computer_ID")

But I cannot do the ("time_out") and ("consumed")

this program uses 2 winsock communication for sending data to update database in server.

the code is as follow..
-------Client.vbp----------------
Private Sub Form_Unload(Cancel As Integer)

Winsock1.SendData "disconnect||" & tmpMatrik & "||" & Winsock1.LocalHostName
Winsock1.Close

End Sub
------------------------

--------------Server.vbp-----------
Case "disconnect"
Matrik1 = vntArray(1)
Machine1 = vntArray(2)
date2 = Format(Now, "dd/mm/yyyy")


'----------I only want this protion to be update in database
timeout = Format(Now, "long time")
consume = timeout - timein
'------------------------------------------------------

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''
Set db = OpenDatabase(App.Path & "dbProject.mdb")
Set rs = db.OpenRecordset("Select * from TRANSACTION where Date = '" & date2 & "' and Matrik_No = '" & Matrik1 & "' and Machine_ID = '" & Machine1 & "'")

db.Execute ("Update TRANSACTION set Time_Out = #" & timeout & "#, Time_Consumed = #" & consume & "# where Date = #" & date1 & "# and Matrik_No ='" & Matrik1 & "' and Machine_Id ='" & Machine1 & "'")
rs.Close
db.Close
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''
End Select
-----------------------------------------


these only some part of my program.. I cannot give all because is very huge..

Oh, I use DAO method.

Is there anything wrong to SQL statement I wrote. I does not promt error message.
please help me...
I really working on this for a long time.. But my lecturer wants result.. not just see me doing...
Please help me check and tell me how I can fix it.

Thanks

Test If Field Is Null, Please Help!!
Hey All

I have an If/else loop, the first part of the loop, the IF, is not supposed to execute currently, the else is


vb Code:
If...........ElseIf .Fields("Note").Value = Null Then......End If


execution never seems to drop into this.
the field is null (or even when its not)

have tried = vbNullsting, <>"", etc no luck it will not execute
the db is MS access using ADO, field is text.

am checking the value straight after the loop in the debug window and it shows null.

any help appreciated

Null Value In Datetime Field
How to Insert null value in datetime field in access database through a ado recordset .

Rs.fields("dt")=NULL

error "Inavalid Use of null "


i am able to do this :

Rs.fields("dt")=EMPTY

But a time value goes in the field
and i want to Query for all records where dt is empty which is not giving correct results .

Any Suggestions

NULL Field Error
How can I read a NULL field from ACCESS into
a control in VB without getting Run Time
Error 94 (Invalid use of Null).

Example: Text1 = !Field1
When Field1 is 'empty' I get Run Time Error 94.

Pulling A Field That Is Null
I am trying to pull a set of records in a Access DB where all fields of column X are NULL. I am using an ADO and trying to use the following SQL to pull the recordset but it does not like this, Any suggestions for this statement please:

strQuery = "SELECT * FROM REPOINVENTORY WHERE DateTtlRcvD =" & vbnull

Null Field In Recordset
If rs1("fld1") contains Null then

    str1 = rs1("fld1")

causes an error message but

    str1 = str1 & rs1("fld1")

seems to work. Why?

Robert

Change Date Field To A Text Field Without The Slashes
I have a field called statement date which is a date type "08/21/2008".
I want to remove the "/" from that variable. Is there an easy way to do that?

Grouping By Date Where Date Field Is Date/time
I have an SQL7 database storing datetime data, and totaliser reading, new records are added every 5 minutes.

I have written the following sql to give me the the min and max totaliser reading for each day - which works fine - except that the resultant Date field is a text field not a date field so the order statement generates an incorrect listing if the data selected sarts in one month and ends in the next, eg.

select left(Date, 12) as Date, Min(Reading) as Day_Start, Max(Reading) as Day_End from Steam where tag_no = 2 and (Date <= '24-November-2005 00:00:00' and Date >= '22-October-2005 00:00:00') Group By left(Date, 12) order by Date asc

Is there another function I can use to extract just the date from the date/time field as a date data type?

I cannot alter the database as its structure is predetermined by the remote field device supplying the data.

Blank Date (null Date)
How do I create a blank Date ( / / ) to pass it to a field

Check Database Field For Null
I've opened the database as below: -
Dim dbMyDB As Database
Dim rsMyRS As Recordset

Set dbMyDB = OpenDatabase("D:VB CodeDatabaseHardware maintenance.mdb")
Set rsMyRS = dbMyDB.OpenRecordset("Hardware", dbOpenDynaset)


This is the bit of code I 'm having problems with: -

If rsMyRS.Fields("HardwareBOMNo") <> Null Then
MsgBox "Not Null"
End If

Instead of MsgBox "Not Null" will be code do do a search to check for duplicate records.

If the current field contains a Null value I don't want to search for duplicate but the code doesn't work. How do I check for a Null in a database field.

Null Value In OLE Object Type Field
I have an SQL server 2000 database with an OLE Object type field in it. The front end of the database was created (not by me) on foxpro. The table where that field is, already had a bunch of records (about 1200) with nothing in this specific field (null values) I did a mass append to the table with a DTS and left that field blank. In fact I did not even include it in my DTS.
When the users open the form for this table with one of my records they get an error message that says "there is nothing in that field". When they open one of the other records that were in the database before (where the field is also empty or null) they do not see the error.
I cannot check the application because is lock. What I want to know is how I can tell the database that this field is null. Any ideas?
Thanks

Null Values In Field With Max() Function
i am querying this:

SELECT MEMBER, DOCTOR, MIN(FROMDT) AS Expr1, MAX(TODT) AS Expr2
FROM CLAIMS
GROUP BY MEMBER, DOCTOR

sometimes i have a null value in the TODT field for members, but with max functions, only pulls records with a date there, how can i make it use <NULL> as the greatest value? Is this possible?

I searched around and found a function like: MAX(NVL(TODT)) but i get an error saying NVL not a valid function..... Anyone please help.

thanks in advance

Displaying A Field With Null Values
pls help me with my problem... this is my code


Code:
Data1.RecordSource _
= "SELECT * FROM AERO " _
& "Where [Alternate PN] ='" & Text1.Text & " '"
Data1.Refresh


Well simply put, that code displays the rows in an msflexgrid which corresponds to the value i typed in my textbox... and my problem is this... how can i display the rows if the value on the "Alternate PN" is null??

Crystal Reports - Null Field
Im using the crystal reports wizard to connect to an access database. Currently the report is returing everything in my access table.

I only want it to return items with a NULL value in my field called "DATE".

Therefore IF my field called "date" has a NULL value then i want to see these records.

Does anyone know how to do this within the crystal report wizard??

Recordset Field Defaulting To 'NULL'
Hope someone can help cos I am totally baffled on this.

A friend has written VBA code to create a recordset from a sybase sql anywhere version 8 database and populate excel 2000 cells with the records. What is happening is that one particular field is resetting itself to 'NULL'. When you highlight the field in debug mode you get the actual value (a reasonably long string containing line feeds, carriage returns and characters) displayed, then, when you move the cursor away and highlight again, the value has magically changed to 'NULL' ....
Is this a known issue ? If someone has seen this before or can help me sort it I'd appreciate it as it is doing my head in.
Thanks
Mick.

How To Insert NULL In Int And Datetime Field From VB
Hi all,

In VB I m executing a insert statement, What I want is that when on the form
the integer and a datetime field is not supplied, NULL should be stored in
the back end.

How should I code to send NULL in integer and datetime field in VB.

insert into t1(f1, f2, f3) values('NULL', ?, ? )



Regsrda,
MRL

Trying To Null A Field To An Underlying Access DB
I have the following code in the Object cmdCancelFollowUp and the Procedure Click. The RequireFollowUp, DateOfFollowUp, and TimeOfFollowUp are fields in the underlying 97 MS-Access database. datRequest is a data control on the main form in Visual Basic 6.0. When I run the program, it says that the DateOfFollowUp and TimeOfFollowUp fields cannot be 0 length.  I want to Null the fields and can't seem to figure it out.


If datRequest.EditMode = dbEditAdd Then
    datRequest.Recordset("RequireFollowUp").Value = No
    datRequest.Recordset("DateOfFollowUp").Value = ""
    datRequest.Recordset("TimeOfFollowUp").Value = ""
    datRequest.Recordset.Update
Else
    datRequest.Recordset.Edit
    datRequest.Recordset("RequireFollowUp").Value = No
    datRequest.Recordset("DateOfFollowUp").Value = ""
    datRequest.Recordset("TimeOfFollowUp").Value = ""
    datRequest.Recordset.Update
End If


Any help would be appreciated.
Thanks,
Richard

How To Find Null Value In ADO Recordset Field?
hiya,

how can you check for a null value in an ADO recordset field?

i have tried the folllowing

If Len(rsAccount("idDefaultShipAddress")) = 0 Then


thanks
js

How To Ignore Null When Searching Db Field? - Thanks
How to ignore null when searching db field? - thanks

Memo Field Returning As Null? URGENT
I have a Word VBA app that requires a large text entry (large paragraph) to be stored for future use. The text stores ok, however, when I pull the data, it returns a null value and I have no data. I changed the data type in Access to Text and that fixed the problem, but the limitations of the text data type aren't acceptable in my scenario.


Code:
Set objRec = objDB.Execute("SELECT LetterData.TextEntered FROM LetterData WHERE LetterData.Case='" & strCase & "'")


If objRec.BOF = True Or objRec.EOF = True Then
MsgBox "No information found for case number " & strCase & ".", vbOKOnly
+ vbCritical, "Error"
Set objRec = Nothing
objDB.Close
ElseIf objRec.RecordCount <> 0 Then
'Use previously entered text from user, if nothing in database, do nothing
If objRec("TextEntered") <> Null Then
UserText$ = objRec("TextEntered").GetChunk(65000)
Else
UserText$ = "Null set returned."
End If
objDB.Close
ElseIf objRec.RecordCount = Null Then
MsgBox "No information found for case number " & strCase & ".", vbOKOnly
+ vbInformation, "Empty"
Set objRec = Nothing
objDB.Close
End If

Ideas anyone?

How To Check NULL Value Of Field In Joined Table?
THe following print out 6 'this rec is null'.
I need only those record with Keywords.Name is NULL.
to delete those record from Codes_Keywords Table..

stmt = "SELECT Codes_Keywords.*, Keywords.* " & _
" FROM Codes_Keywords LEFT JOIN Keywords ON Codes_Keywords.KeywordID = Keywords.ID " & _
" ORDER BY Keywords.Name "

rec.Open stmt, conn, adOpenKeyset, adLockOptimistic
Debug.Print stmt
While Not rec.EOF
If IsNull(rec![Name]) Then
'Debug.Print rec![Name] & ": " & Asc(rec![Name])
Debug.Print "this rec is null"
End If
rec.MoveNext
Wend

To get Those records with Keyword.Name = NULL.
I tried write equivalent code for more efficiency.
I used following codes with New query adding
" WHERE Keywords.Name = NULL " & _ .
I expect it returns 6 records. But It returns ZERO record!!!
WHat is wrong???

stmt = "SELECT Codes_Keywords.*, Keywords.* " & _
" FROM Codes_Keywords LEFT JOIN Keywords ON Codes_Keywords.KeywordID = Keywords.ID " & _
" WHERE Keywords.Name = NULL " & _
" ORDER BY Keywords.Name "

rec.Open stmt, conn, adOpenKeyset, adLockOptimistic
Debug.Print stmt
While Not rec.EOF
If IsNull(rec![Name]) Then
'Debug.Print rec![Name] & ": " & Asc(rec![Name])
Debug.Print "this rec is null"
End If
rec.MoveNext
Wend

Thank you.

Return An Empty String From A Ado Field Containing A Null Value
Is there a way to return an empty string from a null ado field in Visual Basic 6? I use nz() in Access, looking for a similiar method.

Copyright 2005-08 www.BigResource.com, All rights reserved