Comparing dates from date/time field

I need to be able to have my user enter two dates and have the query count the number of records which contain a date between the two entered dates.

The issue I am running into is that the date/time field is formatted MM/DD/YYYY HH:MM:SS AM or PM.

I've done a number of different things and I just can't seem to get this to work. Everything I try either comes back with 0 records found or an error saying that it's too complex to evaluate.

Anything to point me in the right direction?

Should be a simple Where Between situation.
The format of the datetime field shouldn't be a problem since it would appear to be your regional setting if your username is not misleading.

Is the field datatype really datetime or is it text?

You do need to be somewhat careful comparing dates.
Consider these comparisons in the Immediate pane ...
The dates are the same but this expression is false because of the time.
This expression is true, but the dates are the same ...
One solution to this issue is to use the DateValue() function, which removes the time, making this expression true ...
If a field in a table contains a time, you can remove this by querying that table as follows ...
Or in a where clause