Multikey help needed!

[darrel]

G'day All,

I'm new to the idea of multikeys and have read all the threads here but still need some help.

I have a database for booking rooms which uses a multikey based on these fields:

Date & room_id & time

In the time field I am using check boxes for each timeslot.

I want to use this multikey to show the bookings in a portal in another database. The problem is I can't create a calculation that gives me the multikey for room bookings

Eg

date - room - time

20030621 - 01 - 7

20030621 - 01 - 8

20030621 - 01 - 9

I have tried this:

[color:"red"] Substitute(time & " ", " ", date & room_id & time & " " )

But I can't get multiple entries separated by a carriage return.

I presume this is how multikeys work ?

Any help greatly appreciated!

Cheers

Darrel

[cjaeger]

1. if you omit time, you don't need a multikey

date& room_id <-> date & room_id

2. i miss the hyphens in the calc

3. date is actually =year(date) & right("00" & month(date);2) && right("00" & day(date);2)

4.

the right calc would be:

substitue (" "& time", " ","[par] "& year(date) & right("00" & month(date),2) && right("00" & day(date),2) & "-" & room_id & "-")

[darrel]

Thanks Christian,

I've given it a try but all I seem to get from the calculation is a single line with the values.

For example, if I click the time field checkboxes for 8,9, and 10 o'clock all I get is 8910 in the substitution field. There is no date & room_ID information.

Sorry if I'm missing something obvious but it's still not working. Any more thoughts?

Thanks in advance

Darrel

[Ugo DI LUCA]

If your "time"is a checkbox value list, then the field would look like :

Time =

7

8

9

If you concanation = 20030603 - 07

Then, you should have a Multikey =

c_Multikey = Substitute(Substitute(Time &" ", " ", "-"& Concanation& "par."), "par."," ")

[darrel]

Sorry for sounding stupid ... but it must be 'my understanding' of the multi key principle

Your calculation works but I still get the time (in this case 8, 9 and 10 o'clock) as 8910-date & room_id

I thought a multikey for these three time slots would look like this

8 - date & room_id

9 - date & room_id

10 - date & room_id

So that the there would be three entries/times in this one field separated by a carriage return

I must be missing something in how it all works

Thanks for your comments and patience..

Cheers

Darrel

[cjaeger]

set the result of the calc to TEXT not NUMBER !

[cjaeger]

also make sure, time is a text field.

using my calc, it should give you a leading return + the result you want. if you want to parse out the leading return for cosmetic reason, try Middle(Substitute( ....), 2, 999999).

for some reason, the shortcut [ par ] for does only work if enclosed by blanks . ..

[darrel]

Brilliant It works

Thanks heaps for your expert knowledge ... very, very much appreciated!

Cheers

Darrel