Hi, I need to be pointed in the right direction. I'm looking for a way
to dynamically change a table in a query. I have a table called
students and each fiscal year the student table will get rolled to a
new table student2004. Currently all the reports are based on queries
using the student table. I would like to give the user the option to
pick a report and then pick the fiscal i.e. 2004 and then have the
query table change dynamically to student2004. Thanks for you help in
advanced.
3  2487 
You need to change the SQL statement for the query. Code snippet
(requires reference to Microsoft DAO 3.6 Object Library):
Dim rDB As DAO.Database
Dim rQdf As DAO.QueryDef
Set rDB = CurrentDB()
Set rQdf = rDB.QueryDefs(" MyQuery")
rQdf.SQL = "SELECT * FROM student2004;"
rQdf.Close
That said, what you really have is a data normalization problem. I'll
let others in this group point the way to solving this for you.
-Ken
The best approach is to build a form that "prompts" the user.
Assuming that your report has a date field, then you just add a column to
the report query like:
MyYear:Year[DateField]
And, it is possible that you already have a year field (but, if you have a
date field..then you did not need the year field..did you!!).
Anyway, you then simply open the report using the where clause. Assuming
you have a text box on the prompt screen that gets the year from the user,
then you go:
strWhere = "MyYear = " & me.txtWhatYear
docmd.OpenRepro t "mycoolRepot",a cViewPreview,,s trWhere
By use the reports "where" clause, you get dynamic conditions for the
sql..but don't have to modify the sql!!
You can see bunch of screen shots of example report prompt screens here, and
they all use the above idea: http://www.attcanada.net/~kallal.msn.../ridesrpt.html
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada pl************* ****@msn.com http://www.members.shaw.ca/AlbertKallal
Thank you both, I think I have what I need to start the process. Again
thanks.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Sean C. |
last post by:
Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
the optimizer makes often seem flaky. But this last example really
floored me. I was hoping someone could explain why I get worse
response time when the optimizer uses two indexes, than when it uses
one. Some context:
|
by: Andante.in.Blue |
last post by:
I just browsed through some of my Access links when I came across the
Ten Commandments of Access
(http://www.mvps.org/access/tencommandments.htm). Some of the points
I heartily agree with (and wish that my predecessor had followed) but
-- alas -- being a relative beginner to Access, I can't see the
reasoning behind one of the points and the site does not provide any
rationale / explanation for its presence either:
2. Thou shalt never...
|
by: CSDunn |
last post by:
Hello,
I have a situation with MS Access 2000 in which I need to display report
data in spreadsheet orientation (much like a datasheet view for a form). If
you think of the report in terms of what a spreadsheet might show, the
column names will actually be dynamic, based on data from a SQL Server 2000
database. The row data will also come from the same database. So in this
case, I will have a main report and a subreport. I've already tried...
|
by: Sami |
last post by:
I hope someone will tell me how to do this without having to do any VB
as I know nothing in that area. I am a rank beginner in using Access.
I have created a database consisting of student athletes. I have now
learned how to join two different tables in a query so that I might
generate a report.
Specifically, student athletes at a community college are required to
graduate with an AA or AS degree. Consequently, various steps are in...
|
by: Colin McGuire |
last post by:
Hi, I have an report in Microsoft Access and it displays everything in
the table. One column called "DECISION" in the table has either 1,2,
or 3 in it. On my report it displays 1, 2, or 3. I want to appear in
the report is Yes, No, or Maybe. What do I need to do to change what
appears in the report/what term do I need to search out in Google?
Thank you
Colin
| | |
by: Jarrod |
last post by:
I have multiple reports in one database, on one form. I need to know
what reports were run when, and by Network User ID. How do I do that?
|
by: manning_news |
last post by:
I've got a report that's not sorting correctly. I build a SQL
statement and assign it to the recordsource in the Open event, sorting
the data the way the user chooses. The user can choose up to 3 sorts
using a form that opens before the report opens.
When the report previews, it ignores the users' sort request and always
sorts by the primary key even though ORDER BY is explicity set to
another field other than primary key.
If I copy...
|
by: Neil |
last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is
upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server
at the same time. We're moving to SQL Server 2005, and, since he already has
licenses for Office Pro 2002, he wants to upgrade to that.
I've been saying that we need to upgrade to Access 2003, not 2002, even if
Office is kept at 2002. We are also looking to do a fair amount of...
|
by: fperri |
last post by:
I have a pivot query that is populated with rates returned based on options that user selects on a form.
My issue is:
They can either select one lender to see on the report, or a combination of multiple lenders. The pivot table then has the lender's name, as a name of the column heading. The thing is....I need the report to print me the column name (as a heading) and all the values of all the records under that heading (or lender).
But...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
