Trying to do a DCOUNT to test whether or not there are overlapping dates
before allowing the user to create a new record....

If DCount("taxname", "SELECT TaxRatesBases.TaxID, TaxRatesBases.BusUnit, " & _
"TaxRatesBases.TaxName, Location.City, Location.State,
Location.County, " & _
"TaxRatesBases.TaxEffectiveDate, TaxRatesBases.TaxEndDate "
& _
"FROM Location INNER JOIN TaxRatesBases ON Location.BusUnit
= " & _
"TaxRatesBases.BusUnit;" ,""" & strWhere5 & strWhere2 & " & _
" AND [TaxRatesBases].[TaxEffectiveDate]< " &
Forms![qryWorkWithTax subform]!SetEffDate & " & _
" AND [TaxRatesBases].[TaxEndDate] Is Null OR " & strWhere5
& strWhere2 & " & _
" AND [TaxRatesBases].[TaxEffectiveDate]< " &
[Forms]![qryWorkWithTax subform]!SetEffDate & " & _
" And [TaxRatesBases].[TaxEndDate] > " &
Forms![qryWorkWithTax subform]!SetEffDate & """) > 1 Then

First, take a look at the documentation for the DCount function():

http://www.techonthenet.com/access/f...ain/dcount.php
http://office.microsoft.com/en-us/ac...288171033.aspx

DCount is used for simple counting tasks and you don't put a SELECT
statement in a DCount and you can't count over multiple fields. If you want
to perform a calculation over multiples fields, you'll have to build a full
SELECT statement and execute it with whatever plateform (DAO, ADO, etc.)
you're presently using.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"ThriftyFinanceGirl" <(E-Mail Removed)> wrote in
message news:C5486B59-1A71-47A9-A15E-(E-Mail Removed)...
> Trying to do a DCOUNT to test whether or not there are overlapping dates
> before allowing the user to create a new record....
>
> If DCount("taxname", "SELECT TaxRatesBases.TaxID, TaxRatesBases.BusUnit, "
> & _
> "TaxRatesBases.TaxName, Location.City, Location.State,
> Location.County, " & _
> "TaxRatesBases.TaxEffectiveDate, TaxRatesBases.TaxEndDate "
> & _
> "FROM Location INNER JOIN TaxRatesBases ON Location.BusUnit
> = " & _
> "TaxRatesBases.BusUnit;" ,""" & strWhere5 & strWhere2 & " &
> _
> " AND [TaxRatesBases].[TaxEffectiveDate]< " &
> Forms![qryWorkWithTax subform]!SetEffDate & " & _
> " AND [TaxRatesBases].[TaxEndDate] Is Null OR " & strWhere5
> & strWhere2 & " & _
> " AND [TaxRatesBases].[TaxEffectiveDate]< " &
> [Forms]![qryWorkWithTax subform]!SetEffDate & " & _
> " And [TaxRatesBases].[TaxEndDate] > " &
> Forms![qryWorkWithTax subform]!SetEffDate & """) > 1 Then