0

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)
Improve this question
4
  • 1
    The linked question in your post suggests using an adVarChar data type for your parameter - but your code is still using adDBTimeStamp. I'm not sure why you are saying you don't know where to change that in the code. It's exactly in the line you posted - changing adDBTimeStamp to adVarChar. How about trying that fix? Although I'm not sure if casting '' to date will actually work in your stored procedure ... Commented Aug 7, 2023 at 21:41
  • i tried nvarchar in database but i cant handle the conversion of nvarchar between access and sql server , always error because of windows date format , or access recordset , or sql server casting , if i convert to nvarchar i need to handle it every time when i select , update , insert , and all equations in queries which depend on difference between two dates. Commented Aug 7, 2023 at 22:39
  • Your last comment is very difficult to understand. It seems like you are over-thinking this. If you are passing a date string, then you are going to convert it to a date in your stored procedure. Everything should be fine in that case. Commented Aug 7, 2023 at 23:43
  • 1
    This is all pretty confusing but one very clear recommendation would be to only use date data types. Don't use character data types. Character data types are unecessary and just confuse the issue. Commented Aug 8, 2023 at 2:21

2 Answers 2

Reset to default
0

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

i think problem in ms.access side because i tried to execute stored procedure manually in sql server and i passed null date and every thing done well, so the problem still in access i cant pass null date to sql server
Have you tried any of the suggestions yet? There is one in the link you posted (to pass an advarchar instead of adDbTImeStamp (in which case you could pass either null, or an empty string, or a date string formatted so that sql server can work with it). There is another suggestion in another answer posted by nbk.
I'm always try to avoid usage of date as varchar , because it's really hard to handle between access and SQLServer` i tried but always show errors in access code
0

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))
Improve this answer

2 Comments

runtime error 94 invalid use of null , i think date fields not use Null concept
No that means that in your table definition you didn't allow null, but as you wanted there null I assumed that you field can accept null, so you need to alter the column

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.