How to write this SQL in VBA....

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

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?
 
"" & strWhere & strWhere2 & _
" AND [Location].[CloseDate] IS NULL" & _
" ORDER BY Location.BusUnit;"
 
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
 
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?
Click to expand...

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).
 
Thanks Guys! You are my Secret Source! LOL!

Jack Leach said:
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)



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