如何在Ms访问中将空值传递给datetime数据类型 [英] how to pass null values into datetime datatype in Ms access

问题描述

我有更新查询，虽然我传递的是datetime值，我已经使用cDate将字符串转换为datetime，但问题是当我传递空值时它通过错误，如何在Ms access


这是我的代码

I have update query though which i am passing datetime value , i have use cDate to convert string to datetime , but problem is when i pass null values it throughs error , how to pass null values into datetime datatype in Ms access

this is my code

Dim iresult1 As Integer = UtilityClass.ProcessData("update PM11 SET PCODE='" & txtPcode.Text & "' ,PNAME='" & txtPartyName.Text & "',AUTMOB='" & txtAutMob.Text & "',ADD1='" & txtAdd1.Text & "',ADD2='" & txtAdd2.Text & "',ADD3='" & txtAdd3.Text & "',CITY='" & txtCity.Text & "',PHNO='" & txtPHNO.Text & "',PIN='" & txtPin.Text & "',PNAME1='" & txtPName1.Text & "',PNAME2='" & txtPName2.Text & "',QNAME1='" & txtQName1.Text & "',RADD1='" & txtRADD1.Text & "',RADD2='" & txtRADD2.Text & "',RCITY='" & TXTRCITY.Text & "',DOB= CDate(IIf(Isnull( '" & txtDOB.Text & "'),Null,'" & txtDOB.Text & "')),AREACODE='" & txtArea.Text & "',DOAN='" & txtAnvDate.Text & "',BLDGRP='" & txtBdgroup.Text & "',RDGNO='" & txtRdbNo.Text & "',MST='" & txtMST.Text & "',CST='" & txtCST.Text & "',LICEXDATE=CDate(IIf(Isnull('" & txtLicXdate.Text & "'),"null",'" & txtLicXdate.Text & "')),PMLDATE='" & txtFreePaidUptoDt.Text & "',DLICNO20='" & txtlicNo20.Text & "',DLICNO21='" & txtLicNo21.Text & "',DLICNO20C='" & txtLicNo20C.Text & "',DLICNO20B='" & txtLicNo20B.Text & "',DLICNO21B='" & txtLicN021B.Text & "',DLICNO20D='" & txtLicNo20D.Text & "',SCODE='" & type & "' ,email_id='" & Trim(txtemail.Text) & "' WHERE PCODE='" & txtPcode.Text & "'")

推荐答案

看看这里： Microsoft访问数据类型 [ ^ ]

Have a look here: Microsoft Access Data Types[^]

MSDN写道：

日期值必须根据ODBC规范日期格式分隔或由日期时间分隔符（＃）分隔。否则，Microsoft Access会将该值视为算术表达式，不会引发警告或错误。



例如，日期1996年3月5日必须是代表{d'1996-03-05'}或＃03/05/1996＃;否则，如果仅提交03/05/1993，Microsoft Access将评估为3除以5除以1996.此值向上舍入为整数0，并且由于零日映射到1899-12-31，这是使用的日期。

Date values must be either delimited according to the ODBC canonical date format or delimited by the datetime delimiter ("#"). Otherwise, Microsoft Access will treat the value as an arithmetic expression and will not raise a warning or error.

For example, the date "March 5, 1996" must be represented as {d '1996-03-05'} or #03/05/1996#; otherwise, if only 03/05/1993 is submitted, Microsoft Access will evaluate this as 3 divided by 5 divided by 1996. This value rounds up to the integer 0, and since the zero day maps to 1899-12-31, this is the date used.

Access使用双浮点值在内部存储日期时间值。双精度值0.0对应于日期12/31/1899。因此，如果它为空或无效，您可以检查日期值并传递此日期。您还可以传递字符串'Null'。然后Access将字段设置为上面的开始日期。

Access stores datetime values internally using a double floating point value. The double value 0.0 corresponds to the date 12/31/1899. So you can check your date value and pass this date if it is null or invalid. You can also pass the string 'Null'. Then Access sets the field to the above start date.

这篇关于如何在Ms访问中将空值传递给datetime数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！