Questions on format of Select statement

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?  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?  Is it the same in Access as SQL Server?  I want the most 
recent first.  I want those records betweeb 12.1.06 and 3.01.07.

thanks,
0
Utf
3/21/2007 8:30:07 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
551 Views

Similar Articles

[PageSpeed] 52

On Wed, 21 Mar 2007 13:30:07 -0700, Janis <Janis@discussions.microsoft.com>
wrote:

>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]
0
John
3/21/2007 10:17:13 PM
On Mar 21, 10:17 pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

> use a Parameter query:
>
> SELECT tblCalls.ColTime, tblCalls.colProblem, tblCalls.colResolution
> FROM tblCalls
> WHERE  tblCalls.ColTime >= [Enter startdate:]
> AND tblCalls.ColTime <= [Enter enddate:]
> ORDER BY tblCalls.ColTime DESC;

Alternatively, being closer to the OP's original attempt (i.e. more
intuitive):

WHERE tblCalls.ColTime BETWEEN [Enter startdate:] AND [Enter enddate:]

> [Quoted from Help] You can use IN to connect to only one external database at atime.

I wonder what "at a time" means here? Using more than one mdb in a
query certainly works e.g.

SELECT C1.dt
FROM [MS Access;DATABASE=C:\DB1.mdb;].Calendar AS C1,
[MS Access;DATABASE= C:\DB1.mdb;].Calendar AS C2
WHERE C1.dt = C2.dt;

I know it is only possible to use one mdw (i.e. the current one)
across embedded connections.

Jamie.

--


0
Jamie
3/22/2007 11:18:03 AM
Reply:

Similar Artilces:

Mac office 2004 install question
Where does the normal install normally place the "Microsoft User Data" folder that contains, among other things, the Entourage message database? Thanks, -gb In username/documents. In fact, Office requires the MUD folder be there, you have to trick it to put it elsewhere. On 2/18/05 4:34 PM, "Geoff Brandt" wrote: > Where does the normal install normally place the "Microsoft User Data" > folder that contains, among other things, the Entourage message database? > > Thanks, > > -gb > -- Daiya Mitchell, MVP Mac/Word Word FAQ: http://ww...

SelectCase statement
I'm trying to calculate the business week by looking at logdate. Below is the entire syntax for the select query with the case statement indented for easier reading: SELECT tblINQ.LOGDATE, tblINQ.Ticket, tblINQ.TicketDTM, tblINQ.AltKeyID, tblINQ.OrigParentChild, tblINQ.SevCd, tblINQ.EDIType, tblINQ.QueOwnerDesc, tblINQ.Origination, tblINQ.ServiceType, tblINQ.CategoryType, tblINQ.TSCType, tblINQ.LoggedByDivCd, tblINQ.LoggedByDeptCd, tblINQ.LoggedByID, tblINQ.OwnerDivNbr, tblINQ.OwnerDeptCd, tblINQ.OwnerID, tblINQ.RespTech, tblINQ.SupportArea, tblINQ.StatusCd, tblINQ.StatusTyp...

How do I save a Word doc to the epub format?
Is there a download for this? Try Google: epub format... Roughly 1,000,000 hits :-) Regards |:>) Bob Jones [MVP] Office:Mac On 1/23/10 3:03 PM, in article 114A1769-E1F2-4EE1-A44E-38E2643F7699@microsoft.com, "Arnold" <Arnold@discussions.microsoft.com> wrote: > Is there a download for this? ...

Windows Mail; delete selected items
When I select items in a box and try to delete them, I get the message: (Er is een onbekende fout opgetreden) An unknown error occurred. -- Kees Try these options. How to Remove a Stuck Undeletable Message in Windows Mail http://www.vistax64.com/tutorials/62560-windows-mail-problems.html WMUtil http://www.oehelp.com/WMUtil/Default.aspx -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Kees" <Kees@discussions.microsoft.com> wrote in message news:F1DDCE31-D8A0-46AF-B2C7-09F69CC55A42@microsoft.com... > > When I select it...

Turning off all Automatic Formatting in Excel 2003
Hi Is there any way of turning off all automatic formatting in Excel 2003? For example Excel automatically changes strings such as 1-12 in a CSV file to 1-Dec, 10-10-2005 to 10/10/2005 and strings of numbers to an exponential representation. These changes are preserved when saving the file and thus corrupting it, preventing any other applications from reading it. I know you can import CSVs changing all the fields to text using the data import option, however reopening it and saving causes Excel to make the same changes again. Any suggestions would be really appreciated as this is caus...

CRM Integration requires NTFS formatted hard drive
Hi, When I'm trying to instal MSCRM Integration with Great Plains v. 1.2 results in this error -------------------------------------------------------------------------------------- Microsoft CRM Integration requires NTFS formatted hard drive. Please resolve the issue and re-start setup -------------------------------------------------------------------------------------- The integration server is running on Win 2000 Server SP4. The system partition and hard drive already using NTFS file system. Any suggestions? Thanx, David ...

