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 Replies
ADVERTISEMENT
Jan 25, 2008
Hi,
I'm trying to run an update query on a field called "DisplayURL" to remove all of the spaces that the field contains. Some have 1 space, some 5 and some have no spaces.
I'm using Access 2000 and the replace function ( Replace("DisplayURL"," ","") or Replace([DisplayURL]," ",""). However it produces an error stating that there is an "Undefined function "Replace" in expression".
I've searched online and found that Access 2000 doesn't have this function, but its easy to create a "wrapper" to make it work.
I've tried various examples online with no luck. Does anyone know what "module" I'd have to create to get this working, and how to "call" the function as I do not understand how to do this?
Thanks in advance for your help, I've been having a nightmare with this problem.
Thanks again.
Dean
View 5 Replies
View Related
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
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
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
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
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
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 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
Mar 17, 2008
I have a table w/field set as text that I don't want users to enter a "space" into, how would I stop this? I suspect " " in the field validation field? Does this sould correct? What does everyone else use?
View 4 Replies
View Related
Dec 7, 2007
Hi, I have a text box in a from for data entry, how do I disable a space. For example I don't want the user to have a space between charaters or before and after a charater. Thanks.
View 7 Replies
View Related
Mar 10, 2005
Hi, I hope everyone is well.
As part of an extraction program I'm making, I've got a table (lets call it TABLE1) with values that have trailing spaces
e.g. "ENGINEERING ", "LOGISTICS ", etc....
This has been set up as such to match the values that exist in a read only table I am linking to from another database.
I have used a query of TABLE1 as a row source for a combo box. When the values are selected in the combo box the trailing spaces are lost.
Can I remedy this?
Thanks in advance :)
View 3 Replies
View Related
Aug 8, 2005
How can I remove the spaces in the example string!
HICX108-Standard Top -Vendered
Thanks in advance!
View 2 Replies
View Related
Nov 4, 2005
Hi,
i want to replace spaces in my query, but there are a few hard parts in it.
I dont always know how many spaces. en second, the spaces that need to be replaced are after the first character. The rest needs to be removed.
i have:
----1----1----2
the "-" are spaces!!
and i want it to show
1.1.2
this is what i tried replace([MinutesID];" ";".") & " - " & [minutesTitle]
can someone assist...
thanks
View 2 Replies
View Related
Nov 28, 2005
Hello,
I have the following SQL query that runs perfectly. Now I need to put some blank spaces in between for the first expresion (EXPR1). At the moment, as you can see, brings me the 4 fields together.
I have tried using + ' ' + and + " " + but it won't let me as it says that the syntax is Incorrect. What should I use to get spaces?
Thank you!
**********
SET @SQL = 'SELECT dbo.brnch.addr + dbo.brnch.suburb + dbo.brnch.state + dbo.brnch.PIN AS EXPR1, dbo.individuals.posit, dbo.individuals.phn, dbo.company.name
FROM dbo.company INNER JOIN
dbo.brnch ON dbo.company.ID = dbo.brnch.com_ID INNER JOIN
dbo.individuals ON dbo.brnch.br_ID = dbo.individuals.br_ID INNER JOIN
WHERE dbo.brnch.country in (' + @OrderList + ') '
EXEC(@SQL)
View 1 Replies
View Related
