CODEWITHSUNDEEP
sql-servervbams-accessms-access-2016
A.J
A.J

Reputation: 45

Pass null datetime value to SQL Server stored procedure in VBA Access

I use Access with SQL Server, in customer table (customer birth date is optional column), if user left it null while creating record by stored procedure date recorded as (30/12/1899) as shown in the screenshot:

after creating record birth date field

I found this solution, but I really didn't figure it out where exactly I need to change in code or database.

I already tried following code in access.vba to pass empty date

cmd.Parameters.Append cmd.CreateParameter("@p_custi_birth_date", adDBTimeStamp, adParamInput, , Format(CDate(Nz(!custi_birth_date, Empty)), "YYYY-MM-DD"))

This is the SQL statement in the stored procedure to record null if parameter empty

CAST(NULLIF(@p_custi_birth_date, '') AS DATE)

Upvotes: 0

Views: 163

Answers (2)

nbk
nbk

Reputation: 49403

In Access and SQL Server, you can test the variable with ISDATE.

For SQL Server, it can be:

IIF(NOT ISDATE(@p_custi_birth_date), NULL, CAST(@p_custi_birth_date AS DATE))

For Access

IIf(IsNull(!custi_birth_date),Null,CDate(!custi_birth_date))

Upvotes: 0

topsail
topsail

Reputation: 3130

I'm not so sure about the vba side - I'm going to assume you follow the advice in the link you posted, which is to make sure you pass the date as a string, and also that the string is a valid date string that sql server understands - which to my mind means using yyyy-mm-dd or yyyymmdd, not using mm/dd/yyyy or dd/mm/yyyy, though feel free to try these latter if you must.

But in the stored procedure I don't think you want to cast an empty string to a date (you will get a date like 1900-01-01 which is not what you want). So this might work better for your stored procedure (assuming a valid date string is passed in, or otherwise an empty string, or possibly null):

-- //@p_custi_birth_date is a date string such as '2000-12-31'
case
    when isnull(@p_custi_birth_date, '') = '' then null 
    else cast(@p_custi_birth_date as date)
end

Upvotes: 0

Related Questions