Syntax Error in a SQL string

Can someone spot the syntax error in this SQL string? I tried, but could not 
see where it is.

Thanks in advance,

Paulo

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

        Set qdfTemp = CurrentDb.CreateQueryDef("Current Orders Set", _
        "SELECT IIf([Orders]![Status]='REC',[Orders]![DC Cims Week 
Actual],[Orders]![ETA to DC Cims Week]) " & _
        "AS RelevantWeek, Sum(Orders.[Order Qty]) AS [SumOfOrder Qty], 
Sum(Orders.[Received Qty]) AS [SumOfReceived Qty], " & _
        
"Sum(IIf([Orders]![Channel]='WHOLESALE',IIf([Orders]![Status]='REC',[Orders]![Received Qty]*[WSale Price], " & _
        "[Orders]![Order Qty]*[WSale 
Price]),IIf([Orders]![Status]='REC',[Orders]![Received Qty]*[Std Retail 
Price], " & _
        "[Orders]![Order Qty]*[Std Retail Price]) AS Revenue, 
Sum(IIf([Orders]![Status]='REC', " & _
        "[Orders]![Received Qty]*[Landed GBP Cost],[Orders]![Order 
Qty]*[Landed GBP Cost])) AS Costs, " & _
        "Orders.[Col Ssn], Orders.Currency, Orders.Channel, Orders.Supplier 
FROM Orders WHERE" & _
        strWhere)
0
Utf
2/24/2010 11:13:01 AM
access.modulesdaovba 1670 articles. 0 followers. Follow

3 Replies
1164 Views

Similar Articles

[PageSpeed] 42

First error is that you cannot use the bang (!) operator with fields in 
tables. For instance, the first field needs to be

IIf([Orders].[Status]='REC',[Orders].[DC Cims Week Actual],[Orders].[ETA to 
DC Cims Week])

I stopped looking after that...

-- 
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Paulo" <Paulo@discussions.microsoft.com> wrote in message 
news:032EA455-5DE9-40A9-9F16-ECF7EF906291@microsoft.com...
> Can someone spot the syntax error in this SQL string? I tried, but could 
> not
> see where it is.
>
> Thanks in advance,
>
> Paulo
>
> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
>        Set qdfTemp = CurrentDb.CreateQueryDef("Current Orders Set", _
>        "SELECT IIf([Orders]![Status]='REC',[Orders]![DC Cims Week
> Actual],[Orders]![ETA to DC Cims Week]) " & _
>        "AS RelevantWeek, Sum(Orders.[Order Qty]) AS [SumOfOrder Qty],
> Sum(Orders.[Received Qty]) AS [SumOfReceived Qty], " & _
>
> "Sum(IIf([Orders]![Channel]='WHOLESALE',IIf([Orders]![Status]='REC',[Orders]![Received 
> Qty]*[WSale Price], " & _
>        "[Orders]![Order Qty]*[WSale
> Price]),IIf([Orders]![Status]='REC',[Orders]![Received Qty]*[Std Retail
> Price], " & _
>        "[Orders]![Order Qty]*[Std Retail Price]) AS Revenue,
> Sum(IIf([Orders]![Status]='REC', " & _
>        "[Orders]![Received Qty]*[Landed GBP Cost],[Orders]![Order
> Qty]*[Landed GBP Cost])) AS Costs, " & _
>        "Orders.[Col Ssn], Orders.Currency, Orders.Channel, Orders.Supplier
> FROM Orders WHERE" & _
>        strWhere) 


0
Douglas
2/24/2010 11:45:02 AM
It's a lot easier to debug this problem is you do something like

Dim strSQL as String

strSQL ="SELECT IIf([Orders]![Status]='REC',[Orders]![DC Cims Week
Actual],[Orders]![ETA to DC Cims Week]) " & _
         "AS RelevantWeek, Sum(Orders.[Order Qty]) AS [SumOfOrder Qty],
Sum(Orders.[Received Qty]) AS [SumOfReceived Qty], " & _

"Sum(IIf([Orders]![Channel]='WHOLESALE',IIf([Orders]![Status]='REC',[Orders]![Received 
Qty]*[WSale Price], " & _
         "[Orders]![Order Qty]*[WSale
Price]),IIf([Orders]![Status]='REC',[Orders]![Received Qty]*[Std Retail
Price], " & _
         "[Orders]![Order Qty]*[Std Retail Price]) AS Revenue,
Sum(IIf([Orders]![Status]='REC', " & _
         "[Orders]![Received Qty]*[Landed GBP Cost],[Orders]![Order
Qty]*[Landed GBP Cost])) AS Costs, " & _
         "Orders.[Col Ssn], Orders.Currency, Orders.Channel, Orders.Supplier
FROM Orders WHERE" & _
         strWhere

Debug.Print strSQL

