Queries :: Multiple Lines In A Field?

Jan 8, 2014

I've exported some data which has 3-4 address line within the same field which is very odd.

The row has all the usual data, but when I look at the data in datasheet view the adresss line 1 shows only but when I click into the field and press down, more address info comes up. Is there any way to have this on one row within the field rather than multiple.

A bit like in Excel when you Alt+Tab in a cell it drops down but is the same cell, well this exactly like that in Access 2003.

View Replies

Queries :: Possible To Split One Table Into Multiple Lines?

Oct 27, 2014

My boss made a satisfaction database that tracks satisfaction in 2 ways, so we have:

Table

ID
SatisfactionAType
SatisfactionAScore
SatisfactionBType
SatisfactionBScore

I'd like to make a query that would split these into two lines, one for A, one for B and then export it to excel.

View 4 Replies View Related

Forms :: How To Prevent Multiple Lines In Field

Apr 13, 2015

Ive had a recurring problem with one of my databases

ive got a form with a subtable on it, the subtable is very important as much of the DB's data is entered in there.

every once and a while one of the users will be doing data entry and on a certain field (a short text field if that makes any difference) theyll push enter instead of tab, to move on to the next field, enter creates a new line instead of moving to the next field, when a new line is created the user will either assume the field is blank and retype the data or ignore it entirely and move on

the trouble is one of my queries completely fails to work when there are multiple lines in this field (comes with a data type mismatch error)

is there a way to prevent the field from being able to contain multiple lines (like a property setting or some VB code?)

View 3 Replies View Related

Queries :: Sums Up All Previous Lines In A Field

Feb 9, 2014

I have a query that displays the difference between the number of scanned and processed invoices for each date (invoices scanned minus processed). Now I would like to make a query that for each date based on this shows the total backlog accumulated.

Example table:

The first column contains the date, and the second number of invoices scanned minus invoices processed.

2014-01-02 53
2014-01-03 -15
2014-01-04 24

Would it be possible somehow to make a query that for each lines shows the complete sum of all previous lines?

Based on the above data the result should be:

2014-01-02 53
2014-01-03 38
2014-01-04 62

Or would I have to write some VBA code to create some kind of temp table?

View 2 Replies View Related

Queries :: Date Range Query - Return Lines Where Field Is Blank In Table

Aug 15, 2013

I have a single table with customer information, one of the fields is a date field "LastContacted".

I'm creating a search form with 2 date fields (txtDate1 & txtDate2) to search a date range of the LastContacted field, and I need to write this into the query that the search form uses.

I have written this using Nz so that it can still return results if the search boxes are left blank:

Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)

This seems to work and it returns lines from the table where there is a date entered. However some of the fields in the table have no entry in the LastContacted field. How to code this query so that it also returns lines where the LastContacted field is blank in the table?

I have tried:

like "*" & (Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)) & "*"

but this returns errors when I try to run it.

I'm using Access 2010.

View 14 Replies View Related

Multiple Lines In Text Box

Dec 14, 2004

i'm no noob, but i'm also not the expert, what i'm trying to do is this,
i'm taking multiple string out of an recordset and putting them in one big text box
but the problem is that after every record there has to be an enter in the text box
So for example:

record 1 =
-bla bla bla
-tja tja tja

record 2 =
-waa waa

my result in the text box must be:
-bla bla bla
-tja tja tja
-waa waa

and not (like it is now):
-bla bla bla
-tja tja tja -waa waa

who has a clue? chr(11) & chr(13) do not work, i activated "on enter go to next line"

thx in advance for helping out

View 2 Replies View Related

Importing Multiple Lines Of Text

Aug 27, 2007

Good morning. I am new to coding VBA and need some help if possible. I need to import a Comma Delimited Text file into a MS Access table. The records vary in length and may take up multiple lines of text before the next record. The first field contains the type of record and are all prefixed 1###, with the ### being variable. (1001 - 1100). If the record goes over one line the 2nd line with start with 1000. Here is an example: The records with 1001 continue to the next line with 1000 so you know it continues. The other records are all single lines starting with 1100, 1003, 1004, 1006...1017.

