Data Type Conversion Error

[graviz]

I have some fields on a form that get updated into a table. If I don't have a value in one of the date fields and I run this function, I receive the error "Data Type Conversion Error". The table this information is getting dumped into has fields formatted as a date. I used the Nz function to help somewhat but now I'm getting this error and am stuck. Any ideas?

Private Sub CMD_UPDATE_Click()
Dim CDB As Object
Dim CRS As Object
Dim STR_Prop_Name As String
Dim STR_Prop_Status As String


Dim STR_TSS_D As String
Dim STR_DD_R_D As String
Dim STR_DD_C_D As String
Dim STR_BOM_Submit_D As String
Dim STR_Contract_D As String
Dim STR_Inst_S_D As String
Dim STR_Inst_E_D As String
Dim MySQL As String
STR_Prop_Name = Forms!Frm_Update!LBL_Prop_Name.Caption
STR_Prop_Status = Forms!Frm_Update!CBO_Prop_Status.Value
STR_TSS_D = Nz(Forms!Frm_Update!TXT_TSS_COMP_D.Value)
STR_DD_R_D = Nz(Forms!Frm_Update!TXT_DD_R_D.Value)
STR_DD_C_D = Nz(Forms!Frm_Update!TXT_DD_C_D.Value)
STR_BOM_Submit_D = Nz(Forms!Frm_Update!TXT_BOM_Submit_D.Value)
STR_Contract_D = Nz(Forms!Frm_Update!TXT_Contract_D.Value)
STR_Inst_S_D = Nz(Forms!Frm_Update!TXT_Inst_S_D.Value)
STR_Inst_E_D = Nz(Forms!Frm_Update!TXT_Inst_E_D.Value)
Set CDB = CurrentDb
MySQL = "SELECT * FROM Master_Table WHERE Prop_Name='" & [Forms]![Frm_Update]![LBL_Prop_Name].Caption & "'"
Set CRS = CDB.OpenRecordset(MySQL)
CRS.Edit
CRS("Prop_Name").Value = STR_Prop_Name
CRS("Prop_Status").Value = STR_Prop_Status
CRS("TSS_Completion_Date").Value = STR_TSS_D
CRS("DD_Rcv_Prop_Specs").Value = STR_DD_R_D
CRS("DD_Design_Completed").Value = STR_DD_C_D
CRS("BOM_Submitted_To_Sales").Value = STR_BOM_Submit_D
CRS("Contract_Date").Value = STR_Contract_D
CRS("Install_Start_Date").Value = STR_Inst_S_D
CRS("Install_Completion_Date").Value = STR_Inst_E_D
CRS.Update
Set CRS = Nothing
Set CDB = Nothing
End Sub

[Robeen]

A.
This:

STR_TSS_D = Nz(Forms!Frm_Update!TXT_TSS_COMP_D.Value)

. . . looks like it might be an issue because the Nz() function has TWO parameters;
1. The field/value you are testing for Null [which you have in there]
2. The value to substitute the Null WITH [which you do not have].
If you do not provide the second parameter [value_if_null] - Access will provide a zero-length-string.
If you are trying to put that zero-length-string into your date field you could get that error.

I'm thinking you should try something like this:

STR_TSS_D = Nz(Forms!Frm_Update!TXT_TSS_COMP_D.Value, Value_To_Use_If_TXT_TSS_COMP_D_Is_Null)

B.
In your table design - is the Date field 'Required'?
If it is, you not only MUST provide a value, but since the field is formatted as 'Date' - you have to provide a valid Date.
In some test environments that I have worked in, the development team has decided on a date to use for test records. It has always been a date that any User would know was not a possible valid date - for instance 11/11/1111.
Idon't know what dates your table contains, but see if this gives you something to start with.

Let us know if you still have problems.

[graviz]

A.
This:

. . . looks like it might be an issue because the Nz() function has TWO parameters;
1. The field/value you are testing for Null [which you have in there]
2. The value to substitute the Null WITH [which you do not have].
If you do not provide the second parameter [value_if_null] - Access will provide a zero-length-string.
If you are trying to put that zero-length-string into your date field you could get that error.

I'm thinking you should try something like this:

STR_TSS_D = Nz(Forms!Frm_Update!TXT_TSS_COMP_D.Value, Value_To_Use_If_TXT_TSS_COMP_D_Is_Null)

B.
In your table design - is the Date field 'Required'?
If it is, you not only MUST provide a value, but since the field is formatted as 'Date' - you have to provide a valid Date.
In some test environments that I have worked in, the development team has decided on a date to use for test records. It has always been a date that any User would know was not a possible valid date - for instance 11/11/1111.
Idon't know what dates your table contains, but see if this gives you something to start with.

Let us know if you still have problems.

The dates wouldn't be required to the user to enter. Is there any way to not have it enter anything for the fields they leave blank? I know I could do the 11/11/1111 but I would prefer to have it blank or null until they update it if possible. Can this be done?

[Missinglinq]

...If you do not provide the second parameter [value_if_null] - Access will provide a zero-length-string.

Actually, if you don't provide the second parameter, Access will provide a Value appropriate for the DataType of the Field in question; a ZLS for a Text Field, a Zero for a Number Field, and a Date/Time Value value for a Date/Time Field.

Linq ;0)>

[graviz]

Actually, if you don't provide the second parameter, Access will provide a Value appropriate for the DataType of the Field in question; a ZLS for a Text Field, a Zero for a Number Field, and a Date/Time Value value for a Date/Time Field.

Linq ;0)>

I thought that was the case as well but I'm still receiving an error when I don't provide that second parameter.

[Missinglinq]

This

Dim CRS As Object

doesn't look correct, given its use, shouldn't it be

Dim CRS As DAO.Recordset

And are you really setting the Value of STR_Prop_Name to the Caption of a Label on the Form? Seems a bit odd, but odd things sometime work, of course!


You might also check for Missing References, especially the DAO Library for your Access version.

Linq ;0)>

[graviz]

This

Dim CRS As Object

doesn't look correct, given its use, shouldn't it be

Dim CRS As DAO.Recordset

And are you really setting the Value of STR_Prop_Name to the Caption of a Label on the Form? Seems a bit odd, but odd things sometime work, of course!


You might also check for Missing References, especially the DAO Library for your Access version.

Linq ;0)>

The caption is correct. It's only to show the name of a property and it's populated from a search form before this one. I don't want the user to change it (i.e. textbox) so I made it into a lable.

I don't think the declaring is the issue. If I use the "11/11/1111" as Robeen states above it works fine. The problem is I really don't want to have that dummy value in the table.

If I go into the actual table an do a paste append (and don't include all date fields) it works perfectly. This is why I would think something like this should be possible.

[Robeen]

Actually, if you don't provide the second parameter, Access will provide a Value appropriate for the DataType of the Field in question; a ZLS for a Text Field, a Zero for a Number Field, and a Date/Time Value value for a Date/Time Field.

Linq ;0)>

I was going by information on these two sites:
1.
http://office.microsoft.com/en-us/ac...001228890.aspx

If you use the Nz function in an expression in a query without using the valueifnull argument, the results will be a zero-length string in the fields that contain null values.

http://www.techonthenet.com/access/f...dvanced/nz.php

value_if_null is optional. It is the value to use when the variant is a null value. If this parameter is omitted and the variant is a null value, the Nz function will return a zero or a zero-length string.

. . . but I haven't tested . . . so there may be something in what you say.
What do you think the Nz() function will put into a Date field if the 'value-if-null' is ommited - an actual date?