I am getting the error "undefined function 'FIELD' in query expression" when running SQL query statement in excel VBA.

Question

0.00/5 (No votes)

See more:

SQL

SQL = "TRANSFORM FIRST(iif(isnull(c.TimeStamp1),'Pending','Complete')) "
SQL = SQL & "SELECT a.DealName, a.SettlementDate "
SQL = SQL & "FROM DealList a, DealStepLog c INNER JOIN DealStepMasterData b on b.StepName = c.DealStep "
SQL = SQL & "WHERE c.DealName = a.DealName AND "
SQL = SQL & "c.DealStep IN('Sent for De-dupe','De-dupe confirmation received','Sent for Risk feedback','Risk Queries Received','Risk feedback addressed') "
SQL = SQL & "GROUP BY a.DealName, SettlementDate "
SQL = SQL & "ORDER BY FIELD (dealStep, 'Sent for De-dupe', 'De-dupe confirmation received', 'Sent for Risk feedback', 'Risk Queries Received', 'Risk feedback addressed') ASC"

 SQL = SQL & " PIVOT dealStep "

What I have tried:

Please tell me if i can do something with my connection??

Posted 28-Jun-16 3:03am

Member 12605284

Updated 28-Jun-16 8:00am

Patrice T

v2

Add a Solution

Comments

Herman<T>.Instance 28-Jun-16 9:10am

perhaps checking with a profiler would help you?

Member 12605284 28-Jun-16 10:34am

Sorted that out by using an extra column so that i don't had to use FIELD function

CHill60 28-Jun-16 9:13am

Your ORDER BY statement is incorrect. There is no function called FIELD. What were you actually trying to do?
It looks like you want to see values for dealStep with 'Sent for De-Dupe' appearing before 'De-dupe confirmation received' appearing before 'Sent for Risk feedback' etc - is that correct?

Member 12605284 28-Jun-16 10:35am

Sorted that out by using an extra column so that i don't had to use FIELD function. Thanks

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sergiu1001 · Answer 1 · 2016-06-28T07:45:00

Solution 1

Hi,
Try "ORDER BY " without "FIELD".

Posted 28-Jun-16 7:45am

sergiu1001

Updated 28-Jun-16 8:12am

Comments

Member 12605284 29-Jun-16 0:32am

Does not work, but I have got it. Thanks.