[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Date problem...

Posted on 1999-12-27
40
Medium Priority
?
412 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:teesp
  • 14
  • 8
  • 7
  • +7
40 Comments
 

Author Comment

by:teesp
ID: 2309211
Adjusted points to 200
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2309341
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
 
LVL 1

Expert Comment

by:mikeTmike
ID: 2310150
use the delete command and just delete that index number.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:teesp
ID: 2311080
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
 

Expert Comment

by:tell
ID: 2322150
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
 

Author Comment

by:teesp
ID: 2322395
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
 

Author Comment

by:teesp
ID: 2322398
Please do tell me how to use the delete command and how to delete index number.
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2326097
> 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
 

Expert Comment

by:jackg
ID: 2376736
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
 
LVL 1

Expert Comment

by:sdbanks
ID: 2509053
In access there is an option called "allow zero length".  Is this set to yes.

0
 

Author Comment

by:teesp
ID: 2517510
for sdbanks

In access, for Date/Time Type don't have this option call "allow zero length".  :)
0
 

Expert Comment

by:jackg
ID: 2518079
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
 

Author Comment

by:teesp
ID: 2519213
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
 

Expert Comment

by:mpcapps
ID: 2559917
in the validate event do:

if text1="" then text1=vbnull
0
 

Author Comment

by:teesp
ID: 2560153
Adjusted points to 300
0
 

Author Comment

by:teesp
ID: 2560154
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
 

Expert Comment

by:mpcapps
ID: 2560281
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
 
LVL 9

Expert Comment

by:Ruchi
ID: 2567863
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
 

Author Comment

by:teesp
ID: 2568092
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
 

Expert Comment

by:jackg
ID: 2568103
teesp I'm just curious why you are using DAO in VB 6.0 rather than ADO?
0
 

Author Comment

by:teesp
ID: 2568155
to jackq

hmm...DAO is faster then ADO.
0
 

Expert Comment

by:jackg
ID: 2568235
teesp, that may be true but at least you would have more control over your data input.
0
 

Expert Comment

by:jackg
ID: 2568363
teesp, do you want to delete the whole record or just the date/time.
0
 
LVL 9

Expert Comment

by:Ruchi
ID: 2569584
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
 

Author Comment

by:teesp
ID: 2571374
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
 

Expert Comment

by:jackg
ID: 2572040
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
 

Author Comment

by:teesp
ID: 2575291
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
 
LVL 9

Expert Comment

by:Ruchi
ID: 2575454
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
 

Expert Comment

by:jackg
ID: 2575642
teesp, have you tried setting the default value in the access field to null?
0
 

Expert Comment

by:pratherc
ID: 2592987
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
 

Expert Comment

by:pratherc
ID: 2592997
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
 

Author Comment

by:teesp
ID: 2603114
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
 

Author Comment

by:teesp
ID: 2603119
Adjusted points to 400
0
 

Expert Comment

by:pratherc
ID: 2604652
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
 

Accepted Solution

by:
MrXMrY earned 800 total points
ID: 2607489
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
 
LVL 9

Expert Comment

by:Ruchi
ID: 2608069
"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
 
LVL 9

Expert Comment

by:Ruchi
ID: 2630772
Teesp - Where are you?
0
 
LVL 9

Expert Comment

by:Ruchi
ID: 2662947
Teesp..where are you?
0
 
LVL 9

Expert Comment

by:Ruchi
ID: 2668300
Teesp, any news here?
0
 
LVL 9

Expert Comment

by:Ruchi
ID: 2738014
Teesp, Are you there??  Did your problem get solved? If so, please accept an answer and grade it.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question