Can someone tell me what's wrong with this SQL?

T

ThriftyFinanceGirl

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
 
S

Sylvain Lafontaine

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

http://www.techonthenet.com/access/functions/domain/dcount.php
http://office.microsoft.com/en-us/access/HA012288171033.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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top