Update statement with Null Dates

[bwo3333]

I've been struggling with this for some time now. Have looked for threads specific to this issue but haven't been able to find the correct thread. My issue is that I am trying to update a date field. When I do the date field may have a date or may be a null. When I try to pass in a NULL date with no quotes, I get a syntax error. When I have single quotes in the statement and a null value is passed in, I get an invalid use of date. Any ideas? Thanks much.

Dim DENIEDDATE1 As Date

If (Not IsDate(rs.Fields("DENIED_DATE"))) Then
DENIEDDATE1 = Null
Else
DENIEDDATE1 = "'" & rs.Fields("DENIED_DATE") & "'"
End If

update table1 set table1.denieddate = " & denieddate1 & " 'get Update syntax error with this statement

update table1 set table1.denieddate = '" & denieddate1 & "' 'fails due to invalid use of null

 

[rzw0wr]

Show the full code not just parts of it please.

Dale

 

[bwo3333]

strSQL2 = "SELECT distinct CH.SEQ_CLAIM_ID, CD.LINE_NUMBER, mm.SUBSCRIBER_ID, " & _
"ch.line_of_business, CH.MEMBER_AGE, CH.MEMBER_GENDER, CH.PLAN_CODE, CH.SEQ_PROV_ID, " & _
"pm.SHORT_NAME, ps.PRIMARY_SPECIALTY, ps.SPECIALTY_TYPE, CH.Primary_Svc_Date, CH.CLAIM_THRU_DATE, CD.BILLED_AMT, " & _
"CD.ALLOWED_AMT, rc.REASON_CODE, CD.NOT_COVERED_REASON, CD.NOT_COVERED_AMT, ch.EVAL_AUDIT_STATUS, " & _
"CD.NET_AMT, CD.DETAIL_SVC_DATE, CH.PLACE_OF_SERVICE_1, CH.DIAGNOSIS_1, cl.PROVC_CPD_PCT_OF_BILLED, cl.PROVC_CPD_PCT_WITHHOLD, " & _
"CH.DIAGNOSIS_2, cd.max_units, CD.PROCEDURE_MODIFIER, CD.QUANTITY , CD.COPAYMENT_1_AMT, cl.PRULE_PRICE_RULE, cl.PRULE_DESCRIPTION, " & _
"CD.DEDUCTIBLE_AMT, CD.PROCEDURE_CODE, CD.MED_DEF_CODE, md.Description, md.SECURITY_CODE, CH.AUTH_NUMBER, cd.ADJUDICATION_METHOD, " & _
"cl.MEDEF_CODE, cl.MEDEF_SHORT_DESC, cl.MEDEF_ORDER, cl.MEDEF_SEARCH_SEQ, cl.PROVIDER_NPI, cl.BENEF_PROC_ORDER_ID, cl.BENEF_APPLY_PAT_LIAB_TO, cl.BENEF_COPAY_RESTRIC_PCT, cl.BENEF_PATLIAB_COMP, " & _
"cl.PROVC_CPD_CONTRACT_TYPE, cl.PROVC_CPD_PRICE_RULE_1, cl.PROVC_CPD_PRICE_RULE_2, cl.PROVC_CPD_PRICE_REGION, cl.PROVC_CPD_SERVICE_REGION, cl.PROVC_CPD_PCT_ALLOWED, cl.PROVC_CPD_PCT_OF_BILLED, cl.PROVC_CPD_PCT_WITHHOLD, cl.PROVC_CPD_PRICE_SCH, " & _
"cl.PROVC_CPD_PRICE_SCH, le.procedure_code, le.adjusted_Charges, le.adjusted_units, le.ADJUSTED_MODIFIER1, am.Auth_Type, am.SEQ_PROV_ID AM_PROV_ID, am.ADMIT_PRIMARY_DATE, am.DIAGNOSIS_1 am_DIAGNOSIS_1, am.DIAGNOSIS_2 am_DIAGNOSIS_2, am.SURG_PROCEDURE_1, am.SURG_PROCEDURE_2, am.PLACE_OF_SERVICE, am.OVERALL_STATUS, am.HOLD_REASON, am.HOLD_Date, am.DENIED_DATE, am.AUTHORIZED_DAYS_VIS, am.DENIED_DAYS_VIS " & _
"FROM DORIS.PROFSVC_CLAIM_HEADER ch, DORIS.PROFSVC_CLAIM_DETAIL cd, doris.claim_deny_reason dr, DORIS.REASON_CODE_MASTER rc, DORIS.Member_Master mm " & _
", DORIS.PROV_SPECIALTY ps, DORIS.MED_DEFN_CODE md, DORIS.PROV_MASTER pm, DORIS.clpra_master cl, DORIS.ICES_LINE_EDIT le, Doris.Auth_Master am " & _
"Where (CH.seq_claim_id = cd.seq_claim_id) " & _
"and (ch.SEQ_MEMB_ID = mm.SEQ_MEMB_ID) and (ch.Auth_Number = am.Auth_Number (+)) " & _
"and (cd.seq_claim_id = dr.seq_claim_id (+)) and (cd.line_number = dr.line_number (+)) " & _
"and cd.seq_claim_id = cl.seq_claim_id (+) and cd.line_number = cl.line_number (+) " & _
"and (cd.seq_claim_id = le.seq_claim_id (+)) " & _
"and (cd.line_number = le.line_number (+)) " & _
"and (dr.REASON_CODE = rc.REASON_CODE (+)) " & _
"and pm.SEQ_PROV_ID = ps.SEQ_PROV_ID " & _
"and ch.SEQ_PROV_ID = pm.SEQ_PROV_ID " & _
"and (cd.MED_DEF_CODE = md.MED_DEF_CODE (+)) " & _
"and CH.SEQ_CLAIM_ID='" & claimnbr & "'" & _
"and ps.PRIMARY_SPECIALTY= 'Y' " & _
"order by cd.line_number "

