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

  • Thread starter Thread starter ThriftyFinanceGirl
  • Start date Start date
T

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
 
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
 
You can't use DCount with a SQL statement. It only works with the name of a
table or the name of a query.
 
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!

Douglas J. Steele said:
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!)


ThriftyFinanceGirl said:
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
Click to expand...
Click to expand...
 
Back
Top