How to write this SQL in VBA....

[ThriftyFinanceGirl]

Having trouble with the "isnull" part....

"SELECT Location.BusUnit, Location.Description,
Location.APOCode, " & _
"TaxRatesBases.TaxPercentage, TaxRatesBases.TaxEffectiveDate,
TaxRatesBases.TaxEndDate, TaxRatesBases.ExcludefromRecon, " & _
"TaxRatesBases.TaxCollPeriod, TaxRatesBases.TaxPeriodQEnds,
TaxRatesBases.TaxID, Location.State, " & _
"Location.City, Location.County, Location.Active,
Location.CloseDate, TaxTypes.TaxType, TaxRatesBases.TaxName " & _
"FROM Location LEFT JOIN (TaxTypes RIGHT JOIN TaxRatesBases ON
TaxTypes.TaxTypeID = TaxRatesBases.TaxName) ON Location.BusUnit =
TaxRatesBases.BusUnit " & _
"" & strWhere & strWhere2 & " AND (" &
IsNull([Location].[CloseDate]) & ") ORDER BY Location.BusUnit;"


Any help?

 

[Douglas J. Steele]

"" & strWhere & strWhere2 & _
" AND [Location].[CloseDate] IS NULL" & _
" ORDER BY Location.BusUnit;"

 

[Jack Leach]

I believe SQL syntax is "IS NULL" rather than VBA's "IsNull"

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[John Spencer]

Perhaps the following is what you want

"SELECT Location.BusUnit, Location.Description, Location.APOCode, " & _
"TaxRatesBases.TaxPercentage, TaxRatesBases.TaxEffectiveDate, " & _
"TaxRatesBases.TaxEndDate, TaxRatesBases.ExcludefromRecon, " & _
"TaxRatesBases.TaxCollPeriod, TaxRatesBases.TaxPeriodQEnds, " & _
"TaxRatesBases.TaxID, Location.State, " & _
"Location.City, Location.County, Location.Active, " & _
"Location.CloseDate, TaxTypes.TaxType, TaxRatesBases.TaxName " & _
"FROM Location LEFT JOIN (TaxTypes RIGHT JOIN TaxRatesBases " & _
" ON TaxTypes.TaxTypeID = TaxRatesBases.TaxName)" & _+
" ON Location.BusUnit = TaxRatesBases.BusUnit " & _
" " & strWhere & " " & strWhere2 & " AND " &
" [Location].[CloseDate] Is Null " & _
" ORDER BY Location.BusUnit;"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Having trouble with the "isnull" part....

"SELECT Location.BusUnit, Location.Description,
Location.APOCode, " & _
"TaxRatesBases.TaxPercentage, TaxRatesBases.TaxEffectiveDate,
TaxRatesBases.TaxEndDate, TaxRatesBases.ExcludefromRecon, " & _
"TaxRatesBases.TaxCollPeriod, TaxRatesBases.TaxPeriodQEnds,
TaxRatesBases.TaxID, Location.State, " & _
"Location.City, Location.County, Location.Active,
Location.CloseDate, TaxTypes.TaxType, TaxRatesBases.TaxName " & _
"FROM Location LEFT JOIN (TaxTypes RIGHT JOIN TaxRatesBases ON
TaxTypes.TaxTypeID = TaxRatesBases.TaxName) ON Location.BusUnit =
TaxRatesBases.BusUnit " & _
"" & strWhere & strWhere2 & " AND (" &
IsNull([Location].[CloseDate]) & ") ORDER BY Location.BusUnit;"


Any help?

Not unless you tell us what you're trying to do with isnull... where are
strWhere and strWhere2 being defined, and what values are you assigning to
them?

Be aware that there are two different syntaxes possible here:

IS NULL

with a space is a valid SQL WHERE clause term, e.g.

WHERE [fieldname] IS NULL

IsNull()

with no space but with parentheses, is a valid VBA function returning True if
its argument is NULL. This is more common in VBA procedures but can be called
from a query:

WHERE IsNull([fieldname])

The IS NULL syntax is more efficient; the function is useful in other contexts
(such as as an argument to some *other* function).

 

[ThriftyFinanceGirl]

Thanks Guys! You are my Secret Source! LOL!

I believe SQL syntax is "IS NULL" rather than VBA's "IsNull"

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Having trouble with the "isnull" part....

"SELECT Location.BusUnit, Location.Description,
Location.APOCode, " & _
"TaxRatesBases.TaxPercentage, TaxRatesBases.TaxEffectiveDate,
TaxRatesBases.TaxEndDate, TaxRatesBases.ExcludefromRecon, " & _
"TaxRatesBases.TaxCollPeriod, TaxRatesBases.TaxPeriodQEnds,
TaxRatesBases.TaxID, Location.State, " & _
"Location.City, Location.County, Location.Active,
Location.CloseDate, TaxTypes.TaxType, TaxRatesBases.TaxName " & _
"FROM Location LEFT JOIN (TaxTypes RIGHT JOIN TaxRatesBases ON
TaxTypes.TaxTypeID = TaxRatesBases.TaxName) ON Location.BusUnit =
TaxRatesBases.BusUnit " & _
"" & strWhere & strWhere2 & " AND (" &
IsNull([Location].[CloseDate]) & ") ORDER BY Location.BusUnit;"


Any help?