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

[ThriftyFinanceGirl]

Trying to do a DCount via SQL....

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

 

[vanderghast]

Last time I checked, DCount required a saved query, I mean, it does not work
with an ad hoc query.

I have not checked your ad hoc query, one way to do its print is to
debug.Print the string, and paste it into the query designer, to see if it
produces the required records.

You can try to use, with an ad hoc query, something like:

If 1< CurrentProject.Connection.Execute("SELECT COUNT(*) FROM
....").Fields(0).Value Then
..
Else
...
End if


instead of

If 1< DCOUNT("*", "SELECT whatever FROM ... " ) Then
...
Else
..
End if





Vanderghast, Access MVP

 

[Douglas J. Steele]

You can't use DCount with a SQL statement. It only works with the name of a
table or the name of a query.

 

[ThriftyFinanceGirl]

Thanks guys, I got it to work like this....

If DCount("taxname", "qryCheckTaxDates", strWhere5 & strWhere2 & " Or " &
strWhere5 & strWhere2) > 1 Then
MsgBox "Cannot create this Tax, there are Active Taxes for
this Date!", , "S.T.A.N."

I had the query saved, but I needed to pass the strings to it, so this
worked well!

You can't use DCount with a SQL statement. It only works with the name of a
table or the name of a query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Trying to do a DCount via SQL....

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