AfxThread question
I have a piece of code: .... m_data.sThreadType = _T("Client1"); CWinThread* clientThread1 = AfxBeginThread(ClientThreadFunction,(LPVOID)&m_data); ::Sleep(1); m_data.sThreadType = _T("Client2"); CWinThread* clientThread2 = AfxBeginThread(ClientThreadFunction,(LPVOID)&m_data); .... UINT CDialog_MFC_WorkThread::ClientThreadFunction(LPVOID pParam) { ThreadData* pData = (ThreadData*) pParam; CString cs = pData->sThreadType; if(cs == _T("Client1")) m_CListBox_Client1.AddString(_T("in ClientThreadFunction()")); else if(cs == _T("Client...

SQL0518
Hi, I have numerous pivot table spreadsheets all taking data directly fro our IBM AS/400 via Client Access ODBC connection. With just one of the workbooks i cannot get back into query manager t edit the query (although data will still refresh ok !) Each time i try to edit i recieve : [IBM][Client Access Express ODBC Driver (32-bit)][DB/400 SQL]SQL0518 Prepared statement 0000000064 not found. Anyone have any idea what causes this and how to get round the proble ?? Many thank -- Message posted from http://www.ExcelForum.com ...

Recovery Partition Question
A friend had a system crash and resinstalled windows 7 using the factory disk. They selected the option to create a recovery partition. All was well but they were not paying attention after it was created and when they went to install programs and save programs they saved and installed them on the recovery partition. It is now almost full. Can he remove or delete files he does not need ? If so what should remain? Also is there a way to block this from happening in the future. Thanks Nash55 ...

Excel 2002 annoyance
Greetings List I am using Excel XP amd Access XP I regularly run queries in Access and cut and paste the results into a new Excel spreadsheet. The Access data is usually formatted numeric, fixed, zero decimal places. Once pasted into Excel, it gains 2 decimal places and I get the little green triangle in each cell inviting me to convert to numeric of error check etc. Can anyone think of a way to retain the numeric formatting from the MS Access data? Regards George ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet mess...

Stop Chart Style (Format) Update
Hi TWIMC, I'm using Excel 2000 and I have a pivot table that aggregates data based on REGION, COUNTRY, ZONE etc etc. Once I have set up the chart they way that I would like it to look, if I then make a change to the selection using the pivot chart buttons, e.g. I select a country and the chart re-formats itself back to what appears to be the default style. I've set my chart style to the default but it still doesn't keep my chart style. What I want is that the chart style remains the same but the new values are just plotted onto the chart. I'm sure there is an option to turn ...

CTreeCtrl question
I'm starting a project that will display a lots of data on CTreeCtrl. I want to allow the user to see the same data in different views. Ideally I would want to have a one CTreeCtrl with all the data (number of branches) and when I need to show just a subset of it I would want to create a new CTreeCtrl. However I would want all the Items in the second Tree to point to the ones that reside in the main TreeCtrl. Each of the items will have pointer to correspoinding underlying object and I would want to share those as well. I know that I can pass the root of the HTREEIETM and then reconstruc...

SMTP Question
Good morning, I have an exchange 2003 server in one of our clients office which happens to be a law firm. They are working on a class-action law suit which requires they send out very large attachments to many users at a time. this has caused internet slowdowns b/c the emails are stacking-up in the queue and taking a very long time to procees; thus slowing down the internet to a crawl. ( we have a standard dsl network) is there a way to modify the default the stmp settings, so during the times these messages are being sent, it does not use all my internet bandwidth. Are there any good...

Format Problem #2
Hi how can i save an excel file to text file with out chainging the format. ie Excel file: adf 12 a 1 when i change this to text file the alighment changes like adf 12 a 1 so how can i solve this problem. i tryied many wayes and nothing work out properly. so if someone know this , plzz do help me. manikandan Try saving it as a "Formatted Text (Space delimited) (*.prn)" file. You may have to widen columns to make it look right. (And I like to change the font to Courier New--a nice non-proportional font. Then I can see how it'll line up.) manikandan wrote: > >...

Report Formatting & Writting
Hi Everyone, 1. I am just gettign to grips with writting .qrp reports in RMS and wondered if anyone knows if there is a WYSIWYG editor available to help speed up developement? 2. I want to be able to hightlight figures on reports in red if they are negative - is this possible? Tia 1) Nope, no WYSIWG editor. 2) No, you can't set the color, but you can use a standard VB Format String in the "ColFormat" field of the column definition. Here's teh MSDN Reference on number formats: http://msdn2.microsoft.com/en-us/library/4fb56f4y.aspx Glenn Adams Tiber Creek Consulti...

...many questions...
...First note...excuse me for my English :-) In my Exchange implementation i have see any problem... and want to ask you if you ..have solution.. 1) any Company message ask me if is possible sign all out-message with Standard sign... if it's possible personalizing sign with %username% or other AD Info.. i have see about event sink... anyone know simple site about event-sink use or where watching ? 2) Right on Public folder!! ...I need to move a mail in Public folder to sub-folder. To make this i give to user Delete rights and Create Right.... but! this solution is wrong for m...

