Display a messagebox based on results of a query - MS Access

I have an Access database with a form to add records using a command button.  There are a number of textboxes on the form that require the user to enter data into.  One of the textboxes is used for entering a date.

I have a small table with a list of invalid dates.   When the user clicks the command button to attempt to add a record, I want the date value entered in the textbox to be checked against the table to see if the date entered is valid.  If the date exists in the table, it is an invalid date.

The basic query for that is as follows:

SELECT Count(*) AS Expr1
FROM MyDatesTable
WHERE (((MyDatesTable[invalid_date])=Forms![Form1]![txt_Date]));

When the above query is run, it returns a value of 1 (invalid date because the date selected exists in the table) or 0 (a valid date).

What I need is, rather than have a 1 or 0 returned, I want a messagebox to pop up indicating "invalid date selected" when the query result is 1 and no action when the value is 0.  So, I'm looking to have the query results that get returned to be replaced by a messagebox (for invalid dates) or nothing (when the user entered a valid date).

Right now, under the command button to add a record, I have the following to get the basic query to run:

DoCmd.OpenQuery "Valid_Date_query"

How do I modify it or what do I replace it with to get the action I'm looking for?  Thank you.
dbfromnewjerseyAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
You can use:

If CBool(DCount("*", "MyDatesTable", "[invalid_date] = #" & Format(Forms![Form1]![txt_Date].Value, "yyyy\/mm\/dd") & "#")) Then
     MsgBox "Date in use."
End If

Open in new window

/gustav
0
 
dbfromnewjerseyAuthor Commented:
Perfect. Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.