Last Line Query

Aug 23, 2007

Hoping 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!

View Replies


ADVERTISEMENT

Queries :: Remove GROUP BY Line And Stick Semicolon At End Of Previous Line

Jun 25, 2013

Here'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.

View 12 Replies View Related

Forms :: How To Get Values From Line Items Form Inserted Into QUOTE LINE

Jun 5, 2014

I 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

View 6 Replies View Related

Modules & VBA :: Read CSV Transaction File Line By Line And Add Correct Transactions To Access Table

Nov 29, 2014

I 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]....

View 8 Replies View Related

Modules & VBA :: How To Import Text File Line By Line

Nov 18, 2014

I 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.

View 14 Replies View Related

Forms :: No Line Only Marker In Line Graph

Oct 18, 2014

I 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?

View 1 Replies View Related

Line Spaces In Query?

Jan 2, 2008

Hi,

Does anyone know if its possible to have a page break/line space in a query's results?

For example, a query returns 5000 results, and after every 45 there is a space of 2 or 3 spaces. When I say "space" it could be a couple of empty records or rows.

The reason I ask is because I use a query to filter the records I want from my table. I then use a make table query to make a new table with these records, then I use a macro to export the table into a .txt file.

This text file is then used as a product feed, except the web application I use to import the data can only take a maximum of 50 records - so it'd be nice to already have a space so when I cut and paste the records into the web application I don't accidently try to import 51 records and mess everything up.

I know it's a strange one, but if theres anyway of setting the format of the .txt file to contain spaces would save a lot of time.

Any ideas would be welcomed, thanks for your time.

Dean

View 3 Replies View Related

Process Each Line Of Update Query

Sep 8, 2006

I have this update query that does not quite work. I have attached a picture of the design view. What I need to do is process each line and update the processed field (yes no) to minus one to then exclude that record. Unfortunately when this query runs, it sets the processed field after matching all records.

So how do I process line by line

View 1 Replies View Related

Modules & VBA :: Print Every Line From The Query

Mar 16, 2015

I have a query and I am prompted for a " nco " number. This could produce any amount of lines.

What I would like to do is have some code that will ask me the NCO number then print of a report for each line item from the query so if I have 10 lines I need 10 reports .

My query name is "internalworksorder" ...

View 1 Replies View Related

Find Records With A Line Break Using A Query

Apr 1, 2008

Hello all,

I want to create a query that looks for a manual line break in a memo field. Is there a code or some way that I can do this? (i.e in a Memo field the user has entered some data, hit Ctrl-enter to get to a new line, entered more data on the new line. I want to find the records where this has occured)

Also, on a similar note, is there a way I can deny my users the option of doing a manual break (Ctrl-Enter) in a Control in a Form? (to prevent me needing to run this query in the future)

Please advise.

tia,
Angel

View 2 Replies View Related

Query: Display All 3 Matches For A Field In One Line

Feb 19, 2006

Hi all, I'm new to this forum. I'm having a problem with a query I'm trying to create.

I have two tables in the database: tblCustomers and tblInstallations. These two tables are linked by a field 'CustomerID'.
CustomerID is the Primary Key in tblCustomers.
In tblInstallations, the primary key is InstID and for each CustomerID in tblInstallations there are either 1 or 3 InstID records.

Now what I want to do is create a query with fields: Customer; CustomerID; InstID1; InstID2; InstID3.
InstID1 will contain the first InstID for the CustomerID, and InstID2 & InstID3 will contain the second and third, if applicable.

I see in Query Design View in the 'Totals' line there is a option to display the 'First' and 'Last' match for a field. So is there any way i can use this method and also display the second record? If not, is there any other way to make this query work?

I hope somebody can help!!

Kerry

View 1 Replies View Related

Queries :: How To Add A Text Line To A Query Field

May 18, 2013

I have a combo box that gathers data based on a query. Is it possible to add a line of text to be displaed every time i pull down the box?

For example I have:

Data1
Data2
Data3

I would like to have:

Not In List (the added txt)
Data1
Data2
Data3

View 2 Replies View Related

Help With Select Query To Get Single Line Instead Of Multiple Lines

Dec 21, 2007

Hi,

I have a query that selects values from a table and I'm further creating a letter based on this query. The problem i'm having is that if the query pulls 3 rows as result then it creates 3 separate letters. Basically this is how the output of query looks:

(refer attachment)

Is there any way to write this query so that I can get output as below:

Col1 Col2 Col3
10 20 10


