multi record query

[Norm]

I am taking 4 fields from a linked AS 400 database, the 4 fields are
IDAREA = T
IDAISL = RA
IDBAY = IL1
IDLEVL = 9
I need the word "TRAIL19" to be my parameter for the information I need for
a query to fill in the form and report
Is there any way to combine these 4 feilds?

 

[Tom van Stiphout]

On Mon, 11 Jan 2010 20:09:01 -0800, Norm

select IDAREA & IDAISL & IDBAY & IDLEVL as myCombinedField
from myTable
(of course you change myObjectNames to yours)

-Tom.
Microsoft Access MVP

 

[Norm]

Thank you for your reply
my combined field...where do I find this?

 

[KARL DEWEY]

my combined field...where do I find this?
It is whatever name you will use to show the results.

 

[Norm]

Greetings from left field
For some reason I just dont get this one
How can I combine those 4 fields into one that I can use as a parameter query?

 

[John W. Vinson]

Greetings from left field
For some reason I just dont get this one
How can I combine those 4 fields into one that I can use as a parameter query?

Exactly how Tom told you.

To do it in the grid instead of in SQL view...

Create a new Query in the query grid based on your AS400 table.
In a vacant Field cell in the grid type

SomeFieldName: IDAREA & IDAISL & IDBAY & IDLEVL

on the Criteria line under this field type

TRAIL19

or put a parameter and enter TRAIL19 into the parameter textbox or prompt.

 

[Norm]

Success!!!

One problem though when I enter TRAIL19 the query returns nothing
When I enter T* I get all TRAIL records the 19 is the key to what I need
When I look at the query there are blanks so it reads ...T RA IL1 9
is this the reason
Thank you very much for your patience on this

 

[John W. Vinson]

One problem though when I enter TRAIL19 the query returns nothing
When I enter T* I get all TRAIL records the 19 is the key to what I need
When I look at the query there are blanks so it reads ...T RA IL1 9
is this the reason

Please open the query in SQL view and copy and paste the entire SQL to a
message here.

Do the individual fields have leading or trailing blanks in the AS400 table?

 

[Norm]

I believe they do have trailing blanks in AS400 but in Access there are no
leading blanks

 

[John W. Vinson]

I believe they do have trailing blanks in AS400 but in Access there are no
leading blanks

You can use the Trim() function to strip off the blanks:

SomeFieldName: Trim(IDAREA) & Trim(IDAISL) & Trim(IDBAY) & Trim(IDLEVL)

 

[Norm]

You can use the Trim() function to strip off the blanks:

SomeFieldName: Trim(IDAREA) & Trim(IDAISL) & Trim(IDBAY) & Trim(IDLEVL)

--

John W. Vinson [MVP]
.
Success!!!

Thanks for all your help John