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

 

[Dale_Fye via AccessMonster.com]

Little bit difficult to tell you given that we don't know what strWhere5 and
strWhere2 look like.

Personally, I would create a query,save it, and use the query name instead of
the select statement.

My next comment would be to offset references to the SetEffDate field on your
subform with the # sign, like:

" AND [TaxRatesBases].[TaxEffectiveDate]< #" & _
Forms![qryWorkWithTax subform]!SetEffDate & "# " & _

Then, it looks like your reference to the SetEffDate field may be wrong,
unless [qryWorkWithTax subform] is your main form. The proper syntax for
this should look like:

Forms!MainFormName!SubFormControlName.Form.SetEffDate

Then, I would add parenthesis where you think they are appropriate in the
criteria portion of the DCOUNT. I generally declare a variable (varCriteria),
and build that string up outside of the domain function, then I just refer to
it in the domain function. Something like:

Dim varCriteria as Variant

varCriteria = "[TaxRatesBases].[TaxEffectiveDate] < #" &
IF DCOUNT ("FieldName", "queryName", varCriteria) > 1 then
'do something
End IF

HTH
Dale

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

 

[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