I have a form that requires a start date and an end date as input for
the parameters then runs the form which open queries which are designed
to populate certain tables. As it stands now, i can only run the form
one month at a time, so i would enter the first of the month, say
01/01/2002 and the end of the month 01/31/2002 and it would run the
form with january data. If i enter a range of dates from say 01/01/2002
to 06/30/2002, it will only run the query based on the end date being
june 30th and it will ignore the start date. therefore the tables will
only be populated with june data, not january, february, etc. as
expressed by the parameters.
Therefore, what i need to do is create a loop function that will run
the form month by month until all the queries have been run and all the
tables have been populated. I have an idea how to do this, but i can't
seem to get the loop statement correct. it's still only running the
form for only one month based on the end date. is there anyone that can
point me in the right direction? thanks.
Here is pretty much the code that i have thus far:
Dim intDuration As Integer
Dim strInterval As String
intDuration = 1
strInterval = "m"
DoCmd.SetWarnin gs False
Do Until 'this is where i want it to loop the statements month by month
CalcCum "qryCalcCumYTDB M"
DoCmd.OpenQuery "qryYTDReturnsB M", acNormal, acEdit
CalcCum "qryCalcCum QBM"
DoCmd.OpenQuery "qryQReturnsBM" , acNormal, acEdit
CalcCum "qryCalcCum6mBM "
DoCmd.OpenQuery "qry6MReturnsBM ", acNormal, acEdit
CalcCum "qryCalcCum12mB M"
DoCmd.OpenQuery "qry12MReturnsB M", acNormal, acEdit
CalcCum "qryCalcCumCumB M"
DoCmd.OpenQuery "qryCumReturnsB M", acNormal, acEdit
CalcCum "qryCalcCum24mB M"
DoCmd.OpenQuery "qry24MReturnsB M", acNormal, acEdit
Loop
MsgBox "All Calculations Completed" 4 3320
I have created an expression which, to me, looks like it should work.
but if i enter the dates january to march, instead of running those 3
months thru the queries, it runs the same month (march) 3 times. here
is the code if anyone could give me some suggestions on what to fix.
thanks.
Dim StartDate As Date
Dim strInterval As String
Dim Number As Integer
strInterval = "m"
StartDate = Me.Form!txtFrom
EndDate = Me.Form!txtTo
Number = 1
Do While StartDate <= EndDate
CalcCum "qryCalcCumYTDB M"
DoCmd.OpenQuery "qryYTDReturnsB M", acNormal, acEdit
etc.,
StartDate = DateAdd(strInte rval, Number, StartDate)
Loop
MsgBox "All Calculations Completed"
Something's missing here... Does your query point to your form to grab
this value that you're updating? Of course, there's no way for it to
"see" this value, because it's in CODE and not in a control on your
form... you could create an unbound textbox on your form, slap the
value into it, and then maybe your code would work.
Do While StartDate <= EndDate
CalcCum "qryCalcCumYTDB M" <== is "CalcCum" a FUNCTION you wrote?
'--are you passing your textbox value to this? How?
DoCmd.OpenQuery "qryYTDReturnsB M", acNormal, acEdit
etc.,
StartDate = DateAdd(strInte rval, Number, StartDate)
Loop
if qryYTDReturnsBM has a parameter that points at your open form, then
this should work.
SELECT... MyTable!Price * Forms!MyOpenFor m!txtMyQty As MyExpression
FROM MyTable
WHERE MyTable!Invoice No=Forms!MyOpen Form!InvoiceNo
might work.
thanks for thee suggestion, but the query runs off of a table, not a
form (i don't know if this makes a difference). the value for january
should be picked up from the table and ran thru the queries, then the
value from february, then march, and so on. i figured there was no need
to create an unbound text box because the date parameters have already
been input and the return values (if that is the value you are
referring to entering) can't be input because there are many values for
each date due to the fact that there are many funds. any suggestions?
thanks.
the problems with the SELECT expression is I keep receiving an error
message "Compile Error; Expected:Case". any solutions to fix this in
the code? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: jdph40 |
last post by:
I have a form in Access 97 on which I have an unbound list box filled
with our employees names from tblEmployees. When you select an
employee's name and click a button, the following code is run and
another form opens with only that employee's data:
For Each varSelected In Me!EmpList.ItemsSelected
strSQL = strSQL & Me!EmpList.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = ". IN (" & Left(strSQL,
|
by: Dom Boyce |
last post by:
Hi
First up, I am using MS Access 2002.
I have a database which records analyst rating changes for a list of
companies on a daily basis. Unfortunately, the database has been set
up (by my predecessor, I hasten to add) so that each day it creates a
copy of the record for each company, changes the date to today's date,
and prompts the user for any changes of ratings on that day. The
resulting data table grows by approx 600 records per...
|
by: Edwinah63 |
last post by:
Hi guys,
i am not new to vb6, but a mere tyro in vb.net what i want to do is
the following
open transaction
build an ADODB command object using parameters
execute it
build another command
execute it
|
by: colincolehour |
last post by:
I am new to Python and am working on my first program. I am trying to
compare a date I found on a website to todays date. The problem I have
is the website only shows 3 letter month name and the date.
Example: Jun 15
How would I go about comparing that to a different date? The purpose of
my program is to load a webpage and see if the content on the front
page is fresh or stale as in older than a few days. Any help in the
right direction...
|
by: Siv |
last post by:
Hi,
I have a stored procedure that I want to execute and then wait in a loop
showing a timer whilst it completes and then carry on once I get
notification that it has completed. The main reason for this being to stop
the user thinking the application has frozen when in fact it is just waiting
for a long SP to complete. Another reason for doing it like this is that I
also have had a problem in the past where the SP takes longer than the...
| |
by: Matthew Warren |
last post by:
I have the following piece of code, taken from a bigger module, that
even as I was writing I _knew_ there were better ways of doing it, using
a parser or somesuch at least, but learning how wasn't as fun as coding
it... And yes alarm bells went off when I found myself typing eval(),
and I'm sure this is an 'unusual' use of for: else: . And I know it's
not very robust. As an ex cuse/planation, this is what happens when you
get an idea in...
|
by: Jay |
last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send
text messages to many, many employees via system.timer at a 5 second
interval. Basically, I look in a SQL table (queue) to determine who needs
to receive the text message then send the message to the address. Only
problem is, the employee may receive up to 4 of the same messages because
each thread gets the recors then sends the message. I need somehow to
prevent...
|
by: Bill |
last post by:
I have a 200 record database that includes a date/time field,
AnnivDate, for a wedding anniversary. AnnivDate has nulls and some
incorrect year data. I have been creating the Access database using
data imported from an Excel file and the person entering into Excel
only cared about the month and day portion and Excel added the current
year to the field value. I want to produce a report that shows
upcoming anniversaries using a parameter...
|
by: fniles |
last post by:
I am using .Net 2003 and querying a SQL Server 2000 database.
I read the database in a loop, but on every iteration I set the SQLCommand
to new, set it, execute it, dispose and set it to nothing.
Is there any way so that I do not have to do that ? In VB6, I can set a
recordset to new once, set it and execute it within the loop, and close it
after the loop.
Thanks.
Dim m_cmdSQLT As SqlClient.SqlCommand
Dim m_drSQLTop As...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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: 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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |