Need response ASAP! Data type conversion error

[gibli]

Our requirement is: User should be able to enter the no value in the datetime field in our case

it is time in the format HH:MM hours:minutes and still the database should store that value as

blank and no other value not even as 0:00.

In MS-Access 97 SR-2 we have table 'T' with datetime datatype field 'F'. We have a query 'Q'

defined on this table to insert record into this table 'T'. We are executing this query 'Q' from

Visual Basic 6.0 and our application requirement is such that user may sometimes not enter any

value in the date field and still the insert query should work.


Here is my query in MS-Access:

Select query:
--------------
SELECT reptime
FROM table1
WHERE id=8;

Insert query:
-------------
PARAMETERS preptime DateTime;
INSERT INTO table1 ( reptime )
SELECT preptime;

Here is my Table definition:

Name:table1

Fields Datatype
id Autonumber
reptime date/time (short time format)


Here is the code from Visual Basic which gives error as "Data type conversion error":

Private Sub Command1_Click()
Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset
Set db = OpenDatabase("d:\personal\ira.mdb")
Set rs = db.OpenRecordset("table1")
Set qd = db.QueryDefs("insert")

With qd
.Parameters("Preptime") = vbNull
.Execute
End With

Set qd = db.QueryDefs("select")
Set rs = db.OpenRecordset("select")
'rs!RepTime = MaskEdBox1.Text
Text1.Text = rs!RepTime
'rs.Update

End Sub


Problem: When we are exceuting this query using QueryDef from VB application we are getting "data

type conversion error" message. When we did little investigation we found that, when VB is trying

to send some empty string to this date field in MS-Access Database doesn't accept any empty

string.


Here are the options I have tried?

1. When I tried to pass a Null value to the date field in the database when there is no value

entered in this date form variable. MS-Access table is storing that date value as 0:00 which is

no good because user treats it as valid time value.
2. When I tried to execute the query inside the MS-access by passing no value to this date value

it was accepting and the date value is stored as blank but same is not true when I am trying to

sent the blank date value from VB.

Is there any solution for this?