Date problem...

I am using vb6 and ms-access for my database.
I used DAO to access data from ms-access, the problem is after i have entered the date and saved into my database then i realise that i do not need the date.
So, i just delete it from textbox and save it BUT the system not allow me to save the empty textbox after updated, it said "Run-time error '524': Data type conversion error."
I have set Required = No, Indexed = No for my database.
Any others way to delete it or set it to null?
P/s : This problem occur when i used data type = Date/Time
teespAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

teespAuthor Commented:
Adjusted points to 200
0
Jeremy_DCommented:
Try to set the field to Nothing in stead of to Null (or empty string). This helped me most of the time. Access doesn't seem to accept Null as a valid value for a lot of datatypes, although the problem here is probably a invalid conversion. I don't think you can convert an empty string (this is what an empty textbox returns) to a datetime value.

0
mikeTmikeCommented:
use the delete command and just delete that index number.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

teespAuthor Commented:
Ok...Thanks Expert

But the problem is how to set the field to nothing and how to use the delete command to delete that index number?

I have try TextBox.Text = "",
VB don't accept empty string for date but I can delete it using ms-access.

The difference for both is :
VB     : Date = ""
Access : Date = deleted

So, how to delete it?
0
tellCommented:
A simple workaround if you don't need the date for explicit date/time calculations:
Change the data type in your database form Date/Time to text.
0
teespAuthor Commented:
I have change Date/Time Type to Text..
and another problem occur..

I can't sort by date. it would display something like below :

01/01/1998
01/12/1999
02/06/1998
02/12/1998
03/04/1999
03/08/1998

Well..this is not I needed.
0
teespAuthor Commented:
Please do tell me how to use the delete command and how to delete index number.
0
Jeremy_DCommented:
> But the problem is how to set the field to nothing and how to use the delete command to delete that index number?

To set a field to nothing, you can set the Value property of the Field object to Nothing:
fldMyField.Value = Empty
(I'm not sure here, experiment with Nothing, Empty, Null and an empty string. Logic dictates it should be Null or Empty, but I seem to remember that they forgot logic for a moment when they made this).

If you execute the Delete method from the Recordset object, it will delete the CURRENT record, so you'll first have to select that one using MoveNext/Previous and Search commands. Note that this method will not empty a field, but delete the whole record:
MyRecordset.FindFirst "DateField = #01/01/1980#"
'Sorry, had a little MM Bug here ;-)
'Make sure you find the correct record,
' add extra criteria if needed
MyRecordset.Delete

To delete a record using an SQL query:
DELETE * FROM MyTable WHERE DateField = #01/01/1980#
{same command as above, make sure you only delete the one you want)

If you need more clear examples, then show us the part of code where the updates/deletes happen. That will make it easier to help you.
0
jackgCommented:
teesp, all databases are funny when it comes to dates(perhaps one day we will have synchronisaton), however, I know I have encountered and cured this problem at work so I will reply on monday(24th) with the solution.
0
sdbanksCommented:
In access there is an option called "allow zero length".  Is this set to yes.

0
teespAuthor Commented:
for sdbanks

In access, for Date/Time Type don't have this option call "allow zero length".  :)
0
jackgCommented:
teesp, set up a variable as a variant and then check if the text box is empty, if so then update the access field with the variable ie,


dim strnewdatetime as variant
dim strolddatetime as variant

strdatetime = IIf(IsNull(textbox.text), " ", textbox.text)

update <<table>> set <<field>> = strnewdatetime where <<field>> = strolddatetime
0
teespAuthor Commented:
jackq...thank for your answer..

I have try that before...but as I said I used DAO to access data from ms-access and all text box are pointed directly to the field.

By using the method you have told will increase program error in the end.

Anyway...thank you for answering my question.
0
mpcappsCommented:
in the validate event do:

if text1="" then text1=vbnull
0
teespAuthor Commented:
Adjusted points to 300
0
teespAuthor Commented:
to mpcapps

Err 1. Validate event run when "vbkeyreturn"

Err 2. vbnull = 1

To represent a date you can't assign a value 1 into it.

Thank you for your comment...   :)
0
mpcappsCommented:
oops on nuber 2, but as for number 1


Occurs before the focus shifts to a (second) control that has its CausesValidation property set to True.

above pasted from MSDN
0
RuchiCommented:
Use function

'Checks to see if the string passed is null, if so then return "" else return the passed string

Function snull(test As Variant) As String

If IsNull(test) Or Len(test) = 0 Then
snull = ""
Else
snull = test
End If
End Function

sField = snull(txtTextBox.text)
0
teespAuthor Commented:
for Ruchi

Can't do that cause I am using DAO to access database from MS-Access.
Once you have save the date then you can't erase the date but you can change the date.

Set TextBox = "" is not allow when second updating.
0
jackgCommented:
teesp I'm just curious why you are using DAO in VB 6.0 rather than ADO?
0
teespAuthor Commented:
to jackq