Now you can see exactly what the string you have generated looks like.  And 
even copy and paste it into a new query to execute and see what might be wrong 
with the query.  Also, the syntax error could be in strWhere.  Perhaps all you 
need is to add a space after "WHERE" so you don't end up with
WHERESomeField = ... when you want WHERE SomeField = ....

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Paulo wrote:
> Can someone spot the syntax error in this SQL string? I tried, but could not 
> see where it is.
> 
> Thanks in advance
0
John
2/24/2010 2:56:03 PM
I'm not an expert with this stuff (yet) but if you are converting SQL to VBA, 
look at this:
http://allenbrowne.com/ser-71.html

I used it recently and had great success with it!!

Thanks Allen!!
-- 
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"John Spencer" wrote:

> It's a lot easier to debug this problem is you do something like
> 
> Dim strSQL as String
> 
> strSQL ="SELECT IIf([Orders]![Status]='REC',[Orders]![DC Cims Week
> Actual],[Orders]![ETA to DC Cims Week]) " & _
>          "AS RelevantWeek, Sum(Orders.[Order Qty]) AS [SumOfOrder Qty],
> Sum(Orders.[Received Qty]) AS [SumOfReceived Qty], " & _
> 
> "Sum(IIf([Orders]![Channel]='WHOLESALE',IIf([Orders]![Status]='REC',[Orders]![Received 
> Qty]*[WSale Price], " & _
>          "[Orders]![Order Qty]*[WSale
> Price]),IIf([Orders]![Status]='REC',[Orders]![Received Qty]*[Std Retail
> Price], " & _
>          "[Orders]![Order Qty]*[Std Retail Price]) AS Revenue,
> Sum(IIf([Orders]![Status]='REC', " & _
>          "[Orders]![Received Qty]*[Landed GBP Cost],[Orders]![Order
> Qty]*[Landed GBP Cost])) AS Costs, " & _
>          "Orders.[Col Ssn], Orders.Currency, Orders.Channel, Orders.Supplier
> FROM Orders WHERE" & _
>          strWhere
> 
> Debug.Print strSQL
> 
> Now you can see exactly what the string you have generated looks like.  And 
> even copy and paste it into a new query to execute and see what might be wrong 
> with the query.  Also, the syntax error could be in strWhere.  Perhaps all you 
> need is to add a space after "WHERE" so you don't end up with
> WHERESomeField = ... when you want WHERE SomeField = ....
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> Paulo wrote:
> > Can someone spot the syntax error in this SQL string? I tried, but could not 
> > see where it is.
> > 
> > Thanks in advance
> .
> 
0
Utf
2/25/2010 11:16:01 PM
Reply:

Similar Artilces:

