June 4, 2003 at 10:25 am
Good afternoon,
I think this has come up here recently but I can't locate it.
How do you handle an empty date field from an Access 2000 form passed to SQL 7 through ADO? SQL wants to put 1/1/01 in instead of blank or null. Do I need to do a CONVERT in the SQL statement executed by ADO?
Thanks!
June 4, 2003 at 10:53 am
I guess you want place '1/1/01' into a date column if user does not enter date from Access form. If it is, you can alter table to add default constraint to that column to allow '1/1/01' to be the default date.
June 4, 2003 at 11:18 am
Thank you Allen but I think I need to clarify. I want the SQL table to store NULL when Access passes an empty date field through ADO. As things are now, SQL is putting in 1/1/01. I don't want this.
Thank you
Jonathan
June 4, 2003 at 11:37 am
Try to alter table to allow "NULL' for that date column.
June 4, 2003 at 12:15 pm
Hi,
I think you will have to specify in ADO that NULL be inserted when an empty/blank value is passed...if a blank value is sent across then SQL Server inserts a date of 1900-01-01 00:00:00.000
you can check it out in the following :
Create table #T1(Val Datetime NULL)
Insert #T1(Val) Values (Getdate())
Insert #T1(Val) Values ('')
Insert #T1(Val) Values (Getdate())
Insert #T1(Val) Values (NULL)
Insert #T1(Val) Values (Getdate())
select * from #T1
Drop table #T1
June 4, 2003 at 12:23 pm
Use Access 2000 to create linked table to the table that has date column defined with allow 'NULL' and insert data from Access. You will see 'Null' value will be stored in the date column instead of '1900-01-01 00:00:00.000'
June 5, 2003 at 8:57 am
Thanks everyone for your suggestions.
In this case a linked table is not an option for me (it's a long story).
Is there an ADO connection property to set to get an NULL date across to SQL? I can't identify one.
I have tried the following in VBA:
if me.txtDate = "" then
strDate = "NULL"
end if
I then reference '" & strDate & "' in my SQL statement executed through ADO. It still arrives as 1/1/900.
Jonathan
June 5, 2003 at 9:32 am
will the following changes make a difference :
if Trim(me.txtDate) = "" then
strDate = "NULL"
else
strDate = " ' "&me.txtDate&" ' "
end if
and then reference "&strDate&" in the ADO - i.e without the single quotes...
June 5, 2003 at 10:35 am
OK... this worked!
I have a local Access staging table that I need to get to SQL Server. The ship date field may be left blank by the user in Access. I open a recordset from the local staging table and do the following:
Do While Not rst.EOF
If IsNull(rst.Fields("shipdate")) = True Then
strSQL = "INSERT INTO repair_line VALUES " & _
"('" & rst.Fields("co") & "', " & _
"'" & rst.Fields("line") & "', " & _
"'" & Replace(rst.Fields("item"), "'", "''") & "', " & _
"'" & rst.Fields("origshipdate") & "', " & _
"NULL, " & _
"'" & rst.Fields("quantity") & "', " & _
"'" & Replace(rst.Fields("itemcode"), "'", "''") & "', " & _
"'" & rst.Fields("warrenty") & "', " & _
"'" & rst.Fields("replace") & "', " & _
"'" & rst.Fields("code") & "', " & _
"'" & rst.Fields("onsite") & "', " & _
"CONVERT(MONEY,'" & rst.Fields("charge") & "'), " & _
"CONVERT(MONEY,'" & rst.Fields("cost") & "'), " & _
"'" & rst.Fields("note") & "', " & _
"DEFAULT);"
Else
strSQL = "INSERT INTO repair_line VALUES " & _
"('" & rst.Fields("co") & "', " & _
"'" & rst.Fields("line") & "', " & _
"'" & Replace(rst.Fields("item"), "'", "''") & "', " & _
"'" & rst.Fields("origshipdate") & "'), " & _
"'" & rst.Fields("shipdate") & "'), " & _
"'" & rst.Fields("quantity") & "', " & _
"'" & Replace(rst.Fields("itemcode"), "'", "''") & "', " & _
"'" & rst.Fields("warrenty") & "', " & _
"'" & rst.Fields("replace") & "', " & _
"'" & rst.Fields("code") & "', " & _
"'" & rst.Fields("onsite") & "', " & _
"CONVERT(MONEY,'" & rst.Fields("charge") & "'), " & _
"CONVERT(MONEY,'" & rst.Fields("cost") & "'), " & _
"'" & rst.Fields("note") & "', " & _
"DEFAULT);"
End If
cmd.CommandText = strSQL
cmd.Execute
rst.MoveNext
Loop
That seems to be the only way to get NULL in the field on SQL.
Thanks again!
Jonathan
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply