Trend Line Function.
Jun 12, 2007Hi all,
I would like to have a function in an access query showing the slope of a trendline (the linear one in excel).
Do you know how to create it?
Fuga.
ADVERTISEMENT
Modules & VBA :: Insert Into Function - How To Carry Over To New Line
Oct 24, 2013I am using "INSERT INTO tablename(a1,a2)" function but i have to many "a" objecjts. How to carry over to a new line without having error?
View 10 Replies View RelatedQueries :: Function To Output Line Numbers In A Query - YTD
Apr 18, 2014Is there a built in function which can be used to create line numbers in a query?
I've written a query to calculate year to date (YTD) points for yachts in a series of races and sorted it in descending order - so yacht 1 is coming first, yacht 2 is coming second etc. I'm looking for a way to add sequential numbering (starting at one and increasing by one for each line) into the query to represent their YTD places. Or this this something that should be left to the report which uses the query?
Modules & VBA :: Loop Function Repeating First Line Of Recordset
Aug 17, 2015managed to get some code up and running but when trying to enhance it I have hit a rut.
I have a function that is looking up a query called Optimisation - Auto Optimise with operational data in it. I only need 2 key fields; consolcode and volume. There are 106 records with different consolcodes each with different volume. e.g consolcode: Chittagong to Rotterdam201452 (Chittagong to Rotterdam by year "2014" by week "52") and volume 161 (cbm)
I then run a code that allocates the volume into specific sea freight containers and returns the values into a different output table.
The allocation code works fine but when I run the loop function for the recordset rsttradelane it runs for the correct amount of records (106) but always returns the first record of Chittagong to Rotterdam201452 and 161 cbm and not the other 105 consolcodes with the different cbm. Giving me an output table with Chittagong to Rotterdam201452 and 161 cbm repeated 105 times!!
Code:
Set rsttradelane = dbsEPIC.OpenRecordset("Optimisation - Auto Optimise")
consollane = rsttradelane!consolcode
ConsolVol = rsttradelane!Volume
Do Until rsttradelane.EOF
'Optimisation code' then
Code:
rsttradelane.MoveNext
Loop
rsttradelane.Close
How do I ensure that each consolcode and its associated cbm is recognised individually and flushed through the optimisation code?
12 Month Trend
Oct 30, 2007I have made a query that I want to show all records with the the current month and all 11 months earlyer. This is the Criteria that I used. Is my syntax correct?
Between Month(Date()) And Month(Date()-11)
I put it in a field called Month([Date])
Thanks
Anthony
Queries :: Average Call Rate - Determine If Trend Is Up Or Down
Jun 28, 2013I have a query that displays the average call rate on various pieces of equipment over the last 12 months. I was wondering if there is a way to have Access look at this query and determine if the call rates are trending up or down over that time frame. Kind of like a trend line would do on a chart in Excel, only I want this to show me the word Up or Down in my results. Is this possible?
Here is the query I am using:
Code:
TRANSFORM FormatNumber(Avg([All Call Rate]),2) AS AvgCallRate
SELECT List_With_TNC_tbl.Device, List_With_TNC_tbl.Model, List_With_TNC_tbl.[Item Num]
FROM List_With_TNC_tbl
GROUP BY List_With_TNC_tbl.Device, List_With_TNC_tbl.Model, List_With_TNC_tbl.[Item Num]
PIVOT Format([Month Start Date],"mmm") In ("Jan","Feb","Mar","Apr","May", "Jun", "Jul","Aug","Sep", "Oct", "Nov", "Dec");
Queries :: Complicated Query (nested Subqueries) For A Trend Graph
Apr 17, 2014Some essential background first. I have a Balances table which records balances by date. I also have an Issues table where problems are logged. There is a one-to-many relationship between Balances and Issues (i.e. each Balance can have multiple Issues). I also have a Comments table where updates for each Issue are recorded. There is again a one-to-many relationship between Issues and Comments (i.e. each Issue can have multiple Comments)
There are two key date fields in the Issues table :FlagDate (the date an Issue was flagged by a user for investigation)
ResolveDate (the date said investigation was brought to a conclusion)
There is also a date field in the Comments table :UpdatedWhen (the date any given comment was added)
So the basic flow is that an Issue gets flagged (FlagDate), then various comments are added (multiple UpdatedWhen's) and finally the Issue gets resolved (ResolveDate)
I need to incorporate a trend graph which will show the counts ofNew (i.e. new issues flagged as of each day) Cleared (i.e. issues resolved each day)
Updated (i.e. issues not yet resolved but updated each day)
Unchanged (i.e. issues not yet resolved and not updated each day)
Outstanding (i.e. all unresolved issues as of each day)
This is the SQL I've put together to get that table of information on which to base my chart :
Code:
SELECT [tblBalances].[BalanceDate] AS AsOfDate,
(SELECT COUNT([tblIssues].[IssueID])
FROM [tblIssues]
WHERE [tblIssues].[Flag] = True
AND [tblIssues].[FlagDate] = [tblBalances].[BalanceDate]) AS New,
[Code] .....
The subqueries for 'New', 'Cleared' and 'Outstanding' work perfectly; the resultant dataset gives me one record for each date in the Balance table and correctly counts the number of issues falling into each of those buckets.
The problem I have is with the 'Updated' bucket. If a flagged issue happens to be updated twice on the same day (which is perfectly acceptable), it counts this twice as well. I don't want this as I just want to know how many issues were updated on any given day - not how many updates there were.
I tried using COUNT(DISTINCT) in the 'Updated' subquery but it gives me a syntax error - on further research, I don't think it's possible to use the DISTINCT keyword in a COUNT subquery (at least not easily)
I also tried grouping by IssueID within that 'Updated' subquery but it still gives me the duplicate count within the same IssueID (and returns nulls rather than zeroes for those days where no updates occured)
I think I need to add a subquery within the subquery () to only return the latest comment as of the date in question - something along the lines of :
Code:
(SELECT TOP 1 [tblComments].[UpdatedWhen]
FROM [tblComments]
WHERE [tblComments].[IssueID] = [tblIssues].[IssueID]
AND DateValue([tblComments].[UpdatedWhen]) <= [tblBalances].[BalanceDate]
ORDER BY [tblComments].[UpdatedWhen] DESC) AS UpdatedWhen
But how to do this, nor if it is even feasible in Access to begin with.
Queries :: Remove GROUP BY Line And Stick Semicolon At End Of Previous Line
Jun 25, 2013Here's the statement
Code:
sqlfinal = "SELECT Employees.ID, Employees.Name "
sqlfinal = sqlfinal & "FROM ((qryDeptVBA INNER JOIN qrySkillVBA ON qryDeptVBA.ID = qrySkillVBA.ID) "
sqlfinal = sqlfinal & "INNER JOIN Employees ON qryDeptVBA.ID = Employees.ID) "
sqlfinal = sqlfinal & "INNER JOIN qryAreaVBA ON Employees.ID = qryAreaVBA.EmpID "
sqlfinal = sqlfinal & "GROUP BY Employees.ID, Employees.Name;"
If i simply remove the GROUP BY line and stick the semicolon at the end of the previous line (.EmpID; ) it works just fine. How is adding a group by line causing an error?I tried adding another parenthes at the beginning ((( and ending the joins as EmpID); and that failed with the exact same error.
Forms :: How To Get Values From Line Items Form Inserted Into QUOTE LINE
Jun 5, 2014I have a main form with 3 sub forms. The main form is tied to a table called QUOTES_MASTER. The first sub form is tied to a table called QUOTE_ LINE_ ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.
The second sub form adds up total of all line items and is not tied to a table.The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table. how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.
I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.I tried this code on the product total sub form but it doesn't do anything and there are no errors:
Private Sub PROD_SUB_AfterUpdate()
DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID
Modules & VBA :: Read CSV Transaction File Line By Line And Add Correct Transactions To Access Table
Nov 29, 2014I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG)
Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer
Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String
Dim Lim As String, ITNO As Integer
[code]....
Modules & VBA :: How To Import Text File Line By Line
Nov 18, 2014I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code:
Dim strLine As String
Dim intLineNum As Integer
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Open "C:TestTest.txt" For Input As #1
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1
Line 2 is placed in Row 2 Column 1
Line 3 is placed in Row 3 Column 1
.
.
.
Line X is placed in Row X Column 1.
Forms :: No Line Only Marker In Line Graph
Oct 18, 2014I can change multiple things on a line graph with VBA.
Me.Graph47.chartType = GraphType ' take 65 for line
with me.graph47
.SeriesCollection(1).border.Color = vbblue ' change the line color
.SeriesCollection(1).border.Weight = LineWeight ' change the line weight to for example 3
.SeriesCollection(2).MarkerSize = MarkerWeight ' Change the marker weight, for example 4
.SeriesCollection(2).MarkerBackgroundColor = vbblue ' Change the marker color,
.SeriesCollection(2).axisgroup = 2 ' put this series on the secondary axis
end with
SeriesColection(1) is line with markers. This is correct.But now I want the seriescollection(2) without line, so only the markers. I cheched the MSDN site from Microsoft. The Excel trick with the macro does not work for me.how to hide the line with VBA for only SeriesCollection(2) in Access?
Modules & VBA :: Creating A Function That Counts Records And Use That Function In A Query
Dec 11, 2013So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies View RelatedForms :: SUM Function Produces Error From Calculated Function
Jan 30, 2014I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error
Date Function/need Time Function
Jun 9, 2005We have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields.
160037
213137
224356
235716
235800
12341
21708
22732
Here is the date function we use:
Function f2Date(strDateOld As String)
Dim strDate As String, strMonth As String, strYear As String
strMonth = Mid(strDateOld, 5, 2)
strDate = Right(strDateOld, 2)
strYear = Left(strDateOld, 4)
f2Date = strMonth & "-" & strDate & "-" & strYear
f2Date = CDate(f2Date)
f2Date = Format(f2Date, "mmmm d yyyy")
End Function
Now Function To Convert To Date Function
May 25, 2006Hi all,
I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.
Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.
I tried to go back and change the NOW to DATE in VB, but the code does not run.
How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?
Thanks in advance.
SQL Line (Not Like)
Jun 20, 2005I have the following code to display what choice I make on a drop down box:
Private Sub cboMajorLocation_AfterUpdate()
Select Case Me.cboMajorLocation.Value
Case 1
Me.lstPC.RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like '*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;"
Case 2
Me.lstPC.RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'FTM*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;"
Case 3
Me.lstPC.RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'CS*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;"
Case 4
Me![lstPC].RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'PQL*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;"
Case 5
Me![lstPC].RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'Savage*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;"
Case 6
Me![lstPC].RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'Retail*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;"
End Select
End Sub
What I need to know is how to make a statement that you can put in where you want it to display all but take out certain parts of it. Example: On "Case 3" it displays everything starting with "CS*" I want it to not display certain items like "Retail*", & "FTM*" & "PQL*" & "Savage*"
Any help for this statement would be great thanks
End Of The Line
Jun 29, 2005When writing a long line of code, I seem to recall there is a method of stating a new line, trouble is I cant remember what it is. I belive its something like an underscore. Can anyone help please?
View 1 Replies View RelatedNext Line VBA
Mar 18, 2008I am typing in a long SQL string and seem to hit a character line limit in VBA...
How do I programmatically return to the next line and have to code continue to read the SQL String?
I need to add more fields and this is too long..
Line Break
Oct 17, 2005How Can I remove line breaks in a string.
View 6 Replies View RelatedHow To Run It From Command Line?
Jul 20, 2007I have a vba module call runme with this code
Option Compare Database
Sub makequery()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim XlApp As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlws As Excel.worksheet
Dim xlrn As Excel.Range
Dim x As Integer
Set db = Access.CurrentDb
Set qry = db.QueryDefs("q1")
Set rs = qry.OpenRecordset
Set XlApp = New Excel.Application
XlApp.Visible = True
Set xlwb = XlApp.Workbooks.Add
Set xlws = xlwb.ActiveSheet
x = 1
For Each fld In rs.Fields
xlws.Cells(1, x).Value = fld.Name
x = x + 1
Next fld
Set xlrng = xlws.Cells(2, 1)
xlrng.CopyFromRecordset rs
xlws.Columns.AutoFit
rs.Close
qry.Close
qry.Close
End Sub
I can run it from vb eidit interface by hitting run.
How can I run it from command line?
I tried "msaccess.exe" "C:Documents and SettingsjzhuMy Documents est.mdb" /Excl /X "runme"
But it does not seems to work.
THX.
Jeff
Skip A Line
Oct 20, 2005In query:
I set the input parameters:
enter: [Please enter the value:]
After running, it shows "Please enter the value:"
This one I know
How can I make 2 lines showing
"Please enter the value:
eg. 0506:
enter: [Please enter the value: & vbcrlf & eg. 0506:]
It doesn't work.
Please let me know about it. Thanks.
Line Breaks
May 16, 2007I'm not sure if this is the right section for this question, but not sure where else to ask it.
We have a database and a query that is run that creates a table for a webpage.
The webpage (created in FrontPage) displays the info we need, but what we have 1 field that we would like to have display in a certain way. What we would like is, instead of having this 1 field string all the words out in a row.
For example:
word 1 word 2 word 3
Is there something we could either do in the query or within the webpage, that will force each word to a new line within it's table cell on the webpage
So would look something like this.
word1
word2
word3
it makes it easier to read on the webpage.
Last Line Query
Aug 23, 2007Hoping someone can help!:(
I have a table in access which holds interest rates details, and a query that displays the latest interest rate entered into the table (on the last line) by using the Last option in the Totals field.
The problem is that when I enter any new data into the table the query doesn't see this and continues to display the data which is now on the second to last line. The query for some reason just doesnt seem to notice the new last line.
Is there a solution to this so that it shows the new last line?
Thanks is advance!
Reverse Line
Jul 15, 2006Is there a way to reformat so that the line that is the next line to be entered is at the top instead of the bottom >> I hope that is clear
Also when i use the scroll bar at the bottom at the very end to the right it show grey in the background is there a way to limit it so only what is used is showing
Thx STeve
How Do You Create A DB On Line
Jan 5, 2005I need to create a data base from inside an application, in order to then archive data into it. Can anybody help here please
View 1 Replies View Related