claimtype1 = "PROF"
Else
MsgBox "Claim type must be PROF, P, INST or I. Process stopped without execution."
GoTo Subexit
End If

' MsgBox claimtype1
Set conn = CreateObject("ADODB.connection")
If Environment = "HSDREPT" Then
dbName = "hsdrept"
ElseIf Environment = "BWAUAT3" Then
dbName = "bwauat3"
ElseIf Environment = "DIAMUAT2" Then
dbName = "DIAMUAT2"
ElseIf Environment = "DIAMUAT3" Then
dbName = "DIAMUAT3"
ElseIf Environment = "DIAMUPA1" Then
dbName = "DIAMUPA1"
ElseIf Environment = "DIAMUFTS" Then
dbName = "DIAMUFTS"
ElseIf Environment = "DIAMUSTS" Then
dbName = "DIAMUSTS"
ElseIf Environment = "DIAMUPA2" Then
dbName = "DIAMUPA2"
ElseIf Environment = "DIAMDLTS" Then
dbName = "DIAMDLTS"
ElseIf Environment = "DIAMUPA2" Then
dbName = "DIAMUPA2"
Else
MsgBox "Environment not setup. Contact system administrator."
GoTo Subexit
End If

dbUser = "boaks"
dbPass = "well85care"

conn.Open "Provider=OraOLEDB.Oracle;Data Source=" & dbName & ";User Id=" & dbUser & ";Password=" & dbPass & ";"

Set rs = conn.Execute(strSQL2)


MsgBox "Processing Claim: " & rs.Fields("SEQ_CLAIM_ID")

Do While Not rs.EOF

If DCount("[Test Detail ID]", "[00200 - TestDetailTbl]", "[Test Detail ID] = " & currdetailid & "") > 0 Then
'no issue. Records found.
Else
MsgBox "Test Detail Record could not be found. Process stopped"
GoTo Subexit:
End If
'***PREP for SQL CALLS *************************************
SEQ_CLAIM_ID1 = rs.Fields("SEQ_CLAIM_ID")
If IsNull(SEQ_CLAIM_ID1) Then
MsgBox "Claim number can not be blank"
GoTo Subexit
End If

lineid1 = Int(rs.Fields("Line_Number"))
If IsNull(lineid1) Then
MsgBox "Line number can not be blank. Enter line number then press Get Claim"
GoTo Subexit
End If

