You should only have a single table, and it should include a year column.
(NOTE: Don't name the column Year, as that's a reserved word: you'll need to
use something like ItemYear) You would include the year as a parameter of
your query.
However, in the event that you can't correct your application, the sample
code you presented won't work if you've got references set to both ADO and
DAO. I would have expected an error message, mind you, but assuming that
you've correctly added a reference to DAO 3.6 (Microsoft DAO 3.6 Object
Library), did you also remove the reference to ADO (Microsoft ActiveX Dato
Object 2.x Library)?
When you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. For example, to ensure that you get a DAO recordset,
you'll need to use Dim rst as DAO.Recordset (to guarantee an ADO recordset,
you'd use Dim rst As ADODB.Recordset)
The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset
Looking at the sample SQL you posted, I have a number of suggestions.
Refer to the fields in your query using ., not !.
Don't use names like from, to, first and next as field names: they're
reserved words, and you'll run into problems using them. If you have no
choice, use square brackets around their names in the query.
Access SQL uses JOINs to connect tables.
See whether the following works:
SELECT TOP 1 q_txable_s.person_id, q_txable_s.asessment_year,
[2002a].[from],
[2002a].[to], [2002a].[first], [2002a].[next], [2002a].tax, [2002a].[rate]
FROM 2002a INNER JOIN q_txable_s
ON ([2002a].[from])<[q_txable_s].[Taxable_s]) AND
([q_txable_s].[Taxable_s]<=[2002a].[to])
--
Doug Steele, Microsoft Access MVP
(No private e-mails, please)
Alp Bekisoglu said:
Hi Doug,
Was out for two days, so couldn't respond.
1- Yes, there will be tables named 2003,2004,2005,... that contain the basis
data for calculations.
2- The name 2002a is just due to a trial of a different structure. The
actual table is named 2002.
3- The q_taxable_s is a query that extracts an amount from the DB
4- The SQL I provided then locates this value within the relevant table
This is where I need the code since each year the data can be different in
this table and hence there will be the tables as mentioned in "1" above. I
have no problem in defining the name via code. All I need (or want) to do is
rather than having one query per year, define the query in code so have a
one-shot solution by changing items of the query via variables.
I made some modifications to the following code I found just to try, but
ended up Access 2002 going haywire and getting stuck with the only escape
being to "End (a not responding) Program".
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
Set dbs = CurrentDb()
strSQL = "SELECT * FROM [Switchboard Items]"
strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
Me![SwitchboardID]
strSQL = strSQL & " ORDER BY [ItemNumber];"
Set rst = dbs.OpenRecordset(strSQL)
I'm hopeful I'll find the way... some day.
Alp
Douglas J. Steele said:
A table named [2002a] certainly sounds as though you've got different tables
for different periods. That's seldom (if ever) a good idea. I suspect that
2002a should be a field in your table, in which case you wouldn't need to
change the table name in the query: you'd just need to pass an additional
criteria to it.
--
Doug Steele, Microsoft Access MVP
(No private e-mails, please)
Alp Bekisoglu said:
Thanks Doug,
I got my SQL's via the code you've suggested. Learned one more!
What I'm planning to do is to re-write some of my queries in VBA as modules
and would like to find out enough information on the syntax (I've seen some
alien looking ones, at least to me) to save both the group's time & my
headache.
VBA help hasn't been too much of use due to lack of a bit more complex
examples, i.e. selecting many fields, including conditions, etc... The
one
I
found doesn't tell me much....
Public Sub DoSQL()
Dim SQL As String
SQL = "UPDATE Employees " & _
"SET Employees.Title = 'Regional Sales Manager' " & _
"WHERE Employees.Title = 'Sales Manager'"
DoCmd.RunSQL SQL
End Sub
I need to "correctly" code things like this (that is if such is advisable)
which is one of the very simple ones:
SELECT TOP 1 q_txable_s.person_id, q_txable_s.asessment_year, [2002a].from,
[2002a].to, [2002a].first, [2002a].next, [2002a].tax, [2002a].rate
FROM 2002a, q_txable_s
WHERE ((([2002a].from)<[q_txable_s]![Taxable_s]) AND
([q_txable_s]![Taxable_s]<=[2002a]![to]));
Fon instance the 2002a (a table) will be altered to a variable that is
to
be
determined dynamically on call. This is one reason why I want to
convert
my
queries into code.
Thanks again.
Alp
Assuming you've set a reference to DAO, try something like:
Dim dbCurr As Database
Dim qdfCurr As QueryDef
Set dbCurr = CurrentDb()
For Each qdfCurr In dbCurr.QueryDefs
Debug.Print qdfCurr.Name & ": " & qdfCurr.SQL
Next qdfCurr
Set dbCurr = Nothing
--
Doug Steele, Microsoft Access MVP
(No private e-mails, please)
Thanks for the advice but somehow my copy of Access seems to hate
DAO
an
d
other things since I am unable to see any details on such items! Wizard
lists the topics but clicking on any leads no where!
Any other alternatives in your mind? Usually Microsoft's any help/FAQ
is
next to useless or nonsense anyway, but that's beyond the point.
Sincerely,
Alp
P.S.: No offences to any of the valuable MS people, please. I'm
just
fed
up
with answers like "Have you turned your PC on?" kind of answers while
trying
to get something out of things like the "TroubleShooter"!
I normally use the QueryDef Object of the DAO Library to access the
Query
by
code.
Check Access VB Help on the QueryDefs Collection and QueryDef Object.
--
HTH
Van T. Dinh
MVP (Access)
Is there a place I can refer to/check to gain info on query via
code?
(i.e.
define/create, run, alter, ect...)
Thanks in advance,
Alp