1001,00000000,00000000,00000000,00108888,00537906
1000,00100,"CAJUN MEAT ","CHUB "-0020380
1001,00000000,00000000,00000000,00020492,00130534
1000,00108,"RED BEANS ","CHUB ",-0221510
1001,00000000,00000000,00000000,00222572,00796807
1100,0000000000,0009605419
1003,01,000101,263558519,"QQ380427",5,08,19,107,16,07,58
1003,02,000202,263774367,"QQ380428",4,08,19,107,22,22,12
1004,1," "
1004,2," "
1004,3," "
1004,4," "
1006,00000000,00000000,00000000,00000000
1007,"$ OFF ",0000000000,0000000000,0000000000
1007,"FREE ",0000000000,0000000000,0000000000
1007,"SENIOR 10% ",0000000000,0000000000,0000000000
1007,"50% Police ",0000000000,0000000100,0000000001
1007,"CREW 50% ",0000000000,0000000310,0000000003
1007,"CREW 100% ",0000000000,0000001976,0000000009
1007,"MANAGER ",0000000000,0000000260,0000000003
1007,"BONUS ITEMS ",0000000000,0000000000,0000000000
1008,00107,00001,00065,06074,00347,00290
1009,01,"BREAKFAST SALES ",0000114476,02940,000091
1009,02," SALAD SALES ",0000009093,00233,000007
1009,03,"DRIVE-THRU SALES ",0000142716,03666,000106
1010,0000002646,0000009093,0000000000
1011,000,0000000000,003,0000005208
1015,"NET SALES ","+",0000389258
1015,"TAX ","+",0000023434
1015,"GROSS SALES ","=",0000412692
1015,"RESTAURANT BANK ","+",0000000000
1015,"ADJUSTED GROSS CASH ","=",0000412692
1015,"SEPLINE----------------------"," ",0000000000
1015,"AMOUNT DEPOSITED ","+",0000365609
1015,"GIFT REDEEMED ","+",0000000000
1015,"CREDIT CARD ","+",0000044844
1015,"MISC. INCOME ","-",0000000000
1015,"PETTY CASH ","+",0000000000
1015,"NET SALES ","-",0000389258
1015,"TAX ","-",0000023434
1015,"CASH OVER/SHORT ","=",-000002239
1015,"CASHIER OVER/SHORT ","-",-000001839
1015,"OVER/SHORT RECONCILIATION ","=",-000000400
1015,"SEPLINE----------------------"," ",0000000000
1015,"NET SALES ","+",0000389258
1015,"GIFT CERT. SOLD ","-",0000000000
1015,"OTHER NON-FOOD ","-",0000000000
1015,"NET FOOD SALES ","=",0000389258
1016,1,00000000,00000000,00000000
1016,2,00006103,00036415,00118800
1017,0004339338

I will need to do this weekly for many different files. I appreciate all the help that anyone can provide.

View 4 Replies View Related

Split One Record Into Multiple Lines

Mar 10, 2013

I have an access form that is used for cash-ups. The cash-ups are not done on a regular basis and the owner requested that the database look at the last cash-up date, current cash-up date and calculate the difference in days and then calculate the average take per day by dividing the total take for the period by the number of days from the last cash-up date to the current cash-up date. This was simple and is done however, what he now wants is to export this data to another table and have it split the total amount per record by the average number of days and reflect the average amount per day over as many lines.

Eg: R5,000.00 / 20 days = R250.00 per day.

This data needs to be displayed in TWENTY lines each with a value of R250.00.

View 1 Replies View Related

Write/update Multiple Lines Belongs To A PO?

Apr 18, 2008

Hello all,

How are you? Hope everything is well!

I have a question. If I have a form, which has underlying source is the table LineItem. The table has these fields PO#, Line Item, Cancel check box, and Cancel Reason list box. Each PO can have 1 to multiple line items. Once in a while, the users need to cancel certain line items and type a reason in the Cancel Reason box. When the users click the Cancel check box, it force the user to enter a reason.

However, in cases, some POs have 10-100 line items, and the reason are the same for all line items, and it is time-consuming and tedious to do one by one. I would like to find a way to enter reason in line 1, for example, and copy/update the rest of line items in that same PO with that.

Please advise HOW and WHAT COMMAND should I use to update the text in one field of one record (line item) to ALL other records (line items) in the same PO.

Please help! Thanks so much.

View 3 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

Multiple Lines In Order Form From Single Database

Feb 28, 2005

I have 2 databases Product, with 3 entries ProdID, ProdName and UnitCost.
2nd database Order has Order ID, ProdName, UnitCost, Quantity, SaleDate and Total.
I want to create a form to input data into the Order databse that allows me to enter several rows using the data from the product table by selecting ProdName(Product) from list and UnitCost(Product) being inserted into each row automatically as a new ProdName is selected.
Can anyone hsuggest how best to do this.

View 4 Replies View Related

Forms :: How To Break Update Statement Into Multiple Lines

Mar 7, 2014