subscriberID1 = Int(rs.Fields("Subscriber_ID"))
lob1 = rs.Fields("line_of_business")
MemberAge1 = rs.Fields("Member_Age")
Gender1 = rs.Fields("Member_Gender")
PlanCode1 = rs.Fields("Plan_Code")
ProviderID1 = rs.Fields("SEQ_PROV_ID")
ProviderName1 = rs.Fields("Short_name")
ProviderSpecialty1 = rs.Fields("SPECIALTY_TYPE")
EvalAuditStatus1 = rs.Fields("EVAL_AUDIT_STATUS")

'MsgBox "Here"
Dim admitdate1 As Date
Dim ADMITPRIMARYDATE1 As Date
Dim DETAILSVCDATE1 As Date
Dim HoldDate1 As Date
Dim DENIEDDATE1 As Date

If ClaimType = "PROF" Or ClaimType = "P" Then
admitdate1 = rs.Fields("Primary_Svc_date")
LOS1 = rs.Fields("claim_THRU_DATE") - rs.Fields("DETAIL_SVC_DATE")
DETAILSVCDATE1 = rs.Fields("DETAIL_SVC_DATE")
THRUDOS1 = rs.Fields("claim_THRU_DATE")
FromDOS1 = rs.Fields("DETAIL_SVC_DATE")
Else
admitdate1 = rs.Fields("Primary_Svc_date")
LOS1 = rs.Fields("Detail_THRU_DATE") - rs.Fields("DETAIL_SVC_DATE")
DETAILSVCDATE1 = rs.Fields("DETAIL_SVC_DATE")
THRUDOS1 = rs.Fields("Detail_THRU_DATE")
FromDOS1 = rs.Fields("DETAIL_SVC_DATE")
End If

Billedamt1 = rs.Fields("BILLED_AMT")
Allowedamt1 = rs.Fields("ALLOWED_AMT")
REASONCODE1 = rs.Fields("REASON_CODE")
NOTCOVEREDREASON1 = rs.Fields("NOT_COVERED_REASON")
NOTCOVEREDAMT1 = rs.Fields("NOT_COVERED_AMT")
NetAmt1 = rs.Fields("NET_AMT")


POS1 = rs.Fields("PLACE_OF_SERVICE_1")
DIAGNOSIS1 = rs.Fields("DIAGNOSIS_1")
DIAGNOSIS2 = rs.Fields("DIAGNOSIS_2")

If ClaimType = "PROF" Or ClaimType = "P" Then
DRG1 = Null
Else
DRG1 = rs.Fields("DRG_CODE")
End If

Modifier1 = rs.Fields("PROCEDURE_MODIFIER")
QUANTITY1 = rs.Fields("QUANTITY")
COPAYMENT1 = rs.Fields("COPAYMENT_1_AMT")
Deductible1 = rs.Fields("DEDUCTIBLE_AMT")
ProcedureCode1 = rs.Fields("PROCEDURE_CODE")
MEDDEFCODE1 = rs.Fields("MED_DEF_CODE")
Description1 = rs.Fields("Description")
'SearchSquence1 = rs.Fields("SECURITY_CODE")
AUTHNUMBER1 = rs.Fields("AUTH_NUMBER")

If IsNull(AUTHNUMBER1) Then
AUTHNUMBER1 = 0
End If

AdjudicationMethod1 = rs.Fields("Adjudication_Method")

MMEDEFCODE1 = rs.Fields("MEDEF_CODE")
MEDEFSHORTDESC1 = rs.Fields("MEDEF_SHORT_DESC")
MEDEFOrder1 = rs.Fields("MEDEF_order")
MEDEFSEARCHSEQ1 = rs.Fields("MEDEF_SEARCH_SEQ")

ProviderNPI1 = rs.Fields("PROVIDER_NPI")

BENEFProcOrderId1 = rs.Fields("BENEF_PROC_ORDER_ID")
BENEFAPPLYPATLIABTO1 = rs.Fields("BENEF_APPLY_PAT_LIAB_TO")
BENEFCOPAYRESTRICPCT1 = rs.Fields("BENEF_COPAY_RESTRIC_PCT")
BENEFPATLIABCOMP1 = rs.Fields("BENEF_PATLIAB_COMP")

