SQL 2

[villica]

I have this query under the record source of a report, but I get an error message saying that it can't find the table or query.&quot;Select * from forms!frmlogdate!logfile where forms!frmlogdate!logfile!Initials = &quot; & Forms![frmlogdate]![INITIALS]<br>
<br>
Any ideas anyone. <p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>

 

[elizabeth]

For starters, a select stmt selects records from a table, and anything named Forms!... is not a table but a form.

 

[elizabeth]

villica, I think you need to get a good overview of SQL before you can figure out how to build the sort of complex thing you've been posting all these questions about. Here's one that you may find useful:<br>
<br>
<A HREF="

http://www.fmsinc.com/tpapers/queries/index.html"

TARGET="_new">

http://www.fmsinc.com/tpapers/queries/index.html</A><br>

<br>
Hope this helps!

 

[DougP]

villica,<br>
You need to understand how Access was designed to work<br>
1st have your data in a table<br>
2nd make a form to enter data in a table or tables<br>
3rd make a query to find specific data<br>
4th make a report based on a query<br>
<br>
OK so basing a query on a report is really doing it backwards.<br>
so to answer your question:<br>
Access is telling you that there is no Table in your SQL statement. You can't put in a record source from a report or anything else it must be explicitly a table or query.<br>
As Elizabeth suggested you need to understand SQL better<br>
1st the word Select is used most of the time to &quot;Select&quot; certain fields in your Table(s)<br>
2nd put in the field names <br>
Example = &quot;Select Fname,Lname,City<br>
BTW the Asterisk (*) means all fields in the table(s)<br>
3rd from which table or tables or queries notice the &quot;From&quot; word is added before your table<br>
Example = &quot;Select Fname,Lname,City From Contacts<br>
4th your specific parameters &quot;Where&quot;<br>
Example = &quot;Select Fname,Lname,City From Contacts Where Fname = &quot;John&quot;;<br>
Now this is a very simple query but all queries must follow the same syntax.<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[villica]

Thank you Elizabeth for the title of the book and thank you Doug for the explanations. I do know the difference between a form and table maybe I should explain what I was trying to do in the code above. I am not selecting anything from a form I am selecting a everything from the table they pick on a combo box<br>
<br>
.&quot;Select * from forms!frmlogdate!logfile where forms!frmlogdate!logfile!Initials = &quot; & Forms![frmlogdate]![INITIALS]<br>
<br>
name of form frmlogdate<br>
name of combo box logfile(list all the tables in my database<br>
name of one of the fields in the logfile is initials<br>
name of another combo box on the same form initials.<br>
<br>
I was just comparing the initials within the table I pick to initials I pick on the second combo box. <br>
I will try to read more about sql and find out what the syntax should be or maybe try to put this code under the open report event.<br>
<br>
Thank you very much both of you for the help and suggestions. <br>
<p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>

 

[DougP]

Dim db As Database<br>
Dim rst As Recordset<br>
Dim SQL As String<br>
Set db = CurrentDb<br>
<br>
' this is row source of combobox 2<br>
SQL = &quot;SELECT * FROM [Customers] Where [ID] = &quot; & Me!Combo1.Text<br>
Set rst1 = db.OpenRecordset(SQL1)<br>
-------------------------------------------<br>
<br>
Unfortunatley you can not put the Row source of anything, a combo box or whatever into the middle of a SQL statement. It just will not work.<br>
It has to be explictly a Table and a valid field in that tabel not a field embedded in a combox either.<br>
But you can find the values in a combo box by using the Exact same SQL code it wrote when you created the combo box.<br>
<br>
So copy the SQL that is in the Combo box<br>
i.e. go to its properties and copy the entire SQL statement and paste it over the top of mine above<br>
OK<br>
Now whats returned in the Rst variable is your answer<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[elizabeth]

I'm starting to understand you now. This is only a start because I've never tried substituting a form field this way...The &quot; <b><font color=red>!</b></font> &quot; should be a &quot; <b><font color=red>.</b></font> &quot;<br>
<br>
Select * from forms!frmlogdate!logfile where forms!frmlogdate!logfile<b><font color=red>!</b></font>Initials = <br>
Forms![frmlogdate]![INITIALS]<br>
<br>
Select * from forms!frmlogdate!logfile where forms!frmlogdate!logfile<b><font color=red>.</b></font>Initials = <br>
Forms![frmlogdate]![INITIALS]<br>
<br>
... if logfile is the name of the form's field that holds the exact name of the table you want, and Initials is the name of the field in each table and INITIALS is the name of the form's field that holds the value you are trying to match in the Initials field. This is a pretty interesting problem!<br>
<br>
If Access can't figure this out, maybe you could try to build a SQL string, as in:<br>
strSQL = &quot;Select * from &quot; & forms!frmlogdate!logfile & &quot; where &quot; etc.

 

[JimConrad]

Sorry, I know this is off topic but...<br>
<br>
How did you get the ! to be red in your post? <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>

 

[elizabeth]

like this but <i>no</i> spaces at all:<br>
<br>
[ red ] whatever [ / red ]<br>
<br>
italic = i<br>
bold = b<br>
<br>
there are some more but these are the ones i remember because I us 'em a lot...

 

[JimHorton]

villica,<br>
Are you trying to dynamically choose the table name in sql?<br>
If so, do this (watch the &'s and quotes carefully):<br>
<br>
Sub Report_Open(Cancel As Integer)<br>
dim sq as string<br>
sq = &quot;SELECT * from &quot; & forms!frmlogdate!logfile & &quot; where &quot; & forms!frmlogdate!logfile & &quot;!Initials = &quot;&quot;&quot; & Forms![frmlogdate]![INITIALS] & &quot;&quot;&quot;&quot;<br>
me.recordsource = sq<br>
End sub<br>
<br>
Now, the forms!frmlogdate!logfile is, I'm assuming a table's name? Assuming you have fields already set up in the report, and these different log tables you spoke of earlier have similar field names (that is, identical field names), this will work. If there are differences, then you can get creative using CreateReportControl. This is very useful and you can create excellent reports on-the-fly using this. Depending on the complexity of the report this can be easy or tedious.<br>
--Jim

 

[villica]

Elizabeth and Doug thank you so much for all the suggestions you gave me. Jim the code you gave me it works wonderfull. You gave exactly the syntax that I needed which is what I was having problems to begin with. Thanks a million to all of you. I am sorry for all the posting regarding this problem. It was challege. <p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>