Need Syntax for "AND" to Evaluate 2 Cells
I need to evaluate 2 cells while inside an "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". I thought AND would work but I cannot get it to work; I receive a syntax error on the AND(Range... line. Can someone please provide me the proper syntax to evaluate the 2 cells? Here's my code... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveSheet.Name = "Sheet1" Then And(Range("I3") <> "", Range("K4") = "") Then Range("K4") = Range("K3") End...

Sql to find record in a hierarchical chain
Hi, lets say I have a table with 2 fields: myTable Field 1 - ref Field 2 - parentRef Now in the structure there could be numerous chains until you get to the top of the hieratchy (where the ref field = parentRef field). ie ref parentRef 111 222 222 333 333 444 444 444 so in this 444 is top of the chain (there will be many other records as well that are nothing to with 444 and are part of their own hierarchy). So how do I easily relate 111 to 444. Ie how do i find the ultimate top parent for a given 'ref' field. Hope anyone can give me some pointers ...

Is it possible to generate non-technical schema validation errors?
With the 1.0 Framework, I've worked out using the XmlValidatingReader. Since I'm using the validation errors as feedback to the end user, I'm hoping to get away from techy messages such as "The 'http://tempuri.org/XMLFile1.xsd:MaxDependents' element has an invalid value according to its data type. An error occurred at file:///c:/work/prodika/main/code/apps/schemavalidation/XMLFile1.xml(8, 25)." and go with a user friendly message of "Max Dependents must be between 0 and 10". I've scoured the newsgroups, MSDN and docs for creating custom valid...

Upgrading
I want to upgrade from Microsoft Money Financial Suite OEM Edition Version 11.0 to latest version but on trying to open the money file in Microsoft Money 2007 I get the following error message: File was created by an incompatible version Question 1: As the old version was pre-loaded into the Computer when I bought it in India, I don't know which country US/Canada/UK/Australia this software belongs to. So how can I confirm which country is it for? Question 2: And, how can I upgrade it to a version of Money from US or UK. -- Kapil ...

VBA Error Message "Compile Error...."
I used a macro from Ron's website to mail each worksheet to the email address in cell a1. I ran this macro and it worked perfectly for one workbook BUT... when I tried to use it for another workbook I got the follwing error message "Microsoft Visual Basic Compile Error Can't find project or library Ok Help". Help me please... do I have to check some more references in the VBA tools? Thanks, Steve Hi Steve Have you set the reference to Outlook in that other workbook also ? You must do that in every workbook or you can use Late binding. See my site for a example --...

Error 1606 Money Service Pack 2002
I have tried all of the suggested fixes that I've seen on these boards for the error 1606. I now have no money program, I can not reinstall the money program and the error still pops up in my Turbo Tax and it is driving me crazy! I can not delete the service pack from the add or remove programs applet. I would very much like to finish the taxes before the 15Th. Does someone know what is going on? I've deleted registry keys, done clean boots, tried to eliminate every vestige of the program that can be found yet, when I try to reinstall from the disk, I'm stymied by Error...

OWA Error for http links
links inside a message body always generate an asp redirect code that does not execute at all thus giving an http 500 error - page not found. Example on a link to http://www.nightlight.org within a mail body. http://orange/exchweb/bin/redir.asp? URL=http://www.nightlight.org/ Exchange Server 2003 running on Server 2000 SP4 PLease help. ...

Error 2455 Closing Access 2007 database with form open
I have a form with a subform that is requeried when you select a new key for the main form from a combo box. Everything works fine - usually. But sometimes if you have the form open when you close the database down you get the following error message (twice) in a pop up. You say OK (twice) and the database closes OK "2455 you entered an expression that has an invalid reference to the property form/report" If I close the form before the database I never get the error. If I do not touch the form before you close the database I don't get the error. If I update a field by t...

Error message #5
Excel 2000 - Try to save workbook as Excel 97 - 2000 & 5.0/95. Receive error message " An error occurred and this feature is no longer functioning properly. Would you like to repair the feature now?" If you say YES I get an "Internal Error 2709." Does anyone know how to determine what feature the error message is referring to and how to repair the issue randy ...

Error creating new task
Error .. "Could not complete operation. One or more parameter values are not vaild" ...

Public folder issue, "messaging interface has returned an unknown error"
I'm running a SBS2003 domain and just added a public folder and a customized form for entry into the public folder. All of our clients (all XP Pro, Outlook 2003 with all updates) can get to that folder and use the form without any difficulty except for one. On one computer I get the classic "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." error message. I have tried a repair install, no change. I have tried making a new profile, no change. I have tried adding / removing his PST file (currently he's not even using a pst file...

Excel / VBA / SQL DB
Anybody done any work with Excel / VBA / SQL DB? Can you give me some pointers on how I could do the following all in one VBA routine: 1) From a cell variable value (ie, user enters a customer code), I query table A and put the data into a worksheet starting from say cell A1. 2) A blank row is created after the last data line in point 1 above. 3) Using the same cell variable, query table B and put the data into the worksheet starting from the row after the blank row in point 2 above. and so on. Like I've said before I work with ASP all day and know about ADO connections, commands and...

Outlook 2007 error event id: 35
Per hour i receive about 12'000 of these errors in the event viewer on my vista laptop. And usualy my outlook at some point just goes into the "not responding" mode. Then I have to kill the process from the task manager to be able to reopen outlook just to see it freeze again after a couple of seconds. I tried the whole stop indexing service and reinstall index service procedure to no avail. Does anyone else have a solution to this very anoying problem? Fred putzhilfe@gmail.com <putzhilfe@gmail.com> wrote: > Per hour i receive about 12'000 of these errors in the...

Error message when signing into Great Plains
I have a problem when setting up MS Great Plain. When I log into a company (E.G. company ABC), I receive an error message says "Decrypt Job Cost Creation Failed", I press OK, and the program just continues to run. Is there anyone know what is this message is about and how to resolve this problem?? Thank you very much This error is generated by the Wennsoft Products dictionary. Most times it relates to not installing the Wennsoft product from the SETUP.EXE. This is necessary as it installs and registers a couple DLL files, specifically WSREGKEYM.DLL and WSWRAPPER.DLL Refer to th...

Word 2007 Error message
Hello, I'm currently using Works Suite 2006. I am attempting to intall the Word 2007 upgrade to no avail as I receiving the following error message: SET-UP ERROR D:\wordR.ww\osetup.DLL digital signature does not validate or is not present I would appreciate any advice and/or suggestions so that I can install Word 2007. Ciao, Jerry Sorry Jerry but this is Microsoft Access forum. You need to post this question to a different group to find any useful help. "Jerry T" <Jerry T@discussions.microsoft.com> wrote in message news:B45E64C3-D080-405E-9159-CB3E7F73AFFC@micr...

Personal Folder Error when opening Outook 2002
When opening Outlook 2002, I receive the message that the Personal folder was not closed properly and needs to be checked. I allow the check to run, and all is fine. But this error message reappears after I close and reopen Outlook. Please Reply. thanks. ...