Please advise.

View 7 Replies View Related

Queries :: Function To Output Line Numbers In A Query - YTD

Apr 18, 2014

Is 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?

View 1 Replies View Related

Queries :: Creating Query With Single Line Per Employee

Mar 28, 2013

I currently have a database that has the following tables:

t_Employees
t_Cost_Centers
t_Changes

The [t_Changes] tracks what cost centers the employee is in with a start date and end date:

Change_ID
Employee_ID
Cost_Center_ID
Start_Date
End_Date

I want to run a query that will show a single line per employee, and different columns for each of the cost centers the employee was in. If the employee was in 3 different cost centers, the query would have 4 columns (1 for the employee, 1 for the first cost center, 1 for the second cost center, 3 for third cost center).

I saw another thread to linked to a concatenate example by Allen Browne, but that places multiple values in a single cell. I want different columns per cost center.

View 2 Replies View Related

Forms :: Total Line From A Query Displayed In A Form

Jan 8, 2014

I am looking for displaying the totals from a query onto a form.

I have a query which has a total line showing a count of the number of entry's in each column. I would like to be able to display these totals on one of my forms in text boxes (or any other way). Is this possible and if so how do I do it ?

View 3 Replies View Related

Queries :: Query To Show As A Single Line Item

Apr 8, 2013

Access Database in which i download the table from an Excel file.I am looking to create a query with certain criteria but did not have any luck recently.

1) I will need the query to show <Short code> that are only unquie to Korea.That means shortcode with a count of 1 belonging to Korea only.

2) I will need the query to show as a single line item <Shortcode> that appears two times under the country Korea. For example short code CB01406 is shown as two rows, i will like it to only show as a single row.

3) For all other shortcodes which exist in China as well as in Korea or China only. I will like them to be hidden in the query. That means not shown at all.

View 7 Replies View Related

General :: Totaling Line Values In A Query Before Appending The Data?

Oct 21, 2014

we have a calculated field in an append query Line_Cost which eventually creates a purchase order.

We have to have a minimum order value of £1500.00, so, we need to total the values before running the append query.

View 3 Replies View Related

Queries :: Crosstab Query That Keeps Each Contract On One Line And Separate Fields For Each Contact

Jun 4, 2014

I have two tables, and I'm trying to create a crosstab query...I think. One table is Contracts, one is Contacts. Each contract could have multiple contacts, but they are numbered (1, 2, 3...etc.) based on importance. I want to create a query that keeps each contract on one line, and separate fields for each contact and each field of the contact. So a contact will have Title, First, Last, Address, etc. So I want my query to show as follows:

Code:
Contract-----Title1-----First1-----Title2-----First2
ContractX Mr. James Mrs. Sally

I of course need this to be dynamic, so if a contract has 9 contacts, there are fields up to Title9, First9, etc.

View 7 Replies View Related

Disappearing "line Break" In A Query Expression

Oct 25, 2005

I have created an expression in a query to create an address label. My intention is to create one that looks like:
Name
Address
City, State Zipcode

To get the proper line breaks, I used the Alt + enter method. Though it is not visible when I look at it in SQL, the fields appear on separate lines.
Here is the actual expression used:
MailLabel: Nz([Title] & ' ') & [FName] & ' ' & [LName] & ' ' & [Extension] & '
' & [ADDRESS] & '
' & [CITY] & ', ' & [STATE] & ' ' & [Zipcode]

Problem: Sporadically, the database drops my line breaks and the expression becomes:
MailLabel: Nz([Title] & ' ') & [FName] & ' ' & [LName] & ' ' & [Extension] & ' ' & [ADDRESS] & ' ' & [CITY] & ', ' & [STATE] & ' ' & [Zipcode]

The resultant entry then just wraps around, depending on the size of the column.
I am trying to use this query to create labels in a Report. Any idea why the line break disappears? Any suggestion on how to better build the expression to maintain the proper line up?
Thanks

View 5 Replies View Related

SQL Line (Not Like)

Jun 20, 2005

I 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

View 4 Replies View Related

End Of The Line

Jun 29, 2005

When 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 Related

Next Line VBA

Mar 18, 2008

I 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..

View 4 Replies View Related

Line Break

Oct 17, 2005

How Can I remove line breaks in a string.

View 6 Replies View Related

How To Run It From Command Line?

Jul 20, 2007

I 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

View 4 Replies View Related

Skip A Line

Oct 20, 2005

In 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.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved