Re: "Square boxes"

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Do these values appear in separate fields in each record in your incoming
data stream?
Name
Address
City, State, Zip
DOB, SSN

If yes, then you can use a query to concatenate the fields into a single
string and output the result (example shown here is for three fields, with a
blank space separting the value from each field):

SELECT [Field1] & " " & [Field2] & " " [Field3] AS OutputField
FROM TableName;

--

Ken Snell
<MS ACCESS MVP>




"Blaze" <Blaze@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6F0C495D-4EA1-4494-AC10-298F0982F5A6@xxxxxxxxxxxxxxxx
> Here's what I am doing source to destination:
>
>
> 1. Infopath used to fill out repeating tables, with merge setup.
> 1b. Repeating Table has 8 fields as described previously.
> 2. Infopath submit used to post the data to sharepoint services with
> merge
> on the repeating tables.
> 3. I use Access to look at the sharepoint site. The data comes in with
> the
> boxes already there. I think it has something to do with the merge field.
> 4. I use the query to plug the report into something usable as infopath
> is
> near worthless except to enter data with integrity.
>
> Now, I have use the build command to do the following: as Fredg provided:
>
> Replace([FieldName],chr(13),chr(13) & chr(10))
>
> This works to do what I needed. Now is there a way to combine:
>
> Name
> Address
> City, State, Zip
> DOB, SSN
>
> One entry per incoming data stream? If not, I can just put two text boxes
> with the data source laid out and it would work, just wouldn't be as
> "neat"
>
> Thanks for your input on this issue, you've been a real help!
>
> Blaze
>
>
> "Ken Snell [MVP]" wrote:
>
>> This query does not show any concatenation being done in the query, so I
>> must assume that the ComplaintNarrativeOtherNames
>> field in the Complaint and Witness Forms table/query contains the
>> concatenated names?
>>
>> Need to know where the concatenation of the Chr(13) (carriage return)
>> character is being done, or if the Chr(13) character is already in the
>> data
>> before the query selects the records.
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>> "Blaze" <Blaze@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:F1DF2771-84B8-4A3C-99CA-C078129728EE@xxxxxxxxxxxxxxxx
>> > Nevermind, found it... This is a simplified version of what I am
>> > doing.
>> >
>> > Select [Complaint and Witness Forms].ID, [Complaint and Witness
>> > Forms.ComplaintNarrativeOtherNames
>> > From [Complaint and Witness Forms]
>> > Where ((([Complaint and Witness Forms].ID)=[ID])));
>> >
>> >
>> > "Ken Snell [MVP]" wrote:
>> >
>> >> Post the query's SQL statement. It appears that you are using only
>> >> half
>> >> of
>> >> what you need to start a new line. In ACCESS, you use the combination
>> >> of
>> >> Chr(13) & Chr(10) for a new line (carriage return and line feed,
>> >> respectively).
>> >> --
>> >>
>> >> Ken Snell
>> >> <MS ACCESS MVP>
>> >>
>> >>
>> >>
>> >> "Blaze" <Blaze@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:972FDD8D-8A0F-4A54-B5D5-B645AED03288@xxxxxxxxxxxxxxxx
>> >> >I have a query that seperates the fields by "square boxes". I am
>> >> >pretty
>> >> >sure
>> >> > that these are from an unknown character, but how can I find out
>> >> > what
>> >> > it
>> >> > is?
>> >> > Here is an example of what I want:
>> >> >
>> >> > Michael S Parrill
>> >> > Jessica L Davis
>> >> > Cody A Phipps
>> >> > David W Henderson Jr
>> >> >
>> >> > When I copied and pasted it from my query it shows up correctly, as
>> >> > you
>> >> > can
>> >> > see above, the query looks something like this:
>> >> > Michael S Parrill[]Jessica L Davis[]Cody A Phipps[]David W Henderson
>> >> > Jr
>> >> > The [] is the "square box symbol". When I copy that symbol into
>> >> > here,
>> >> > it
>> >> > is
>> >> > treated as a carriage return.
>> >> >
>> >> > Help, as I'm going nuts on this!
>> >> > Blaze
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: Query with concatenation in explain plan
    ... Because the query is fairly complex, the two parts of the query take a lot of time, where the whole part could be done in one run. ... I stripped the query to the point where the concatenation comes in. ... because the two FILTERs are filtering constants, ... the "good" part of the plan may be bad because you ...
    (comp.databases.oracle.server)
  • Re: Id like NOT to see something on my form.
    ... SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate, ... SELECT LastName,FirstName, "Anniversary" As CardType, DOA As CombinedDate ... Because you've aliased the field in your union query as CombineDate, ...
    (microsoft.public.access.forms)
  • Re: Problem with sql statement in VB
    ... I think that your admonition to "never use concatenation to build ... to build a second query or as parameters to a second query, ... > Of course you could use, but you never know what are special characters, ... >> searching for SQL Injection Attack. ...
    (microsoft.public.vb.database.ado)
  • Re: Two tables / one to many relationship - need single record
    ... Convert the query to a crosstab, specifying File as row hearder, Child / DOB ... The desired recordset would include the needed client ...
    (microsoft.public.access.queries)
  • Re: Query with concatenation in explain plan
    ... Because the query is fairly complex, the two parts of the query take a lot of time, where the whole part could be done in one run. ... I stripped the query to the point where the concatenation comes in. ... Cost: 748 Bytes: 275,550 Cardinality: 2,505 ...
    (comp.databases.oracle.server)