PROVCCPDCONTRACTTYPE1 = rs.Fields("PROVC_CPD_CONTRACT_TYPE")
PROVCCPDPRICERULE1 = rs.Fields("PROVC_CPD_PRICE_RULE_1")
PROVCCPDPRICERULE2 = rs.Fields("PROVC_CPD_PRICE_RULE_2")
PROVCCPDPRICEREGION1 = rs.Fields("PROVC_CPD_PRICE_REGION")
PROVCCPDSERVICEREGION1 = rs.Fields("PROVC_CPD_SERVICE_REGION")

PROVCCPDPCTALLOWED1 = rs.Fields("PROVC_CPD_PCT_ALLOWED")
If IsNull(PROVCCPDPCTALLOWED1) Or Nz(PROVCCPDPCTALLOWED1) Then
PROVCCPDPCTALLOWED1 = 0
End If


PRULEPRICERULE1 = rs.Fields("PRULE_PRICE_RULE")
PRULEDESCRIPTION1 = rs.Fields("PRULE_DESCRIPTION")

PROVCCPDPCTOFBILLED1 = rs.Fields("PROVC_CPD_PCT_OF_BILLED")
If IsNull(PROVCCPDPCTOFBILLED1) Or Nz(PROVCCPDPCTOFBILLED1) Then
PROVCCPDPCTOFBILLED1 = 0
End If

PROVCCPDPCTWITHHOLD1 = rs.Fields("PROVC_CPD_PCT_WITHHOLD")
If IsNull(PROVCCPDPCTWITHHOLD1) Or Nz(PROVCCPDPCTWITHHOLD1) Then
PROVCCPDPCTWITHHOLD1 = 0
End If

PROVCCPDPRICESCH1 = rs.Fields("PROVC_CPD_PRICE_SCH")

ProcedureCode2 = rs.Fields("procedure_code")

AdjustedCharges1 = rs.Fields("adjusted_Charges")
If IsNull(AdjustedCharges1) Then
AdjustedCharges1 = 0
End If

AdjustedUnits1 = rs.Fields("adjusted_units")
If IsNull(AdjustedUnits1) Then
AdjustedUnits1 = 0
End If

ADJUSTEDMODIFIER11 = rs.Fields("ADJUSTED_MODIFIER1")

'Auth fields
AuthType1 = rs.Fields("Auth_Type")
AMPROVID1 = rs.Fields("AM_PROV_ID")
ADMITPRIMARYDATE1 = rs.Fields("ADMIT_PRIMARY_DATE")
amDIAGNOSIS1 = rs.Fields("am_DIAGNOSIS_1")
amDIAGNOSIS2 = rs.Fields("am_DIAGNOSIS_2")
SURGPROCEDURE1 = rs.Fields("SURG_PROCEDURE_1")
SURGPROCEDURE2 = rs.Fields("SURG_PROCEDURE_2")
PLACEOFSERVICE1 = rs.Fields("PLACE_OF_SERVICE")
OVERALLSTATUS1 = rs.Fields("OVERALL_STATUS")
HOLDREASON1 = rs.Fields("HOLD_REASON")

HoldDate1 = rs.Fields("HOLD_Date")

'If (Not IsDate(rs.Fields("HOLD_Date"))) Then
' HoldDate1 = Null
'Else
' HoldDate1 = "'" & rs.Fields("HOLD_Date") & "'"
'End If
'MsgBox "here"

DENIEDDATE1 = rs.Fields("DENIED_DATE")
'If (Not IsDate(rs.Fields("DENIED_DATE"))) Then
' DENIEDDATE1 = Null
'Else
' DENIEDDATE1 = "'" & rs.Fields("DENIED_DATE") & "'"
'End If

'DENIEDDATE1 = rs.Fields("DENIED_DATE")
AUTHORIZEDDAYSVIS1 = rs.Fields("AUTHORIZED_DAYS_VIS")
If IsNull(AUTHORIZEDDAYSVIS1) Then
AUTHORIZEDDAYSVIS1 = 0
End If
DENIEDDAYSVIS1 = rs.Fields("DENIED_DAYS_VIS")

If IsNull(DENIEDDAYSVIS1) Then
DENIEDDAYSVIS1 = 0
End If


