How to INSERT INTO with Null/No Date 3

[waldemar]

So I have this nice little function to convert any Dates in SQL type dates:

Public Function makeSQLDate(dtmDate) As String
If IsDate(dtmDate) Then
makeSQLDate=Format(CDate(dtmDate),"\#yyyy\-mm\-dd\#", vbMonday, vbFirstFourDays)
Else
makeSQLDate = ""
End If
End Function

And I am trying to insert a new data entry into a table:

DoCmd.RunSQL "INSERT INTO [tblTable] (...[dtmDate]...) VALUES (...makeSQLDate(strDate)...);"

The problem is, [strDate] CAN BE NULL (the user did not enter anything in the form, which is ok, there MUST be the case where the field is left empty). But how do I INSERT INTO a Null Date???

Regards
waldemar

 

[zevw]

Why are you inserting null dates!!

Before you insert check the value if it isnull(<variable name>) and if it is true.

Do not insert it in the table.

 

[waldemar]

Well the user fills in a form with 5 or so dates, some of them can be left empty. I guess if theres no way in the insert query to tell access not to assign one of these null date fields I have to do it via the recordset object....?!

Regards
waldemar

 

[yamafopa]

Waldemar
I think I have the same problem as you. I run quite a number of DoCmd.RunSql commands. Many of these include inserts or updates that include dates. In most cases the business rules allow for no date to be entered in the source record. However when you get to running the command you can't insert null into the date field without an error. Also I don't seem to be able to set a variable (assuming it is of type Date) to null. Once you dim a variable of type Date there seems to automatically be a numberic date equivalent in the variable. Problem is I don't want anything to show in the date field after I have run the sql. I would like to opent the form that is bound to the new record and have the date field show empty. Date fields seems to be allowed to hold null values initially, however you can't append any value other than a date equivalent to the field. The only answer I have been able to find is to test the value of the date variable and, if it is null, alter the actual sql so as to not include an insert to the date field. This gets quite messy, however, if there are more than one date fields...
Am I on the right track? Do you (or anyone else) have any other ideas about how we/I might proceed???
Chris

 

[Quehay]

The problem is trying to insert (in the ELSE) a zero-length string as a date. Try making the strDate = NULL. You'll have to insert NULL without quotation wrappers (like a number).

 

[yamafopa]

In my case I do not require a variable. So yes I could set a string type variable to null, but I can not set a date type variable to null. My problem arises when I get a value that comes from a sql select in code or from an empty control on a form, then I try to move that value into a new table with a sql string in VBA. If the control has a null value I'm fried. Can't pass null without and error so I am required to put a substitute for null. The only way I can seem to do that is to give it a date or date equivalent number. This results in an erroneous value in the new record.
I don't see how I can remove the wrappers (although I use # rather than ', as it is a date), without changeing the whole sql statement - which is what I am trying to avoid.
I get the feeling that I am missing something obvious...
Chris

 

[scking]

I believe either of the following work. When you look at a null date in the debugger they are what you will see. This has worked successfully for me on an ACCESS database.

Const BAD_DATE As Date = #12:00:00 AM#
Global Const DATE_NULL As Date = #12/31/1899 8:00:00 AM# ----------------------
Steve King
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[Quehay]

If you need to have NULL where no value applies (if not then Steve has solved the problem) then build the SQL string
in one of two ways based on the test:

*The function isn't so involved that it really hurts to put it inline.
Also, the format function returns a string, so there's no reason to do CDate prior. I'm allowing for an implicit conversion in using Format with a date, but it's not bad practice here (IMHO).

(Sorry about layout--I'm not having much luck with line breaks and a 10 column edit window here)
[tt]

If len(dtmDate) Then

strDate = Format(dtmDate,#yyyy\-mm\-dd\#&quot

strSQL = &quot;INSERT INTO [tblTable] (coltext,colNum,colDate) &quot; & _
&quot;VALUES ('TextCol',34343, strDate);&quot;

Else

strSQL = &quot;INSERT INTO [tblTable] (...[dtmDate]...) VALUES &quot; & _ ('TextCol',34343, NULL);&quot;


End If

DoCmd.RunSQL strSQL [/tt]

 

[cmmrfrds]

If you work backwards from the resolved sql statement it may be easier to see what to enter. Here are 2 valid insert statements. As you can see the word null replaces a valid date when you want null in the field.

insert into yourtable (fld1,txt1,mymoney,mydate)
Values (9,'some text',44.55,'1/1/02')

insert into yourtable (fld1,txt1,mymoney,mydate)
Values (9,'some text',44.55,null)

Just make sure the sql will resolve to the word null instead of the date value. This can be done in an if statement.

 

[yamafopa]

Gentlemen
Thank you for all your help. In between all the other stuff I managed to take a few minutes to look at you advice and came up with this solution (which I like 'cause it enables me to test for null values in recordset fields while I am in the midst of appending the recordset values to other tables.

DoCmd.SetWarnings False

DoCmd.RunSQL &quot;INSERT INTO tblConfig (config_date, original_code) VALUES (&quot; _
& IIf((dtDate) = 0, &quot;'&quot; & Null & &quot;'&quot;, &quot;#&quot; & dtDate & &quot;#&quot & &quot;, 'TestingFinal');&quot;


DoCmd.RunSQL &quot;INSERT INTO tblConfig &quot; _
& &quot;(config_date, original_code) VALUES (&quot; _
& IIf((dtDate) = 0, &quot;'&quot; & Null & &quot;'&quot;, &quot;#&quot; & dtDate & &quot;#&quot _
& &quot;, 'Testing');&quot;

This code will fire the message (not really an error)

[Database Name] can't append all the records in the append query.

[Database Name] set 1 field(s) to Null due to conversion failure ...

This message can be suppressed and the procdure fires anyway. A little on the ugly side but it works

Thanks again, this had been bugging me for a while and just never found the time and advice to work it out.

Chris

 

[waldemar]

perfect....
thanks all