SELECT tblCalls.ColTime, tblCalls.colProblem,
tblCalls.colResolution from tblCalls, in dbCallLogs
WHERE tblCalls.colResolution from tblCalls,
in dbCallLogs where tblCalls.ColTime >= #12/1/2006# <= #3/2/2007#
ORDER BY tblCalls.ColTime DESC;
I want to know if this is right?
The in dbCallLogs syntax doesn't look right to me, and you have two FROM
clauses - you can only have one; likewise you have two WHERE clauses, the
first of which makes no sense to me. The IN clause looks wrong here: from the
Access Help on the FROM clause, subtopic IN:
To identify a source table:
FROM tableexpression IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}
A SELECT statement containing an IN clause has these parts:
Part Description
destination The name of the external table into which data is inserted.
tableexpression The name of the table or tables from which data is retrieved.
This argument can be a single table name, a saved query, or a compound
resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN.
path The full path for the directory or file containing table.
type The name of the database type used to create table if a database is not a
Microsoft Jet database (for example, dBASE III, dBASE IV, Paradox 3.x, or
Paradox 4.x).
Remarks
You can use IN to connect to only one external database at a time.
In some cases, the path argument refers to the directory containing the
database files. For example, when working with dBASE, Microsoft FoxPro®, or
Paradox database tables, the path argument specifies the directory containing
..dbf or .db files. The table file name is derived from the destination or
tableexpression argument.
To specify a non-Microsoft Jet database, append a semicolon (
to the name,
and enclose it in single (' ') or double (" ") quotation marks. For example,
either 'dBASE IV;' or "dBASE IV;" is acceptable.
You can also use the DATABASE reserved word to specify the external database.
For example, the following lines specify the same table:
.... FROM Table IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;];
.... FROM Table IN "C:\DBASE\DATA\SALES" "dBASE IV;"
Notes
For improved performance and ease of use, use a linked table instead of IN.
Note that literal dates like this are in mm/dd/yyyy American format - this
query will retrieve records from December 2006 through March 2 2007.
I notice in some queries the table name
before the column name is missing? Also when do you have to put in the
database name?
It's necessary to put the tablename only if the fieldname is ambiguous - say
you have two tables both of which have a field named ID. As noted above, you
only need the IN clause if you're linking to an external database, and even
there, it's better to use File... Get External Data... Link to link to it
instead.
Is it the same in Access as SQL Server?
Not exactly. There are minor (but annoying) dialect differences.
I want the most recent first.
That is what the ORDER BY clause does for you.
I want those records betweeb 12.1.06 and 3.01.07.
That you should be getting. Use the American / syntax if you're using literal
dates, or (probably better) use a Parameter query:
SELECT tblCalls.ColTime, tblCalls.colProblem, tblCalls.colResolution
FROM tblCalls
WHERE tblCalls.ColTime >= [Enter start date:]
AND tblCalls.ColTime <= [Enter end date:]
ORDER BY tblCalls.ColTime DESC;
I'd suggest starting with the query grid - select the table, enter the
criteria, and then switch to SQL view to show how Access constructs the query.
you're making the job a lot harder than it needs to be!
John W. Vinson [MVP]
