Moving from Access BE to SQL - Primary key stopping me from adding new records

[Micron]

Not sure, but I suspect IsNull isn't needed. I expect the IsDate function would return False with Null as well as any value that is not a date.
@Kev86; yes doing it as you describe is OK as long as you don't mind writing and maintaining basically the same code multiple times. If the tests are against different data types and I was going to use Minty's approach I'd might do this:

Public Function IsValid(var As Variant) As Boolean

IsValid = True
Select Case TypeName(var)
    Case "Date"
        If var < #1/1/2020# Or var > Now() Then IsValid = False
    
    Case "Double" 'add cases for "Integer", "Long", "Single"?
        If Len(var) <> 10 Or Left(var, 2) <> 85 Then IsValid = False
        
    'or as per my post is it "85" and not 85?
    Case "String"
        If Len(var) <> 10 Or Left(var, 2) <> "85" Then IsValid = False
        
    Case Else
        IsValid = False
End Select

Debug.Print IsValid

End Function

I'm undecided about that function's return type given that I'm not actually using it.

Notes
- if you're really passing the # character, you're passing a string. Not a good idea for comparing numbers - "1111" > "22" is False. Your intent may be correctly interpreted by Access but it may not be.
- be careful comparing dates with Now. If you are storing only dates, the field's time value defaults to 00:00:00 whether you see it or not. Looks OK here though.

[Kev86]

Thank you Micron, i have tested the code as a simply If statement in the BeforeUpdate event against a form and it seems to work. Like i said i am more used to writing code this way rather than in a function then calling upon it. Either way even if i build it as a function i still need to call upon it in each form for each date field for example. So i might as well just do that and copy and paste the code multiple times add date validation and validation to the SAP reference field

[Minty]

Thank you Micron, i have tested the code as a simply If statement in the BeforeUpdate event against a form and it seems to work. Like i said i am more used to writing code this way rather than in a function then calling upon it. Either way even if i build it as a function i still need to call upon it in each form for each date field for example. So i might as well just do that and copy and paste the code multiple times add date validation and validation to the SAP reference field

The real saving in the centralised function, is that if your SAP number format or valid dates need to alter you are only changing the code in the function once, not having to find and replace it dozens of times in every form you called it from.
That is planning for the future and defensive programming.

[Kev86]

Hi Minty, yes good idea i didn't really think of that benefit. I don't think it will be something I will change in future and my database is built on BeforeUpdate & AfterUpdate events rather than calling upon a function but if i ever build a database again in the future i will definitely take that into consideration.

You and Micron have been more than helpful and i appreciate you taking your time to answer my questions so thanks again for all your help.