Not just returning value of selected record

[littlevikinggirl]

Hi,

I posted a badly worded question last week so got no replies and am
still struggling to figure out the problem myself. I have a table
containing two fields, Location and Serial Number. I want to display
some of the serial number values on a form which will have a diagram
overlaid (to map out the "locations" ).

I have been trying to write an If statement in the controlsource
properties of the text boxes to say "if location=positi on1 then serial
number, else "?"

=iif ([Location]![Location]="position1" ,[Location]![SN],"?")

This only evaluates the record I am displaying and so would have to
toggle through to see each one in turn. But I want to be able to see
all the records displayed on the form at once i.e evaluate the if
statement against every record in the table, not just the selected
one.

Is this possible or am I barking up the wrong tree?

thanks

Little Viking Girl

[kingston via AccessMonster.com]

Try using a query and a calculated field with your expression:

Field1: Location
Field2: Serial Number
Field3: IIF([Location]="position1" ,[Serial Number],"?")

This will evaluate every record in the table. Then you can use the query
results in the form instead of the table.

li************* *@hotmail.com wrote:

>Hi,

I posted a badly worded question last week so got no replies and am
still struggling to figure out the problem myself. I have a table
containing two fields, Location and Serial Number. I want to display
some of the serial number values on a form which will have a diagram
overlaid (to map out the "locations" ).

I have been trying to write an If statement in the controlsource
properties of the text boxes to say "if location=positi on1 then serial
number, else "?"

=iif ([Location]![Location]="position1" ,[Location]![SN],"?")

This only evaluates the record I am displaying and so would have to
toggle through to see each one in turn. But I want to be able to see
all the records displayed on the form at once i.e evaluate the if
statement against every record in the table, not just the selected
one.

Is this possible or am I barking up the wrong tree?

thanks

Little Viking Girl

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200703/1

[littlevikinggirl]

Thanks for the suggestion but the problem I have with using a query is
that there is more than one position I want to display, closer in fact
to 100. So I'd have to write a query for position1, another for
position2, and so on. I can't do it by prompting user input
([Location:] in criteria) as again I want to display multiple values,
not just the one for position1.

Maybe I need to describe my problem better? Say there is a floor plan
of an office and you want to display the serial number of each PC,
printer etc on the floor plan. They are all stored in the table but
with names like "desk1" and "desk2" as the locations and people may
not know where these positions relate to physically. So I want to have
numerous text boxes on the form, each one evaluating a different
location in the table in order to give a visual representation of the
locations.

Hope this makes more sense now and if I'm wrong about the query /
still not using it correctly then please let me know,

thanks again

little viking girl

[kingston via AccessMonster.com]

It sounds like you can present this information in a map with location names
and a corresponding table that lists the serial numbers at each location. I
think I understand what you're trying to achieve, but it seems impractical
because a desk may have no items or it could have 10 items. Is there enough
space to list 10 serial numbers? What happens if that list needs to grow
even further?

If you really want to do it your way, create an empty table of locations
(unique) and serial numbers. Then, use an update query to add each serial
number field by joining with your original table on the field location and
something like:

IIF(IsNull([Serial Number]),[OriginalSerialN umber],[Serial Number] & ", " &
[OriginalSerialN umber])

The result will be a list of locations and all of the serial numbers at that
location. Then, you can place a textbox at each location and draw the data
from the newly populated serial number field. You'll find more information
about doing this if you search for "transpose" or "concatenat e" in these
forums.
li************* *@hotmail.com wrote:

>Thanks for the suggestion but the problem I have with using a query is
that there is more than one position I want to display, closer in fact
to 100. So I'd have to write a query for position1, another for
position2, and so on. I can't do it by prompting user input
([Location:] in criteria) as again I want to display multiple values,
not just the one for position1.

Maybe I need to describe my problem better? Say there is a floor plan
of an office and you want to display the serial number of each PC,
printer etc on the floor plan. They are all stored in the table but
with names like "desk1" and "desk2" as the locations and people may
not know where these positions relate to physically. So I want to have
numerous text boxes on the form, each one evaluating a different
location in the table in order to give a visual representation of the
locations.

Hope this makes more sense now and if I'm wrong about the query /
still not using it correctly then please let me know,

thanks again

little viking girl

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200703/1

[littlevikinggirl]

For my situation there will only ever be one piece of hardware with a
unique serial number at each uniquely named location. Therefore
shouldn't I be able to draw from my original [Location] table
directly? I can understand the query is grouping together all serial
numbers at a particular location but I don't need to do that as there
are never 2 items in one spot.

I am struggling to understand how transpose and concatenate help in
this case but will continue to read through more posts. As for drawing
the data from the new table to display in the text box I am back to my
original problem of when I do an iif on the table it only reports the
value / evaluates it against the first record. Maybe I'm getting too
confused.

thanks again

little viking girl

[kingston via AccessMonster.com]

Your form will only present data from one record at a time so instead of
concatenating the data into unique locations, concatenate all of the data
into one record (probably a memo type):
Location1Serial Number1Location 2SerialNumber2L ocation3SerialN umber3... Then,
at each location on the map, you'd have a textbox that evaluates that string
by doing something like:

Mid([LongString],Instr([LongString],"Position11")+ 10,12)

This breaks apart the long string by looking for a specific position and
looking for the characters that follow it. In this example, I've assumed
that all serial numbers are 12 characters long. You can do this artificially
by padding the length. Each textbox would look for a different "Position"
and return that part of the string that follows that exact sequence of
characters. You could devise a way to prevent duplicates and shorten the
string, but the bottom line is that this has to be in one record if you don't
want to programmaticall y assign values. hth
li************* *@hotmail.com wrote:

>For my situation there will only ever be one piece of hardware with a
unique serial number at each uniquely named location. Therefore
shouldn't I be able to draw from my original [Location] table
directly? I can understand the query is grouping together all serial
numbers at a particular location but I don't need to do that as there
are never 2 items in one spot.

I am struggling to understand how transpose and concatenate help in
this case but will continue to read through more posts. As for drawing
the data from the new table to display in the text box I am back to my
original problem of when I do an iif on the table it only reports the
value / evaluates it against the first record. Maybe I'm getting too
confused.

thanks again

little viking girl

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200703/1