DataEnvironment and SQL

[MikeZ]

I have a DataEnvironment set up with one command and on child command if I run a report with no sql it works fine it takes data from both ,I would like to set up an sql but I am having a problem getting the results I want for example: I have 2 tables in my database "color" and "shapes"
and DataEnvironment is set up as Color as command and shapes as child command I want to select the color and have the shape as well on a report "red" "triangle"
I tried doing the following"
DataEnvironment1.commands("color").commandtext= _
"SELECT * FROM shapes,colors WHERE color= '"&Search$&"'"

but this generates an error "The specified field "color" could refer to more than one table specifed in your FROM clause" The color field is what I used to create the child command with...any ideas?

[JHausmann]

It sounds like the field "color" belongs to both tables. If this is so, the parser is telling you it needs help to tell it which field to use. You have another prblem in that you're collecting data from two tables but not joining them. This will lead to you getting a ton of data, redundant, that you won't be expecting. Assuming that "color" is a field common to both tables, the following query is more what you need:


"SELECT * FROM shapes a , colors b WHERE a.color=b.color and b.color= '"&Search$&"'"

[MikeZ]

I did what you suggested but but when I ran the report an error came up "Datafield color.color not found" I removed the the sql and the report printed the shapes and colors ..all of them what am I doing wrong?