"could not fetch new headers " ERROR
i get the error " could not fetch new headers in the inbox of IMA server.The operation was cancelled ", while using my outlook 2000 o win98se mach. config: smtp/imap thro internet mail a/c option pl try to provide solutions to it. the error message pops up say every 5 min whenever outlook is on. thnks n regards gir ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com aggiridharan <aggiridharan.149yy0@outlookforum.com> wrote: > i get the error " coul...

Paid Sales Transaction Removal error message
Hola Familia When I try to do run a "Paid Sales Transaction Removal" against a specify customer (Microsoft Dynamics GP menu >> Tools >> Routines >> Sales >> Paid Transaction Removal) in Dynamics GP 10 with SQL 2005, I get the following error: [Microsoft] [SQL Native Client] [SQL Server] Violation of PRIMARY KEY constraint ‘PKRM30101’. Cannot insert duplicate key in object 'dbo.RM30101'. I searched for the error message in Customer Source Knowledge base and found Article ID : 861084, which I followed and it showed the duplicate document (invoice...

Error while backup SystemState via DPM 2010
Hi.. we backup the SystemState of our Server via DPM 2010. Since we're upgrade from DPM 2007 RTM to DPM 2010RC (now we use DPM 2010 RTM) on the Server DPM is hosted a eventlog error occured every day after the backup is finished: Source: FilterManager, Event ID: 3 "Filter Manager failed to attach to volume '\Device\HarddiskVolume....'. This volume will be unavailable for filtering until a reboot. The final status was 0xc03a001c." Server Version: Windows Server 2008 R2 x64 any ideas? Alex ...

absolutely cannot resolve timeout error
OL 2002 (10.6515.6626) SP3 Win XP HE Follow-up to: *microsoft.public.outlook* (this message is cross-posted to the newsgroups that appear in the headers. To reply, just click "Reply" in news client and it will go to the follow-up newsgroup *microsoft.public.outlook*, where u can follow the thread. Thanks.) Hi, I am constantly receiving error message: Task 'smtp server name - Sending and Receiving' reported error (0x8004210A): 'The operation timed out waiting for a response from the receiving (POP) server. If you continue to receive this message, contact your s...

Error trying to make an account default
I have a new computer and my outlook 2003 is not letting me assign a default account to send and receive. I get this error: "The Specicied Account could not be found. It might have been deleted." I have four accounnts setup and have tried removing the accounts and recreating them and still get the error. when the account has been made, it tests fine. None of the four can be made default. Thanks in Advance! ...

ESM 2003 info store Error on WinXP PC
ESM 2003 on Windows XP SP1a PC: Installed exchange system manager onto a windows xp sp 1a pc. I can run ESM fine, yet not administer public folders. When I access a public folder's properties, select the Permissions Tab and click "client permissions" button, I get an error-- The information store could not be opened. The logon to the Microsoft Exchange Server computer failed. MAPI 1.0 ID no: 80040111-0286-00000000 ID no: c105000 Exchange System Manager OK. Any ideas to resolve? Using ESM on server (even through RDC) works fine! Thanks! ~Terry ...

Can I have spell check ignore errors and alert me to real words?
Instead of stopping on misspellings and skipping real words, is there a way to stop on correct spellings to find actual words in a bunch of gibberish? Sort of an "UNspell check". For example, pass over "JJKSXO" but show me "SPELL". No, there is no way of doing that regards, Peo Sjoblom "JenLynFish" wrote: > Instead of stopping on misspellings and skipping real words, is there a way > to stop on correct spellings to find actual words in a bunch of gibberish? > Sort of an "UNspell check". For example, pass over "JJKSX...

Can I set SQL mirroring across WAN?
We have a 100 Mb dedicated DR link to another data center. Want to set up a SQL 2008 database to use mirroring from primary to a standby server at the remote data center. I did a ping test and here is the result. ping -n 100 -l 5120 <target IP> Ping statistics for <target IP> Packets: Sent = 100, Received = 99, Lost = 1 (1% loss), Approximate round trip times in milli-seconds: Minimum = 8ms, Maximum = 19ms, Average = 11ms is the network latency good enough for database mirroring? Does anyone know the min. requirement? Field experience will be fine too....

Unicode string passing
I have a query about calling a DLL using Unicode. I need to pass a string from an application (which I compile with UNICODE and _UNICODE defined) to a DLL (also UNICODE compiled). However, the string I pass gets mangled along the way. I'm passing the string as a LPCTSTR. Identical code works fine when both are compiled without the UNICODE defines. Does anyone have any ideas as to why this may be? Thanks in advance The application: extern "C" __declspec(dllimport) bool IsLicensed(LPCTSTR); { .. .. CString appName = _T("AppName"); bool test = IsLicensed(a...