Date/Time Error

[gibli]

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.

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.

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.


Is there any solution for this? or is it a bug in Visual Basic 6.0

Use different queries, one with Date (analize input before to chose wich query to use) and in second one skip Date.
Vlad

[gibli]

Can you please be more explanatory on your solution?

[santulan]

Hi,
Suppose you table 'T' has A,B,C and D fields. D being date field.
I assume that INSERT triggrered on on event like click, dbl click .. what so ever. You should be storing proposed values of A, B, C and D in some text box, combo box, list etc. Ok.
This is the scnerio i am assuming for your system.
Before actually inserting the value please validate the values:
After that -
suppose text1 box holds the date value.
check it

if isdate(text1.text) then
mySQL = "INSERT into T (A,B,C,D) VALUES('" & text4.text & "','" & text3.text & "','" & text2.text & "',#" & text1.text & "#)"
' I assume the values in A, B, C are string. If they are numbers remove ' sign
else
mySQL = "INSERT into T (A,B,C) VALUES('" & text4.text & "','" & text3.text & "','" & text2.text & "')"
End if



After that you simply execute the query stored in
mySQL
Suppose your database variable is myDb then

myDB.execute mySQL




thats all.... This ensures all validation at client side itself. You can easily handle all validations.

Good luck

Santulan