hmm...DAO is faster then ADO.
0
jackgCommented:
teesp, that may be true but at least you would have more control over your data input.
0
jackgCommented:
teesp, do you want to delete the whole record or just the date/time.
0
RuchiCommented:
http://www.ezvb.com/tips/ezdetail.asp?id=122


Here is another one..

"How do you avoid the "Invalid use of null" error when reading null values from a database?If you try to retrieve a null value (empty field) from a database, you will get the error: "Invalid use of Null". Here is one way to get around this problem: .."

TextBox.Text = MyTest.Fields("TestFld") & ""

or,

txt.Text = Iif(IsNUll(rs!MyField), "", rs!MyField)
0
teespAuthor Commented:
for jackg
I need to delete the date after updated. Not the whole record.

for Ruchi
I am not retrieving a null value from database, I just save a "" or empty string in a TextBox where the TextBox is pointed to a field and the field is Date/Time Type in Ms-Access.
0
jackgCommented:
teesp, can you give me a little more info, such as, are you using a databound grid,are you updating an existing record or creating a new one etc.
0
teespAuthor Commented:
jackq, i am updating an existing record and i am not using databound grid.

1. I'm using TextBox
2. TextBox.Field is pointed to database field.
3. Database field type is Date/Time.
4. Enter a date and update.
5. Erase the contain in the TextBox and then Update it again.
6. An error will occur ("Run-time error '524': Data type conversion error.")
0
RuchiCommented:
get this information from planet source code vb.

isnull(rstTest!Date) then     txtField.text = "" 
else
txtfield.text = Format$(rsttest!date,"dd/mm/yyyy")


Another way:
Text1.Text = Format$("", "dd/mm/yyyy")
0
jackgCommented:
teesp, have you tried setting the default value in the access field to null?
0
prathercCommented:
I have used the following code in Visual Basic 5.0 with few problems:

Private Sub Text2_LostFocus()
    Dim savefield As String
    If Text2.Text = "" Then
        savefield = Text2.DataField        ' save data field linkage
        Text2.DataField = ""                   ' disconnect text field from data bar
        Data1.Recordset.Edit                  ' perpare for update
        Data1.Recordset.Fields(1) = Null ' update the dataset field
        Data1.UpdateRecord                   ' update the recordset
        Text2.DataField = savefield         ' restore linkage for text box
    End If
End Sub

From my experience the text box cannot hold a true null value and as long as it is connected to the data bar the problem occurs.

Note the field number in your database will probably be different in the update field statement.
0
prathercCommented:
An additional note: the lostfocus event doesn't appear to be triggered when you attempt to move from record to record.  I usualy disable the data bar while the cursor is in this field to prevent errors and re-enable it later (lostfocus event, Update command button, etc)
0
teespAuthor Commented:
to pratherc

I am not having problem with null in text box.

The Date give me problem when I want to erase it from database.
Try this :-
1. Use Ms-Access to create database.
2. Set a field using Date/Time type.
3. Use DAO at VB, create a text box pointed to DAO and the field. This will get data from database when you scroll forward or backward.
4. Type a correct date in the field and save it.
5. Scroll to the updated record and try to erase the date. (Not the ENTIRE record)
6. The Problem (Date can not be erase)
0
teespAuthor Commented:
Adjusted points to 400
0
prathercCommented:
Yes, I understood the problem.

Try this:

Create a new project, add a textbox and a command button. (no data bar or other controls)

add the following line of text to the click event of the command button:

    If IsNull(Text1.Text) Then MsgBox "test"

Run the project and alter the data in the text box: delete it, change it do anything to it and then click on the command button.

Under VB5 I have not been able to make the message box appear.  This shows the textbox is never actually null. This would mean any textbox would never pass a null to the field of a bould recordset.

Under VB5 the information in HELP under the ISNULL function gives an example of testing a field being assigned to NULL, and "". It shows "" (empty string) tests false using the ISNULL function.

The code I submitted earlier was designed to temporarily break the linkage of the textbox to the data bar, then update the date field of the recordset directly, then reconnect the textbox back to the data bar. (in the case of my dummy dataset it was field #1)

For this I made the assumption you were using a data bar to access the database.
0
MrXMrYCommented:
hi;
teesp , try this :
  dim rs as recordset
   rs.edit
   rs!date = Nothing
   rs.update
 when ever you want to set varibl (any type) to empty
 use the object Nothing.
 i hope this will help you. :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RuchiCommented:
"PRB: Error When Assign DB Value to Var: Invalid Use of Null"

http://support.microsoft.com/support/kb/articles/Q147/6/51.asp

read the The IsNull() Function Method paragraph

I think this should fix your problem!
0
RuchiCommented:
Teesp - Where are you?
0
RuchiCommented:
Teesp..where are you?
0
RuchiCommented:
Teesp, any news here?
0
RuchiCommented:
Teesp, Are you there??  Did your problem get solved? If so, please accept an answer and grade it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.