Field Edit to Avoid No Entry is Not Working

[ed coleman]

I have attached a copy of part of my program where a user inputs a date to get the next 10 days of expected customer deliveries. I input a criterion that the date input must be in the future, ie > today. When I input an earlier date and hit the arrow on the Customer drop down box, I get the message that the date is incorrect and cursor goes to the date input field. So that part works.

However, if I leave the date input (text16) blank and hit the arrow on the Customer drop down box, I get the message "The expression is typed incorrectly," yada, yada,yada. It is the same message as when I try to run the query that sets up the combo box for the Customer. (Note: when the text16 field is left blank it is equal to " ".)

Not really sure what I am doing wrong. Any assistance would be most appreciated.

 

[plog]

I think this has to do with Null values.

var X
var Y=""

X and Y are not equivalent. X is null, Y is an empty string. I believe Me.Text16 has nulls, which means it passes your validation so it goes to your query where it blows it up because the query can't handle Nulls.

Test for Nulls.

 

[plog]

Actually, test for dates.

Instead of trying to verify all the things it shouldn't be. Just make sure it is the thing it should be:

https://www.techonthenet.com/access/functions/advanced/isdate.php

 

[ed coleman]

Thanks for your response.


I looked at this, and if you look about half way down my attachment, I did a Immediate screen for Null and "" and it appears to me text16="" not null.


Did I miss something?



Ed

 

[ed coleman]

Did the Isdate() thing and it showed up as false whether it was empty or filled with a date. Text16 is an unbound text box formatted as a short date.


ed

 

[plog]

I'm not entirely familiar with Events and where you should put things. I don't know if Text16.BeforeUpdate is the right place.
I assume you have a button the user clicks to perform the action. Why not put all validation on that buton's click event?

Are you even sure its the BeforeUpdate event blowing things up? It might be the Enter End Date field. Is it doing a DateAdd on a null Start Date?

 

[ed coleman]

Thanks plog.



The form works properly if I enter a date. e.g. if it is today or prior to today, it brings back a message and puts the cursor back at text16, if it is in the future, it gives the drop down box with the list of customers that match the date input.



On most other forms I have developed, I have a command button to edit for criteria, such as blank date, date outside of range, etc., and it worked well. On this form, the intent was to enter the date, do the edits for the criteria and then bring up a list of customers that suit the critetian, if not blank, or go back to text16 if blank. Perhaps I could add another step and input a command box to hopefully circumvent the error. But I still can't discern why the code doesn't work.