Lookup taking none equal value

[Mark Burton]

I am a novice FMP user building a simple film production database. So far I have most of the database working how I would like. It has tables for CODEBOOK, SHOTCARD, IMAGES, TAPES and SCENES.

One function of the database which is not working as it should is the CODEBOOK image lookup. In the CODEBOOK every record has a 4 digit value like such as 0001 (this relates to the shot number and by the end of the shoot we will have numbers up to 0700 or so). e.g Shooting slate 43 would have 0043 in this field.

The IMAGES table contains a JPG from that camera setup and has the same 4 digit values.

Since the CODEBOOK can contain any number of records with the same 4 digit number (multiple takes from the same camera setup) I wanted to use the IMAGES table to store the JPG's and have the CODEBOOK pull the corresponding image in for each record.

It works fine for these straightforward numbers, but I also have records which have an 'X' in the 4 digit number e.g. X002. Equally the IMAGES table has this entry, but the Lookup goes to 0002 for this type of record.

How do I get the Lookup to respect 'X002' over '0002'?

Many thanks for any help.

Mark

[Søren Dyhr]

Since X isn't an integer, are you bound to turn your key values into text, to get rid of this jiffy'ish kind of behaviour.

One more thing is that you then would need a prefixing of the numbers all to include accordin number of leading zeros, so if you enter 43 would the keyvalue turn 0043.

This is done this way Right("0000" & theEntry;4)

Finally should I dissuade you from Lookups, and turn you to the tunneling of data between tables that are related. Why should you have the image in two locations when all it takes is referencing??

--sd

[Mark Burton]

Thanks Søren, that was it!

The looked up field in the IMAGES table was a calculation (taking the imported JPG file name and removing .jpg), but it was set to return a Number as the result; changing this to return Text, fixes the issue.

Thanks for the quick and very helpful reply, much appreciated.

Mark