pass in null/blank value in the date field or declare the field as string and co

I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@.option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@.mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please helpThe stored procedure will accept null if you define the parameter that way:

create procedure TESTPROCEDURE (@.TestDate datetime = NULL)
as
select @.TestDate
go

exec TESTPROCEDURE '1/1/2003'
go

exec TESTPROCEDURE
go

blindman|||I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.

Try the following:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I think the stored proc idea is better though, it's safer and better for your data integrity.

Dan|||Originally posted by danielacroft
I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.

Try the following:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I think the stored proc idea is better though, it's safer and better for your data integrity.

Dan

Hello Dan,

What I need is an empty string in the date field to pass in in the stored procedure. What is the vb code for that?

Thanks!|||The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:

.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I modified your existing code. I'm not 100% sure that this will work but it should.

Dan|||Originally posted by danielacroft
The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:

.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I modified your existing code. I'm not 100% sure that this will work but it should.

Dan

Thanks for replying so quickly.
I edited my code as you have it above.
I'm stilll having trouble getting the date displaying correctly. I need the year to display in four digits. It displays something '12/31/03'
This is code that I have

Function get_date(mfg_start_date as string,..)
mfg_start_date = Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)
mfg_start_date = CStr(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Year(mfg_start_date))

mfg_start_date is the textbox I need the date field to display but it will only eight digits of the year and place two empty strings after. I can't understand why. In the db design the mfg_start_date field is a char with length 10 as in the stored procedure.

Also there must be a better way to write the code that I have above.

Thank you again.|||The date format is normally determined by the locale settings ont he server when you're using VB. Can I ask why you're not using a date field in your database?

Dan