I am unable to break the UPDATE statement into multiple lines and now I am getting "Syntax Error in UPDATE statement"

Following is the multiple line Update Statement

Dim strsql As String
strsql = "Update tblcurr" & _
"SET tblCurr.Currencyname =" & _
"[forms]![updatecurrency]![txtcurname]" & _
"WHERE (((tblCurr.Currencycode)=" & _
"[forms]![updatecurrency]![txtcurcode]));"
DoCmd.RunSQL strsql

Following is the one liner Update statement which, works perfectly

strsql = "UPDATE tblCurr SET tblCurr.Currencyname = [forms]![updatecurrency]![txtcurname] WHERE (((tblCurr.Currencycode)=[forms]![updatecurrency]![txtcurcode]));

View 4 Replies View Related

Forms :: Duplicate Lines In Multiple Form And Query

Jul 8, 2015

The purpose of my database is to keep a record of training activities undertaken by staff. There is therefore a multiple form which displays, for a specified worker, the list of all the required activities. Each line has a button which opens another form to allow an activity to be started (together with start date and other info). The main tables are Worker, Activities, and a junction table Worker2Activities. On closing the second form and refreshing the first one, the started activity is correctly recorded, but on as many lines as there are staff on the course. To make matters worse, these also appear when i then select another worker (and if i create another worker, the number of lines goes up by one for all of them). The Worker2Activities table correctly show one new record created

View 3 Replies View Related

How To Keep Multiple Lines When Importing Data From Excel To Access

Sep 7, 2011

I face a multiple line problem when i try to import data from Excel to Access db. Is it possible to keep multiple lines of a cell after transferring to Access. Since Access see any ceel of Excel as a Field, it takes all the lines as a line in the same row.

In enclosed, you can see a sample Access document. I put a point(.) between every lines in the same field manually. Is it possible to split up those data from these points and make a new line in the same field by VBA? For example,

BEFORE:

1) 232N1635-2.2) 412N1168-7

AFTER:

1) 232N1635-2.
2) 412N1168-7

View 1 Replies View Related

General :: Saving Invoice With Multiple Lines In The Item Section

Jun 17, 2014

I want to make an invoice with products and services and totals, the thing that has me wondering is how do I save an invoice with multiple lines in the item section of the invoice. I want to be able to choose a customer and then see the list of invoices connected to that customer, and when I click the invoice to open it as it was. What would be the best way to do this?

The invoice contains:Customer info, adress, name...etc

List - multiple lines of products sold / services completed
Totals, date & time...etc
Relationship goes like this: Customer -> Invoice <- Inventory / Services

View 3 Replies View Related

Import Text File / Multiple Lines And Varying Delimiters / Size To One Record

Aug 22, 2012

Using MS 2007, I have a 200 text files exported each day from another application that has two different types of lines (see below). I would like to import each text file in to a database as a single record.

Text file example (text.txt):

R111 WC 8/21/2012 7:00 Doe, John doej 10110110

First Question? Y
Second Question? N
Third Question? Y
...
Seventeenth Question? 10

As you see, I have the first row with multiple fields, but the next rows I have a question and an answer.

I would like to have this data imported as shown in the attachment. Example.zip

Most answers I see are for either multiple lines (same data and sizing). I am not sure how to handle several different lines with that vary in size and delimiters.

View 5 Replies View Related

Queries :: Comparison Of Dates Between Lines

Oct 22, 2013

I am having some "fun" at the moment trying to work out how to compare two times on different rows. The current setup I have is:

StartDTM EndDTM VisitID
09:00:00 10:00:00 1
11:00:00 12:00:00 2
12:15:00 13:00:00 3

etc etc...

I have found a SQL Query I have tried to use, however I am having no joy. The query is such:

WITH rows AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY VisitID) AS rn
FROM Data
)
SELECT DATEDIFF("n", mc.EndDTM, mp.StartDTM)
FROM rows mc
JOIN rows mp
ON mc.rn = mp.rn - 1

View 6 Replies View Related

Queries :: Updating Lines In A Query

Sep 26, 2013

I have created a query which is based on 2 tables.I now want to use the query to update content as needed in one of the tablesWhen I try to do this it says, Operation must use an updateable query..I used the update feature but it still says must be created in updateable query.

View 4 Replies View Related

Count How Many Lines Stored In A Field.

Jun 26, 2007

Is there any way to count how many lines of text are stored in a particular table field? How about counting how many times was the "enter" key pressed?

Thanks

Mafhobb

View 4 Replies View Related

Automatically Removing Lines When A Field Has No Value