GetSystemMetrics32(n) Question
I have found many articles on the web about the use of GetSystemMetrics(n) to determine the screen resolution by using n=0 and n=1. However, running the code: Declare Function GetSystemMetrics32 Lib "user32" _ Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long Sub x() Dim i As Long For i = 1 To 300 Range("A" & i) = i - 1 Range("B" & i) = GetSystemMetrics32(i - 1) Next i End Sub Seems to be giving meaningful values for n=0-84. All n>84 return 0. I found that n=80 gives the number of monitors. Is there...

Function Argument Question
I'm having a brain freeze and cannot come up with the formula to express the following: If A1 is greater than 0 but less than 3, return 50, If A1 is greater than 3 but less than 6, return 100, If A1 is great than 6, return 200. I was looking for a "between" function like in formatting, but I don't think that is how it's done. Thanks in advance for any help! Hi War, Try this: =if(A1>=6,200,if(A1>3,200,50)) -- Hope this helps "WAR" wrote: > I'm having a brain freeze and cannot come up with the formula to expre...

Web Query Question
I'm updating sports scores in excel 2002, The first set of Columns Are the Team Names and the Final Score of the Game (These 2 columns update after each game). My other 2 columns are used for notes and other information. When ever I refresh the data, the first 2 sets of columns update, however the notes for the game stays in the same place. How do I get my other 2 columns to move down the page with the old scores. Please help, because this is driving me crazy! If I need to explain further Please dont hesitate to ask!! Thank you, Matt Thu, 20 Dec 2007 13:22:00 -0800 from So...

Shading a cell using an if statement
Hi Group, What I am trying to do is to shade a cell when an arguement is true. if(a3=10,shade cell,don't shade cell) thanks a bunch for any help!! Hi this is not possible with formulas as they can only return values but not change formats. The only way would be to use VBA and create an event procedure. e.g. something like the following (this code goes into your worksheet module): Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub With Target If .Value = 10 Then .Interior.ColorIndex = 3 End If End With ...

Windows 2003 R2 Active Directory Performance Question
Here is our environment: 5 Windows 2003 R2 SP2 Domain Controllers (4 of which also do File/Print/DNS and 1 is running DHCP) spread across multiple VLANs (multiple NICs mapped to different VLANs in each) ��� These are HP DL380 G5's with 8GB RAM runninw Win2k3R2 Enterprise These DCs are all in the same physical location supporting 10 other buildings, some buildings are 1 mile, some are 7 miles away connected by GB fiber. Network is GB between buildings, GB between closets, 100MB to the desktop with a mix of Extreme and HP equipment with one BlackDiamond 6808 Router in the data ...

how can i put a file path statement in the footer in publisher?
how can i put a file path statement in the footer in publisher? Office 2007 Are you asking for a link to click on? If so, this is only used on websites or htm/html files. -- Don - Publisher 2000� Vancouver, USA "tampasculler" <tampasculler@discussions.microsoft.com> wrote in message news:7A998059-7E53-4017-9C89-18158D23EB88@microsoft.com... > how can i put a file path statement in the footer in publisher? Office > 2007 "Don Schmidt" wrote: > Are you asking for a link to click on? If so, this is only used on websites > or htm/html files...

Changing format on calculated field in a query
I have a calculated field in which I am using a switch function. The function calculates a bonus discount based on how many units a customer purchases. The units purchased are listed in the Quantity field and the Quantity field is used in my function. This query is based on one table and the Quantity field in that table has a number datatype. The switch function works fine but I can't change the formatting to a percent format. There is nothing in the Format text box drop down menu in the Field Properties dialog box. I tried typing in percent, but that didn't work. Can anyone ...

graph based on the city i select in a particular cell
I have cities and no. of stores in each city. For each store i have 2 data points. Can I have a graph based on a city i select in a drop down cell.. e.g if i select a city A, the graph should display for stores located in City A, if i select City B, the stores of City B get displayed in the chart Plz help Nikhil Hi, Depending on how you data and chart is setup you maybe able to use Autofilter. Select the chart and make sure the option to plot visible cells only is set. Tools > Options > Charts > Plot visible cells only. Cheers Andy -- Andy Pope, Microsoft MVP - Excel htt...

Conditional formatting.... Excel 2002
Hi, In Conditional formatting I am only permitted upto 3 Conditions but I have 6 conditions how may I overcome. Do later Excel revisions allow for more conditions in Conditional fromatting? Thanks Gunjani Excel 12 will support unlimited CFs. You can do it in earlier versions with VBA, such as Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1:H10" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case ...