How do I limit which characters within a field are visible in a report?

[TechyGal]

Hi there,

I am hoping that someone may be able to assit me in sourcing a
solution to the following problems:

I have a table, in a database (the database is in SQL but I am using
Access as the front end), which contains a field called 'Room_ID' of
type 'nvarchar' set to a max of 20.

This 'Room_ID' is the primary key for the 'Room_Schecdule' table and
needs to consist of two different elements:

1). A Project ID - Required by us - e.g. 1, 2, 3, 4 etc...

2). A Room code - Specified by the client - e.g. AD01, AD02, AD03
etc...

The Room_ID currently looks like this: 1~AD01

1~ Signifies the Project id;

AD01 Siginfies the Room code;

I have set it like this in the hope that I can sort the records
firstly in order of projects and secondly in order of Room codes.

This brings me to my first question:

Is this the best way to achieve this and how do I sort in two levels?

The second issue I have is that I need to be able to generate a
report, which will be presented to the client.

On this report I need to display the Room_ID but I only want to show
the Room code element of this field!

Is it possible to 'Not show' the first two or three characters of a
record within this field?

If so, how can I achieve this?

I would really appreciate a 'dummys guide' as I am still just a baby
in the field of database development!

Many Thanks in anticipation!

TechyGal

 

[Jeff Boyce]

Rather than force two pieces of data (ProjectID, RoomCode) into a single
field, use two fields.

When you need to "see" both together (your "Room_ID"), use a query to
concatenate the values in the two fields.

When you have two fields, you can sort on either or both.

When you have two fields, you can "see" just one, if you wish.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[TechyGal]

Thanks for the response and I had already considered that but we
really do need to combine the two as one field because of the
implecations on so many aspects of the, immense, project database,
which is already established and functional!!

There must, surely, be a way of truncating the information displayed
to cut off the first few or last few characters within a field???

Thanks for yor help!!

TechyGal

 

[Jeff Boyce]

If you want to get the best use of Access' relationally-oriented features
and functions, you have to feed it well-normalized, relational data.
Putting more than one fact in a field violates good database design.

You've stated that you "have to" ... but not why. I'll suggest that you
don't "have to", and offered a way to keep the data elements separate (for
storage) but allow them to be combined (for forms and reports).

If you have no other options, take a look at the Left(), Right(), Mid() and
Instr() functions in Access HELP. They give you a way of pulling out a
portion of a value in a field. You will need to create a query to get the
field, then apply these functions to get the part you want. (or, you could
just pull the piece you want, if your data were ...<g>)

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[TechyGal]

That sounds like exactly what I need. Thank You!

I was thinking of using the mid(room_id, 3) to achieve the desired
results (assuming that I only need 2 characters for the project id)
but I am unsure how to create the query (is it just to pull all the
room_id's out?) I am also unsure where to enter this function?

Nor am I sure how to then get this info into the report!

Your help is so greatfully received!

With Thanks,

TechyGal )

 

[Paul Shapiro]

If you really have an immense db, the sooner you correct the design flaws
the less hassle (expensive/disruptive) it will be. A design that combines
two separate pieces of information into a single field will not work well in
a relational database. That's why it's a violation of first normal form-
because it's KNOWN to cause problems. You should have a Project table, Room
table and a RoomSchedule table. It sounds like you do have those tables.
RoomSchedule should contain two foreign key attributes, projectID and
roomID. As Jeff pointed out, it's trivial to combine the two values into a
single display field. Sorting by two fields is also trivial. Maintaining
referential integrity is automatic. With your current design, each of these
steps requires careful programming every single place the data is used.

The first db I developed was a similarly poorly normalized design. I
eventually HAD to redesign it because maintaining the poor design became
more work, and less reliable, than the redesign. The sooner you get a
suitable db design, the better. It only gets more expensive as development
continues on the poor design.
Paul Shapiro

 

[Bob Quintal]

That sounds like exactly what I need. Thank You!

I was thinking of using the mid(room_id, 3) to achieve the desired
results (assuming that I only need 2 characters for the project
id) but I am unsure how to create the query (is it just to pull
all the room_id's out?) I am also unsure where to enter this
function?

Nor am I sure how to then get this info into the report!

Your help is so greatfully received!

With Thanks,

TechyGal )

There are two ways to get this into your report
1) In query design,just put mid(room_id, 3) in the top row of the
grid. Access will add Expr1: to the left of it, change that to a
meaningful name for your calculated column, then add al the other
fields you want in the report, and build the reprt on that.

2) build the report on the table. once it's working with the full
field, change the control source property for that fiield from
[room_id] to =mid([Room_Schecdule].[room_id], 3)
BTW is Room_Schecdule really the name of your field, or is it
Room_Schedule with a transcription error? Fix my code as required.

 

