Type mismatch when setting Date variable from textbox

[Markb384]

Hi guys



I have a simple form with 8 controls where the user can input into data into a minumum of one up to all eight. Therefore, not every control is required to be filled in.

I set a bunch of variables based on the values in the controls for later use in some SQL code. The rest all work perfectly, apart from my Date of Birth component. This is a basic textbox but with the Short Date format and an input mask. I plan on adding data validation at some point as well, but I just want to get up and running with some VBA SQL code first.

The code is used to test if the textbox is neither null nor an empty string, and if it isn't then it pulls out the date entered into a Date type variable. It works fine when a date is entered. However, it bugs out due to "type mismatch" when the textbox is empty, hovering my cursor over the value indicates Me.TxtPatDoB = "" (I think).

'patient DoB should be entered full (will use data validation to ensure)    If Not IsNull(Me.TxtPatDoB) Or Me.TxtPatDoB <> "" Then
        dateOfBirth = Me.TxtPatDoB.Value
    End If

Is there a workaround or better solution?

Thank you

[pbaldy]

Your test is logically incorrect. You want And rather than Or. You could also use:

If Len(Me.txtPatDoB & vbNullString) > 0 Then

or IsDate().

[Markb384]

I was testing an OR because for my other textboxes, sometimes they are classed as either null (for instance on the Form load) or "" is the user enters a value and then deletes it. Therefore, I want to test whether it was one condition or the other. Surely using an AND would never be fulfilled, as it cannot be Null and an empty string at the same time?

Using IsDate still gave me the error, but the Len solution appears to work.

Thank you

[pbaldy]

You can use Or when you're testing with =. When you use Not or <>, the logic reverses. If my test is "if my name is not Bill OR my name is not Paul", I've passed the test because it's not Bill, and I used OR. I'd want "if my name is not Bill AND my name is not Paul".

[Markb384]

I typed out a massive post replying how I thought OR was giving me the same answer as AND, only to realise that you were in fact correct

Thank you

[pbaldy]

No problem. I'm always correct...except when I'm wrong.