Queries

[gr8_access]

Hi,
I am having issues with access sql. I have data for example: 123456789. I
would like to break this into two lines and I don't know how to do it.
Intended output should be
1234
56789

Please advise how can I accomplish this ?

 

[John W. Vinson]

Hi,
I am having issues with access sql. I have data for example: 123456789. I
would like to break this into two lines and I don't know how to do it.
Intended output should be
1234
56789

Please advise how can I accomplish this ?

Is this a nine-character Text field in a table? Do you want to just display it
on two lines (on a form or report), or create two records ("lines") in a
table? Context please!

You may be able to use

Left([fieldname], 4) & Chr(13) & Chr(10) & Mid([fieldname], 5)

as a calculated field... but again, the context is unclear.

 

[gr8_access]

John,
This is my current output...
P123456123456789.1212345678.0112345678.0112.345612345678901.2312345678.
901234567890.121234567890.12123456789.12123456789.12123456789.1212345678.
90123456789.
121234567890123456789123456789012345678912345678901234567891234567890

I would like to break this down into two lines. Would you please advise ?
Please let me know how can I break this into two lines or even three lines
also ?

Hi,
I am having issues with access sql. I have data for example: 123456789. I

[quoted text clipped - 4 lines]

Please advise how can I accomplish this ?

Is this a nine-character Text field in a table? Do you want to just display it
on two lines (on a form or report), or create two records ("lines") in a
table? Context please!

You may be able to use

Left([fieldname], 4) & Chr(13) & Chr(10) & Mid([fieldname], 5)

as a calculated field... but again, the context is unclear.

 

[John Spencer]

Again the question is do you want this done in one row (record) or do
you want to split this value into multiple rows (records)?

AND how do you decide where to split the value into multiple lines? Do
you want an exact number of characters per line, or do you want a new
line after every period, or do you want a new line based on every third
appearance of the characters 89 in sequence, or ...

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John,
This is my current output...
P123456123456789.1212345678.0112345678.0112.345612345678901.2312345678.
901234567890.121234567890.12123456789.12123456789.12123456789.1212345678.
90123456789.
121234567890123456789123456789012345678912345678901234567891234567890

I would like to break this down into two lines. Would you please advise ?
Please let me know how can I break this into two lines or even three lines
also ?

Hi,
I am having issues with access sql. I have data for example: 123456789. I

[quoted text clipped - 4 lines]

Please advise how can I accomplish this ?

Is this a nine-character Text field in a table? Do you want to just display it
on two lines (on a form or report), or create two records ("lines") in a
table? Context please!

You may be able to use

Left([fieldname], 4) & Chr(13) & Chr(10) & Mid([fieldname], 5)

as a calculated field... but again, the context is unclear.

 

[John W. Vinson]

John,
This is my current output...

Output from what? To what? How is this output being generated?

P123456123456789.1212345678.0112345678.0112.345612345678901.2312345678.
901234567890.121234567890.12123456789.12123456789.12123456789.1212345678.
90123456789.
121234567890123456789123456789012345678912345678901234567891234567890

I would like to break this down into two lines. Would you please advise ?

Do you want to break after the letter P, after the 65th character, after the
fourth period....

Please let me know how can I break this into two lines or even three lines
also ?

You will need to give us some more information. WE CANNOT SEE YOUR DATABASE.
You have not answered our questions.

We will be glad to help, if you will allow us to do so, but we're not mind
readers.

What is this data?
Where is it coming from?
What is the context - a query, a form, a report?

 

[gr8_access via AccessMonster.com]

sorry guys, I am new to these things so couldn't explain clearly. Please be
patient with me, and let me try this again.

I have this below data like multiple records in my table.
P123456123456789.1212345678.01 H12345678.0112.3456 D12345678901.2312345678

I would like break this down by running a access sql query for each record.
I would like to see the out like this below:

P123456123456789.1212345678.01
H12345678.0112.3456
D12345678901.2312345678
Hope I was able to explain this time. Please let me know.