Set rst = New ADODB.Recordset
Set cnn = CurrentProject.Connection

'It's possible that someone added or deleted records at the Detail level. It could happen within the Access Database or within Xcelys.
'Therefore, need to see what is currently in the database. If it exists update it, otherwise Insert the new record.
'Also, claims are constantly re-used within one environment. It's important to look at the test detail id, test claim id and environment when updating.

strSQL = "SELECT [00100 - TestHeadertbl].[Test Header ID], [00200 - TestDetailTbl].[Test Detail ID], [00400 - LineDetailTbl].[Line ID], " & _
"[00100 - TestHeadertbl].Environment, [00200 - TestDetailTbl].[Test Claim ID] " & _
"FROM ([00100 - TestHeadertbl] INNER JOIN [00150 - TestScenarioTbl] ON [00100 - TestHeadertbl].[Test Header ID] = [00150 - TestScenarioTbl].[Test Header ID]) INNER JOIN ([00200 - TestDetailTbl] LEFT JOIN [00400 - LineDetailTbl] ON [00200 - TestDetailTbl].[Test Detail ID] = [00400 - LineDetailTbl].[Test Detail ID]) ON [00150 - TestScenarioTbl].[Scenario ID] = [00200 - TestDetailTbl].[Scenario ID] " & _
"WHERE [00200 - TestDetailTbl].[Test Detail ID]= " & currdetailid & " AND [00200 - TestDetailTbl].[Test Claim ID]= '" & SEQ_CLAIM_ID1 & "' and [00100 - TestHeadertbl].[environment]= '" & Environment & "' " & _
"Order by [00400 - LineDetailTbl].[Line ID];"


With rst
.Open strSQL, cnn, adOpenKeyset, adLockBatchOptimistic
TotalRecords = rst.RecordCount
FoundClaim = False
FoundLine = False

Do While Not rst.EOF 'These are the records currently in the database

Set rst2 = New ADODB.Recordset
Set cnn2 = CurrentProject.Connection

Set rst3 = New ADODB.Recordset
Set cnn3 = CurrentProject.Connection

testClaimID2 = ![Test Claim ID]
lineid2 = ![Line ID]
testdetailid2 = ![Test Detail ID]
environment2 = ![Environment]
' SEQ_CLAIM_ID2 = "'" & SEQ_CLAIM_ID1 & "'"

If environment2 = Environment And testdetailid2 = currdetailid Then 'don't do anything if the environment or the test detail id's are different is different
If testClaimID2 = CStr(SEQ_CLAIM_ID1) Then 'yes records are already in the database. Update current records

strSQL2 = "UPDATE [00200 - TestDetailTbl] SET " & _
"[00200 - TestDetailTbl].[Test Claim ID] = " & [SEQ_CLAIM_ID1] & ", [00200 - TestDetailTbl].[lob] = '" & [lob1] & "', [00200 - TestDetailTbl].[Member Age] = " & [MemberAge1] & ", [00200 - TestDetailTbl].[Gender] = '" & [Gender1] & "' " & _
", [00200 - TestDetailTbl].[Plan Code] = '" & [PlanCode1] & "', [00200 - TestDetailTbl].[ProviderID] = '" & [ProviderID1] & "' " & _
", [00200 - TestDetailTbl].[Provider Name] = '" & [ProviderName1] & "', [00200 - TestDetailTbl].[Provider Specialty] = '" & [ProviderSpecialty1] & "', [00200 - TestDetailTbl].[Eval_Audit_Status] = '" & [EvalAuditStatus1] & "' " & _
", [00200 - TestDetailTbl].[Admit Date] = '" & [admitdate1] & "', [00200 - TestDetailTbl].[Claim Type] = '" & [claimtype1] & "', [00200 - TestDetailTbl].[Auth ID] = " & [AUTHNUMBER1] & " " & _
", [00200 - TestDetailTbl].[Provider NPI] = '" & [ProviderNPI1] & "', [00200 - TestDetailTbl].[SubscriberID] = " & [subscriberID1] & ", [00200 - TestDetailTbl].[Length of Stay] = " & [LOS1] & " " & _
", [00200 - TestDetailTbl].[Auth Type] = '" & [AuthType1] & "', [00200 - TestDetailTbl].[Auth Provider ID] = '" & [AMPROVID1] & "' " & _
", [00200 - TestDetailTbl].[Auth Admit Date] = '" & [ADMITPRIMARYDATE1] & "', [00200 - TestDetailTbl].[Auth Diagnosis Code 1] = '" & [amDIAGNOSIS1] & "' " & _
", [00200 - TestDetailTbl].[Auth Diagnosis Code 2] = '" & [amDIAGNOSIS2] & "', [00200 - TestDetailTbl].[Auth Procedure Code 1] = '" & [SURGPROCEDURE1] & "' " & _
", [00200 - TestDetailTbl].[Auth Procedure Code 2] = '" & [SURGPROCEDURE2] & "', [00200 - TestDetailTbl].[Auth POS] = '" & [PLACEOFSERVICE1] & "' " & _
", [00200 - TestDetailTbl].[Auth Status] = '" & [OVERALLSTATUS1] & "', [00200 - TestDetailTbl].[Auth Hold Reason] = '" & [HOLDREASON1] & "' " & _
", [00200 - TestDetailTbl].[Authorized Days] = " & [AUTHORIZEDDAYSVIS1] & ", [00200 - TestDetailTbl].[Auth Denied Days] = " & [DENIEDDAYSVIS1] & " " & _
", [00200 - TestDetailTbl].[Auth Hold Date] = '" & [HoldDate1] & "', [00200 - TestDetailTbl].[Auth Denied Date] = '" & [DENIEDDATE1] & "' " & _
" WHERE [00200 - TestDetailTbl].[Test Detail ID] = " & [currdetailid] & " ;"


