RE: Queries returning with missing info (plus asks query question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Let’s start with the simple query first. Try the below and see what happens.

PARAMETERS [Enter Shop] Text;
SELECT [T Master].[Site/Dept],
[T Master].Initiation,
[T Master].Seniority,
[T Master].[First],
[T Master].[Last],
[T Master].Dues,
[T Master].Title,
[T Master].Signedcard,
[T Master].paying,
[T Master].Steward,
[T Master].[Shop Contact],
[T Master].Address,
[T Master].City,
[T Master].State,
[T Master].ZIP,
[T Master].[Phone #],
[T Master].[Alternate #],
[T Master].[Cell #],
[T Master].[E-mail],
[T Master].[Umass Form],
[T Master].[Employee #],
[T Master].[NOTES]
FROM [T Master]
WHERE [T Master].Plant)=[Enter Shop]
AND [T Master].Termed < #1/1/1800#;

At first I thought that the First, Last, and NOTES field names could be a
reserved word problem. Note is a reserved work, but not NOTES. First and Last
are aggregate functions, but not reserved words. Just in case, I put square
brackets around them []. For more about reserved words see:
http://support.microsoft.com/kb/286335/

I’m assuming that [?shop] is a parameter. If so, it’s not declared in the
SQL statement. Plus I’m worried about the question mark being a problem. See
what is at the front of the SQL statement. If the Plant field is a number
instead of text, change it.

Last is the "1/1/1800". If Termed is a proper Date/Time field, it should be
#1/1/1800#. If Termed is a text field, you won’t get the results that you
wanted as "2/1/1799" is greater than "1/1/1800" in a text field.

If the SQL gives you problems still, remove the entire Where clause and the
Parameter clause at the top. Next hard code in a Plant in a Where clause.
Last put back in the Termed in the Where clause. Start from simple and build
back up until you find the problem.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"spark" wrote:

orignal:

SELECT [T Master].[Site/Dept], [T Master].Initiation, [T Master].Seniority,
[T Master].First, [T Master].Last, [T Master].Dues, [T Master].Title, [T
Master].Signedcard, [T Master].paying, [T Master].Steward, [T Master].[Shop
Contact], [T Master].Address, [T Master].City, [T Master].State, [T
Master].ZIP, [T Master].[Phone #], [T Master].[Alternate #], [T Master].[Cell
#], [T Master].[E-mail], [T Master].[Umass Form], [T shop_list].[Shop Name],
[T Master].[Employee #], [T Master].NOTES
FROM [T Master] LEFT JOIN [T shop_list] ON [T Master].Plant = [T
shop_list].Code
WHERE ((([T Master].Plant)=[?shop]) AND (([T Master].Termed)<"1/1/1800"));

changed to:

SELECT [T Master].[Site/Dept], [T Master].Initiation, [T Master].Seniority,
[T Master].First, [T Master].Last, [T Master].Dues, [T Master].Title, [T
Master].Signedcard, [T Master].paying, [T Master].Steward, [T Master].[Shop
Contact], [T Master].Address, [T Master].City, [T Master].State, [T
Master].ZIP, [T Master].[Phone #], [T Master].[Alternate #], [T Master].[Cell
#], [T Master].[E-mail], [T Master].[Umass Form], [T Master].[Employee #], [T
Master].NOTES
FROM [T Master]
WHERE ((([T Master].Plant)=[?shop]) AND (([T Master].Termed)<"1/1/1800"));



"Jerry Whittle" wrote:

Show us the SQL for the query that you just changed and the way it was before
you removed reference to the second table. That might tell us something.

Open the query in design view. Next go to View, SQL View and copy and past
it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

.

Relevant Pages

  • Re: formula help please
    ... primary and foreign keys are essentials in solving such problems. ... each of the tables are related to each other with each SQL statement. ... engine like Oracle), the program adds additional lines to the actual ... WHERE clause that describes how the tables are to be linked together. ...
    (comp.databases.oracle.misc)
  • Re: query based on form
    ... Typically it's only the WHERE clause of the query that changes so you can build that dynamically, patch it into the SQL statement, and assign it to the SQL property of the QueryDef you use for export. ... they can choose one Sales Rep, all cities, all states, all counties, just one type of business and one renewal month. ...
    (microsoft.public.access.queries)
  • Re: Date - Time Format
    ... This usually means that you are using a reserved keyword in your statement. ... See here for a list of reserved words: ... where sqlstatement represents the variable containing your sql statement. ... Doug wrote: ...
    (microsoft.public.inetserver.asp.db)
  • Re: Using SQL in Sub to fill box on mouseclick
    ... Your SQL statement is incorrect. ... The WHERE clause comes before the GROUP BY clause. ... > filled with a query telling me which people are scheduled to work a shift. ... > Private Sub Ctl8a_4pb_Click ...
    (microsoft.public.access.formscoding)
  • Re: WHERE clause question
    ... Carl Imthurn wrote: ... > Here's the WHERE clause of my SQL statement: ... I modified the WHERE clause as follows, ran it, and went ... How unique is the ProcedureID data in relation to the entire table? ...
    (microsoft.public.sqlserver.programming)