Validate date in form based on data on a table (1 Viewer)

[Andyto]

I have this form to capture a date

I would like to validate the date entered in the form based on this table DateMaster, and before running a report that would came out as blank if there is no sales made on that entered date, thus having a validation that would advised "There are no Sales on that date" Thetable for a year is too long for a dropdown list

The table looks like this and it is linked to another with the salesmen, cities, etc linked by the order no which is unique.

OrderNo	HOOK	SENTDATE	ORDERDATE	Ordertype	YearWeek
535	1/7/1943BSRG	1/16/2023	1/7/2023	BSRG	1
536	1/7/1943BSSO	1/16/2023	1/7/2023	BSSO	1
537	1/14/1943BSRG	1/23/2023	1/12/2023	BSRG	2
538	1/14/1943BSSO	1/27/2023	1/14/2023	BSSO	2
540	1/21/1943BSRG	1/30/2023	1/18/2023	BSRG	3
541	1/21/1943BSSO	1/30/2023	1/21/2023	BSSO	3
542	1/28/1943BSRG	2/4/2023	1/28/2023	BSRG	4
543	1/28/1943BSSO	2/6/2023	1/28/2023	BSSO	4
545	2/4/1943BSRG	2/13/2023	2/3/2023	BSRG	5
546	2/4/1943BSSO	2/13/2023	2/4/2023	BSSO	5
548	2/11/1943BSRG	2/20/2023	2/11/2023	BSRG	6
549	2/11/1943BSSO	2/21/2023	2/11/2023	BSSO	6
551	2/18/1943BSSO	2/287/2023	2/18/2023	BSSO	7
553	2/25/1943BSRG	3/6/2023	2/25/2023	BSRG	8
554	2/25/1943BSSO	3/6/2023	2/26/2023	BSSO	8

Please help with the validation linking the date enterer in the EnterDate Form to DateMaster.Orderdate or DateMaster.SentDate but I have been getting several access error trying to use an expression builder was noit useful either.

 

[theDBguy]

Hi. Welcome to AWF!

Did you mean to post a screenshot of your form instead of your macro?

 

[Andyto]

The macro captures the data in the form. This is the form

 

[Gasman]

Captures it where?
The form is unbound.
Use a DCount() or DLookUp() to see if any records match the date entered. Then work from that.

 

[Pat Hartman]

Wouldn't it be simpler to just add a message to the report's On No Data event?

 

[Andyto]

Wouldn't it be simpler to just add a message to the report's On No Data event?

I had not thought about it and I have never used that option before. I gave it a try and it works. Thank you

 

[Andyto]

Captures it where?
The form is unbound.
Use a DCount() or DLookUp() to see if any records match the date entered. Then work from that.

Thank you. I will try that!

 

[Pat Hartman]

You're welcome