Thanks

 

[John W. Vinson]

sorry guys, I am new to these things so couldn't explain clearly. Please be
patient with me, and let me try this again.

I have this below data like multiple records in my table.
P123456123456789.1212345678.01 H12345678.0112.3456 D12345678901.2312345678

I would like break this down by running a access sql query for each record.
I would like to see the out like this below:

P123456123456789.1212345678.01
H12345678.0112.3456
D12345678901.2312345678
Hope I was able to explain this time. Please let me know.

Thanks

I'm sorry, but we're still not communicating.

Please post a description of your table in the form:

Tablename
Fieldname Datatype Size
Fieldname Datatype Size

and give an example of how the big block of text is represented in your table.
Is P123456123456789.1212345678.01 H12345678.0112.3456 D12345678901.2312345678
the value of one field in your table? Or is this three fields? Or is this one
field and three records in the table?

And are you looking at the *Query Datasheet* - or do you want to look at the
query datasheet - for data presentation? If so, that's not the function of a
query. Queries are for combining and organizing data; Forms are used to
display data on screen; Reports are used to print data. If the three strings
(separated by blanks) are in fact three fields in a table, then a Form or
Report with three textboxes, one above the other, will display the data as you
request.

 

[gr8_access via AccessMonster.com]

John,
My table name is "Various_Records". As an example, I will use characters as
field names.
These are the fields: P, H, D
My IT wrote some query that gives the following output from these fields....
This is the output as example: And this is just on record.
P123456123456789.1212345678.01 H12345678.0112.3456 D12345678901.2312345678

There are may be around 100-150 records with different numbers but same
preceding characters P, H, D.

Field P has upto 13 character space, H has upto 14 character space, D has
upto 11 character space. The above example might have more character space.

When I tried access sql, this what I have:
Select P&H&D from Various_Records which produces the same result above. But
I would like to break it down like below:
P...
H...
D...

Please let me know if I ws able to explain this time.
Thanks

sorry guys, I am new to these things so couldn't explain clearly. Please be
patient with me, and let me try this again.

[quoted text clipped - 11 lines]

Thanks

I'm sorry, but we're still not communicating.

Please post a description of your table in the form:

Tablename
Fieldname Datatype Size
Fieldname Datatype Size

and give an example of how the big block of text is represented in your table.
Is P123456123456789.1212345678.01 H12345678.0112.3456 D12345678901.2312345678
the value of one field in your table? Or is this three fields? Or is this one
field and three records in the table?

And are you looking at the *Query Datasheet* - or do you want to look at the
query datasheet - for data presentation? If so, that's not the function of a
query. Queries are for combining and organizing data; Forms are used to
display data on screen; Reports are used to print data. If the three strings
(separated by blanks) are in fact three fields in a table, then a Form or
Report with three textboxes, one above the other, will display the data as you
request.

 

[John W. Vinson]

John,
My table name is "Various_Records". As an example, I will use characters as
field names.
These are the fields: P, H, D
My IT wrote some query that gives the following output from these fields....
This is the output as example: And this is just on record.
P123456123456789.1212345678.01 H12345678.0112.3456 D12345678901.2312345678

There are may be around 100-150 records with different numbers but same
preceding characters P, H, D.

Field P has upto 13 character space, H has upto 14 character space, D has
upto 11 character space. The above example might have more character space.

When I tried access sql, this what I have:
Select P&H&D from Various_Records which produces the same result above. But
I would like to break it down like below:
P...
H...
D...

Please let me know if I ws able to explain this time.

Don't confuse data *storage* with data *presentation*. A Query is not an
appropriate tool for presenting data!

Create a Form based on the table.

Put a textbox on the form bound to the P field.
Directly beneath that textbox put another textbox bound to the H field.
Directly beneath that... well, you get the idea.

For printing the data use a Report rather than a Form.

This could be done in SQL but it would be a hassle and is simply not the right
tool for the task.