Dec 28, 2011

I'm using a query to concatenate data from a table into a specific HTML format. I would like, however, to find a way to remove lines of HTML when there is no data in a specific field.

Example:

If I were to create a list of available colors of a shirt like so:

<ul>
<li>[Color1]</li>
<li>[Color2]</li>
<li>[Color3]</li>
<li>[Color4]</li>
</ul>

but if there were only 3 available colors, I'd end up with this:

<ul>
<li>[Color1]</li>
<li>[Color2]</li>
<li>[Color3]</li>
<li></li>
</ul>

I need to find a way to get rid of that last vacant tag. I image there's some kind of code I could write (if x is null, clear line17, or something like that) but I wouldn't even know where to start with that.

View 3 Replies View Related

Ordering Lines Within A Memo Field By Date/time

Dec 7, 2006

I have a memo field which records the events that happen on a form. So for example when a user changes the assignment from one user to the next, a row is written to the memo field indicating date, time, windows logon and then a brief description

Basically I end up with :

07/12/06 11:16:34 kleaves : Assigned user changed from Joe Bloggs to John Doe

At present every new row is added to the memo field at the end, so the most recent action is at the bottom.

How can I sort this so that most recent is at the top.

View 8 Replies View Related

Queries :: Can Copy And Paste Lines From Email And Display Properly

Mar 20, 2014

This may not be the proper forum, but I don't know where else to put the question. Table1 is a mysql table with a memo field which is set up in a form as a rich text field. I can copy and paste lines from an email and they display properly. Viewing that field in the underlying table they have <br />, etc.

Table2 is another mysql table, containing a memo field in plain text. I need to append the records in table2 to table1. The memo field gets appended as one long run-on sentence with no LF/Cr or <br />. How can I programatically convert the plain text to rich text either before, during or after the append?

View 2 Replies View Related

Purchase Orders And Lines - How To Make The Sorting Of 1 Field Dependent Upon Another

Dec 12, 2006

I have a database of purchase orders
Many of the purchase orders have a revision No against them, eg:-

Purchase Order No / Line No / Revision No / Value
1001 / 001 / 00 / £50
1001 / 001 / 01 / £100
1001 / 001 / 02 / £200

I am trying to find a way to write a query that will show me the actual value of the final revised value of each Purchase order line ie: the answer to above is

1001 / 001 / 02 / £200

Could anyone please help?

View 2 Replies View Related

Queries :: Counting Records Returning Load Of Lines Of Repeated Data?

Oct 11, 2014

I have created a query that is designed to return a count of how many records there are in various tables. There are 10 expressions in all, so when it is run I am expecting to see one row of data with each field populated with the number of records.

It does do this, but the data is repeated over line after line (see the attached picture)

View 4 Replies View Related

Multiple Field Queries

Aug 18, 2006

Hey y'all, my question this morning is concerning doing a multiple field query in Access. I have a lot of experience with both SQL and Access, but have never had to do something like this.

I have a form with a text field for every row in a table (so there are 15 text boxes), and I want the user to be able to type in any data they know for any number of fields, from 0-15 rows. So I want each text box to be matched on (Like '*'&[data]&'*'). My problem is that if I say "where x=x AND y=y" and so on and so forth, it brings up all fields, because there are wildcards on each end of an empty string. I also have problems with just doing OR for each field, because it only matches on one field, even if they entered data into 4 fields. I'm so confused!!!! Did this make any sense? Any Ideas? :confused:

View 1 Replies View Related

Multiple Queries On Memo Field

Jun 27, 2007

Hello all,

I'm having a bit of a problem with a query that I am trying to run that searches a memo field, and I hope someone can help me!

Basically, I have a fairly simple database, 2 tables - 1 of the tables has a memo field which has 'keywords' listed in it (a mixture of words/phrases separated by commas, although I've tried with just spaces in between).

When I search using a Query box for 2 keywords using the following query, it can only find the correct records when I enter the keywords in the reverse order in which they appear in the memo field...
query: Like "*" & [Enter the 1st keyword to search for] And Like "*" & [Enter the 2nd keyword to search for] & "*"

If the memo field has data eg, "funding, teaching" (as opposed to "funding, nursing" for example) and I search for 'funding' and then 'teaching' - no results appear.
If however, I search for 'teaching' and then 'funding'... ta da! Result found.

Obviously, whoever searches the database isn't necessarily going to know what order the words were entered into the memo field!

So, is this a peculiarity to Access, or is there something I'm doing wrong?

Thank you in advance for your help.

View 4 Replies View Related