[TechyGal]

Hi Bob,

Thank you so much for your help! That works perfectly!

It was a typo, it is meant to be 'Room_Schedule' Good job your on the
ball!

I entered the function into the control source on the report, as this
was already setup, and only had to enter
=Mid([Room_Id],3) and it achieves exactly the result I was after!

Cannot thank you enough! Easy when you know how eh!!

from TechyGal

That sounds like exactly what I need. Thank You!

I was thinking of using the mid(room_id, 3) to achieve the desired
results (assuming that I only need 2 characters for the project
id) but I am unsure how to create the query (is it just to pull
all the room_id's out?) I am also unsure where to enter this
function?

Nor am I sure how to then get this info into the report!

Your help is so greatfully received!

With Thanks,

TechyGal )

There are two ways to get this into your report
1) In query design,just put mid(room_id, 3) in the top row of the
grid. Access will add Expr1: to the left of it, change that to a
meaningful name for your calculated column, then add al the other
fields you want in the report, and build the reprt on that.

2) build the report on the table. once it's working with the full
field, change the control source property for that fiield from
[room_id] to =mid([Room_Schecdule].[room_id], 3)
BTW is Room_Schecdule really the name of your field, or is it
Room_Schedule with a transcription error? Fix my code as required.

 

[TechyGal]

To add a further query to this can you please tell me if it is
possible to display the text up until a certain character is reached,
rather than knowing and specifying the exact length of the characters
to include!

This would be really useful if the project code aspect of this field
was entered at the end of the Room code rather than at the begining.

E.g. AD01~1

I would want to display the text upto, but not including, the
character '~' and then stop.

This would allow the Room Code 'AD0112-1' to grow without affecting
the cut off point of the =mid([Room_Id],?) fuction.

I realise that this is not effective database design and this is
something that I am going to discuss with managment (I only started
this job a month ago, so need to tread careful) and suggest looking
into for future redevlopment of the database, but right now I am
looking for a quick fix solution.

Many Thanks!!

TechyGal

That sounds like exactly what I need. Thank You!

I was thinking of using the mid(room_id, 3) to achieve the desired
results (assuming that I only need 2 characters for the project
id) but I am unsure how to create the query (is it just to pull
all the room_id's out?) I am also unsure where to enter this
function?

Nor am I sure how to then get this info into the report!

Your help is so greatfully received!

With Thanks,

TechyGal )

There are two ways to get this into your report
1) In query design,just put mid(room_id, 3) in the top row of the
grid. Access will add Expr1: to the left of it, change that to a
meaningful name for your calculated column, then add al the other
fields you want in the report, and build the reprt on that.

2) build the report on the table. once it's working with the full
field, change the control source property for that fiield from
[room_id] to =mid([Room_Schecdule].[room_id], 3)
BTW is Room_Schecdule really the name of your field, or is it
Room_Schedule with a transcription error? Fix my code as required.

 

[Bob Quintal]

To add a further query to this can you please tell me if it is
possible to display the text up until a certain character is
reached, rather than knowing and specifying the exact length of
the characters to include!

This would be really useful if the project code aspect of this
field was entered at the end of the Room code rather than at the
begining.

E.g. AD01~1

I would want to display the text upto, but not including, the
character '~' and then stop.

This would allow the Room Code 'AD0112-1' to grow without
affecting the cut off point of the =mid([Room_Id],?) fuction.

I realise that this is not effective database design and this is
something that I am going to discuss with managment (I only
started this job a month ago, so need to tread careful) and
suggest looking into for future redevlopment of the database, but
right now I am looking for a quick fix solution.

Many Thanks!!

TechyGal

Instr() returns the number of characters from the start of string1
that it found string2
instr("AD0112~1","~") will return 7

So you need the first (7-1) characters, but if there is no tilde
instr() returns a zero but left() or mid() doesn't like a length of
-1 characters, so we need to test for a 0 length..
Then you can use the left or mid() functions to cut out the parts
you need.

Because of the complexity, I suggest that instead of putting all
that in the textbox, build a function and call the function from the
textbox.

Public Function fRoomFromID(TheField as variant) as variant
On error GoTo err_fRoomFromID
Dim iTildePosition as integer

if len(trim(TheField)) & vbnullstring =0 then GoTo Exit_fRoomFromID
iTildePosition = instr("AD0112~1","~")
If iTildePosition =0 then
fRoomFromID = TheField
else
fRoomFromID = left(TheField,iTildePosition)
' or mid((TheField, 1, iTildePosition)
end if

Exit_fRoomFromID:
exit function.

Err_fRoomFromID:
' to be filled in as required.
Resume Exit_fRoomFromID

end function