Software

Ask something
Discussion Locked

Add fields together and append to memo

0 helpful
8 replies

by dhozen

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I am using Microsoft Access to move data from one application to another. Part of what I need to do is gather fields (from quotes by our sales department) and put them into a memo field of another program. The fields would be Partnumber, Quantity,and Price Quoted. I can easily build a string for a single item on a quote, but what I can't do is add additional items if the quote contains more than one item. Also, if possible, I would like to format the memo field so that each item is below the previous, to keep from just having scrambled strings of information. I would really appreciate the help.

8 replies

by dhozen

Yes, that works fine if the quote has one item, but if two or more, I only get the first item. I want all of them. They are stored in a table that has all quote details; including quote number, partnumber, price, qty, etc. of all quotes recorded.

by stillatit

How you do it depends on how your data is stored. If the detail lines of the quote are in separate records, you will have to set up your query to include all these records. You can then have your program walk down the records, and do what you need.
If by "add additional items" you mean making a string with more than one line, you can get around this problem in a number of ways:
- Write the lines to an excel file (easiest) then use Excel to format and output.
- Write an ASCII file, outputting each line as you find it.
- Depending on what you are writing to, you can try to include a carriage return and line feed in the string. cr=chr(15) lf=chr(12). Some programs will filter these out on input.

If you are trying to sum all the quoted widgits by part number, for example, you could first do a query to find the open quotes, sorted by part number, then either walk the records or, for each part number, do a separate query, doing whatever summing you need.

Good luck.

by dhozen

This is partly on track, but I wonder if I am not being clear enough in describing my problem. For example, we quoted the following (this would be an example of a single quote):
100 abc123 $1.00
200 xyz789 $2.00
1000 lmn456 $1.50
Each of these items appears as individual records in the quote details table. I want to grab them and put them into a memo field exactly as you see (one under the next), but I want to do this daily, by the batch for all quotes done on a specific day (each quote having all the detailed quote items accumilated into its memo field). I would like to do all of the work in Access so the process can be automated and run on a scheduled basis. Using either queries or VBA to doit would be fine. Please keep up the input.

by shmaltz

From what you describe I understand that you can acomplish it using the follwoing.
1. First you need to figure out a way on gathering all the records that belong to this quote.
2. The next thing would be to (everything from here on is in VBA) dim a string with an array (using dim mystring()).
3. Next you whould loop thru all the records in step 1.
4. In each step in the loop you whould redim the string with the array using the preserve Keyword
5. Next loop thru the array and insert each item from the array in the memo field. use the constant VBCRLF to insert a Carrige return line feed (new line), in the memo field before you go to the next item in the array, or (to save some space) before you enter the next item (not before you GO to the next item) if it's not the first item (that way you eliminate unnecessery blank CRLF).

by shmaltz

In step 4 in each redim you point to a new item in the array. And you insert the item from the quote there.

by dhozen

This is pretty much what I was looking for, a little more help with the details of the code would have made it easier, but this helped all the same. Thanks for you input.

This discussion is closed to new replies.