Hi AJ,
You are so close to get this working. Based on what you just say, it is a
number ID field. You form textbox has to be 1 of the valid number ID field
from your table (I am sure it is right now).
Couple thing to try.
Create a simpler query using the same form. Type a number in Text14. With
the Form open, try the following.
Simple Query:
SELECT Table1.[Team Leader] From Table1 Where Table1.[Team Leader] =
Forms![frmQueryTeamLeaderreport]![Text14];
If it does not return the same number as Text14, try the query below.
SELECT Table1.[Team Leader] From Table1 Where Table1.[Team Leader] =
Val(Forms![frmQueryTeamLeaderreport]![Text14]);
One of them or both of them should work. I do this kind of stuff all the
time. I just can't believe it is not working.
If it is till not working, I can give you one of my junk email address. You
can sent me the zipped up version of your Access (Just sample data). I can
take it look at it.
:
The [Team Leader] when entered by a user is pulled from another table. This
table is the ID # table where the fields are ID (primary Key), Name, ID #,
Email. This is the same table I'm using to chose the name from for Combo9 or
Text14. I went in and changed [Team Leader] to be text as it was number when
I went in. Even with that change I'm not getting what I'm looking for. For
whatever reason I can enter a # into the query and it runs perfectly and when
it displays the actual text is displayed. But, if I type in a name it pulls
back nothing. I think I may have to find another way to get what I want here
since nothing I"m doing is fixing the problem. Thanks so much for all your
help. You got me further than anyone else.
:
Hi AJ,
Your [Team Leader] is coming from table "Table1". The definition is in
"Table1". If the "[Team Leader]" is defined as number or text, but the
actual value is "1" (1), "2" (2), ... etc. Then your form field "[Text14]"
has to show 1, 2, .... etc.
If the definition is text and the actual value is "JL", "AJ", "ABC", "DEF",
.... etc. Then your form field "[Text14]" has to show "JL", "AJ", "ABC",
"DEF", .... etc.
I hope this will resolve your problem.
:
Here is the SQL view of the query (learn something new everyday! - thanks)
SELECT Table1.[Team Leader], Table1.Closed, Table1.[QI Type], Table1.[QI #],
Table1.[Part #], Table1.[Batch Code], Table1.[MO#], Table1.Returned,
Table1.[Corrective Actions Accepted by QA], Table1.[Corrective Actions
Complete and Sent to QA], Table1.[Rejection Reason], Table1.[Rejection Type],
Table1.[Resources Needed 1], Table1.[Resources Needed 2], Table1.[Resources
Needed 3], Table1.[Resources Needed 4], Table1.[Resources Needed 5],
Table1.[Issue Date]
FROM Table1
WHERE (((Table1.[Team
Leader])=[Forms]![frmQueryforTeamLeaderreport]![Text14]));
Team Leader was number field. I changed it to text. Now, I can type in
the name and I don't get an error, but I don't get the records either.
I'm trying to figure out how to set the Text14 to a text and see if that
works, not really sure how (though I'm certain it is something obvious I'm
missing)
thanks again.
:
Hi AJ,
Ok, it looks like you have everything in the right place.
Questions:
1) Is the "[Team Leader]" a number (ID) or name string?
2) Is the "Text14" textbox on the form a number or name string?
If they are name string, then make sure the case are matching and the table
actually have that name exist.
If they are number, you can try
"Val([Forms]![frmQueryforTeamLeaderreport]![Text14]) to force to be a number.
To paste the whole SQL statement, in Query Tab, hit "Design" to open the
query in question. Then under "View", there should have "SQL View". Then
you will be able to see the whole SQL query.
:
The Form Name is: frmQueryTeamLeaderreport
The Query is: Open QI Query
The Sql Statement as copied directy from the criteria section under teh
filed Team Leader is: [Forms]![frmQueryforTeamLeaderreport]![Text14]
Thanks for helping me.
:
Hi AJ,
You can copy the SQL from the Query Design. Under the same icon or pull
down, there is a SQL. That will give you the SQL statement.
I also need to know the Form name and Query name. My guess is that you get
those mixed up. That is why I need to see it and fix it for you.
:
Yes, the form was set to me.visible after Update. I went and removed that
and tried and still got the same error though.
How do i post the query? COuld I zip up the database and send it to you?
Thanks for your help!
:
Hi AJ,
Was the form open with the value in the textbox when you run your query?
:
I tried this. I'm still getting the same error when I run the query. I set
up Text 14 and it works great on the form, it shows the name. I put this in
the criteria for the query
the field is Team Leader the criteria is
[form]![frmQueryforTeamLeader]![Text14]
Again, it still gives me the same error I had before.
thanks
:
Hi AJ,
To answer your question.
You have to create another textbox (hidden or visible) to store your value
in it and then use it to do your query.
My best guess is that the "Column(?)" only exist in memory (when the combo
box is active). That is the reason that you can not reference it.
Hope this helps.
:
What is the syntax to get a query to recognize the second column from a Combo
box?
In the criteria section of the query from design view I have :
[Forms]![frmQueryforTeamLeaderreport]![Combo9].Column(1)
The query gives me a "unidentified function" error when I run it with this
in it. If I just leave the Coulmn(1) off then the query doesn't run
correctly in that it will only accept values equal to the first column in my
Combo. Note that Combo9 allows you to shoose from a table that has ID and
Name as the columns. ID is just the number and the primary Key. The Combo
properly allows me t select teh name. However, when I type in the name when
the query prompts for criteria I get errors. It wants the number. I need to
make it accept the name.
thanks for any help anyone can give me, and i am very appreciate to all of
you who have gotten me this far.
aj