With rst2
.Open strSQL2, cnn2, adOpenKeyset, adLockBatchOptimistic
End With

FoundClaim = True

 

[spikepl]

Only variables of type Variant can hold a Null. If you try to assign Null to a variable of any other type you get the error that you actually got.

When pasting code, use the code tags: Advanced ->select code->press #

 

[bwo3333]

Thanks I'll give this a shot later today and let all know the results.

 

[bwo3333]

I changed the variable to a variant. Still experiencing the same problem. I believe the Null value is getting stored in the variable. The real problem is the fact the Null does not need the single quotes but when I do have a date, it needs the single quotes. If I take the single quotes out of the update SQL statement, I get error 2147217900 Syntax error in update statement. If I put the single quotes back into the Update statement and the value of the variable is a null, I get error -2147217913 type mismatch error.

 

[Cronk]

It is possible to store a Null in a date field, but...

Firstly, check the table design that the date field does not have Required set to yes, otherwise you can't insert a null.

Secondly, the syntax for the SQL should be
UPDATE Table SET DateField to Null WHERE ...

Check the exact contents of your sql string by putting a debug.print before you open the update recordset. If nothing obviously is wrong, try pasting the sql into a query design.

 

[spikepl]

If (Not IsDate(rs.Fields("DENIED_DATE"))) Then
    DENIEDDATE1 = Null
Else
    DENIEDDATE1 = "'" & rs.Fields("DENIED_DATE") & "'"
End If

Above cannot work, because you are coercing a date into a string and then assigning that string to a date variable. Drop the quotes altogether.

In Access dates are surrounded by ## with the exception of ADO SQL. I don't know how it serves it to Oracle, but have you tried the#, so :

update table1 set table1.denieddate = #" & denieddate1 & "#"

if you have a US locale, or lookup http://allenbrowne.com/ser-36.html if not.

 

[bwo3333]

The '#' was the key Spikepl. Thank you so much! Thanks to all those who tried to help.

For anyone that needs it here is the answer. Not sure yiou really need the if then statement....


Dim HoldDate1 As Date
Dim DENIEDDATE1 As Date

DENIEDDATE1 = rs.Fields("DENIED_DATE")
If (Not IsDate(rs.Fields("DENIED_DATE"))) Then
'DENIEDDATE1 = Null
Else
DENIEDDATE1 = "'" & rs.Fields("DENIED_DATE") & "'"
End If

Update table1 set
" [table1].[Auth Hold Date] = #" & [HoldDate1] & "#, [table1].[Auth Denied Date] = #" & [DENIEDDATE1] & "#" & _