I have some difficult to show soil description for each sample in Sample Table from Lithology Table. Here are what I did:
<<Sql(SELECT (LITHOLOGY.DESCRIPTION) _ FROM LITHOLOGY _ WHERE (<<Sample.Depth>> < <<Lithology.Bottom>>) AND _(<<Sample.depth>> >= <<lithology.depth>>) _)>>
But this doesn't work. Please help me check what wrong with this.
Thanks a lot
Shan
Just looking at the SQL, there are some problems:
should be:
<<Sql(SELECT [LITHOLOGY.DESCRIPTION] _ FROM [LITHOLOGY] _ WHERE [LITHOLOGY.PointID] = "<<PointID>>" _ AND [LITHOLOGY.Bottom] > <<SAMPLE.Depth>> _ AND [LITHOLOGY.Depth] <= <<SAMPLE.Depth>>_)>>
The [ ] delimeters for SQL tables/fields and the space between the continuation underscore and the end of each internal line are crucial. The gINT data we are comparing should be delimited with the gINT token, << >>. We also need to add the condition that the PointID is the current one we are reporting.
However, I'm not sure even that rewritten code will work as you want, to select the description that covers a range beginning before the sample top Depth and ending after the sample Bottom depth. How can you test it? NEAT TRICK: Take the arguments out of the <<Sql()>> function 'wrapper" and remove the gINT-token delimited variables, substituting explicit values for the variables, and execute it in INPUT as a query (Tools menu Query option). Note that text data still need to be delimited with double-quotes:
SELECT [LITHOLOGY.DESCRIPTION] FROM [LITHOLOGY] WHERE [LITHOLOGY.PointID] = "12-3" AND [LITHOLOGY.Bottom] > 0.5 AND [LITHOLOGY.Depth] <= 0.5
I am still not certain that the expression will work as you wish, but it is closer. If it is still not working in your report, you may want to think about how to more-completely specify the SAMPLE table values you are using as a comparison.
Thanks, Pat! Looks like yours works.