SQL Syntax issues between ADO.net and Jet 4 Nz and like

I am using queries against an Access 2K mdb to create datasets in VB2K5.  I am running into issue with the SQL syntax.  I can not get the "like" operator to return any rows.  So I used a left trim against the field as a work around.  However, now I need the Nz function.
Tesla428Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gnoonCommented:
AFAIK, jet's using * and ? as wildcard with LIKE clause, not %.
See more here http://office.microsoft.com/en-us/access/HP010322501033.aspx?pid=CH010410151033
0
Tesla428Author Commented:
Thanks, Gnoon.  You will get a portion of the points.
OK. The like operator works with the %. That makes sense.  

Now, I am looking for an equivalent to the Nz function.  The SQL statement returns the wrong sort order with out the Nz.  The Nulls appear at the top instead of the bottom.

Please disregard left and like parts of this statement as that part is now working.
SELECT DISTINCT Nz([LB_WO_BACKLOG]![CREW_PRIORITY],25) AS Expr1, lb_emp_crew.CREW, LB_EMP_SCHED_HRS.ACTUAL_DATE
FROM (lb_emp_crew INNER JOIN LB_EMP_SCHED_HRS ON lb_emp_crew.EMP_NO = LB_EMP_SCHED_HRS.EMP_NO) LEFT JOIN LB_WO_BACKLOG ON lb_emp_crew.CREW = LB_WO_BACKLOG.CREW
WHERE (((LB_EMP_SCHED_HRS.ACTUAL_DATE)=#1/31/2008#) AND ((Left(([lb_emp_crew].[CREW]),4))="PINE") AND ((LB_EMP_SCHED_HRS.SCHED_HRS)=0) AND ((LB_EMP_SCHED_HRS.AVAIL_HRS)>0)) OR (((lb_emp_crew.CREW) Like "PINE") AND ((LB_EMP_SCHED_HRS.ACTUAL_DATE)=#1/31/2008#) AND ((LB_EMP_SCHED_HRS.SCHED_HRS)>0) AND ((LB_EMP_SCHED_HRS.AVAIL_HRS)=0))
ORDER BY Nz([LB_WO_BACKLOG]![CREW_PRIORITY],25), lb_emp_crew.CREW;

Open in new window

0
Anthony PerkinsCommented:
gnoon,
>>AFAIK, jet's using * and ? as wildcard with LIKE clause, not %.<<
Actually no.  Seeing as the user is using ADO.NET (also true with ADO classic) % is the correct wildcard character.  

From your own link: "The ANSI SQL wildcards are only available when using Jet 4.X and the Microsoft OLE DB Provider for Jet. If you try to use the ANSI SQL wildcards through Microsoft Access or DAO, then they will be interpreted as literals. The opposite is true when using the Microsoft OLE DB Provider for Jet and Jet 4.X. "

Guess what provider ADO.NET uses when there is an MS Access database involved?

Tesla428,
>>However, now I need the Nz function.<<
I don't believe this is supported in ADO.NET, so can you post your query and perhaps we can suggest a workaround.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Anthony PerkinsCommented:
>>The Nulls appear at the top instead of the bottom.<<
So you are saying that the OLEDB driver does support Nz and whenever there is a Null it will replace it with 25.  If that is the case than it will be sorted whereever 25 belongs and has nothing to do wiith the fact that it was Null.
0
Tesla428Author Commented:
Acperkins:  You are absolutely correct on the wildcard deal.  I think I need an ANSI SQL equivalent for Access's Nz function.  I also believe that IsNull should do it.

This statement errors out with a "Wrong Number of Arguments" error on the IsNull function.
SELECT DISTINCT IsNull([LB_WO_BACKLOG].[CREW_PRIORITY],25), lb_emp_crew.CREW FROM (lb_emp_crew INNER JOIN LB_EMP_SCHED_HRS ON lb_emp_crew.EMP_NO = LB_EMP_SCHED_HRS.EMP_NO) LEFT JOIN LB_WO_BACKLOG ON lb_emp_crew.CREW = LB_WO_BACKLOG.CREW WHERE ((((lb_emp_crew.CREW) LIKE  "PINE%") AND ((LB_EMP_SCHED_HRS.ACTUAL_DATE)=#1/31/2008#) AND ((LB_EMP_SCHED_HRS.SCHED_HRS)=0) AND ((LB_EMP_SCHED_HRS.AVAIL_HRS)>0)) OR (((lb_emp_crew.CREW) Like "PINE%") AND ((LB_EMP_SCHED_HRS.ACTUAL_DATE)=#1/31/2008#) AND ((LB_EMP_SCHED_HRS.SCHED_HRS)>0) AND ((LB_EMP_SCHED_HRS.AVAIL_HRS)=0))) ORDER BY IsNull([LB_WO_BACKLOG].[CREW_PRIORITY],'25');

Open in new window

0
Anthony PerkinsCommented:
>>I think I need an ANSI SQL equivalent for Access's Nz function. <<
I am afraid I have no idea.  I don't do Access.  But if your previous function using Nz() did not report a syntax error than it obviously does support it.  The fact that it did not return the results in the expected order is irrelevant.

If I recall the JET SQL dialect has an IsNull() function, but it is not equivalent of T-SQL's IsNull and only supports one parameter.   So you may be stuck with Nz().
0
David ToddSenior DBACommented:
Hi,

is LB_WO_BACKLOG].[CREW_PRIORITY] a character or numeric data type?

Cheers
  David
0
Tesla428Author Commented:
It turns out that Crew_Priority is a 6 character String.

Nz does error out with "Not a Function"

Keep um coming!
0
David ToddSenior DBACommented:
Hi,

The change the
IsNull([LB_WO_BACKLOG].[CREW_PRIORITY],25),
to
IsNull([LB_WO_BACKLOG].[CREW_PRIORITY], '25' ),

in the above query

HTH
  David
0
Tesla428Author Commented:
David,
Thanks but:
Wrong number of arguments used with function in query expression 'IsNull([LB_WO_BACKLOG].[CREW_PRIORITY], '25' )'.

Would a case syntax work?
SELECT DISTINCT IsNull([LB_WO_BACKLOG].[CREW_PRIORITY], '25' ), lb_emp_crew.CREW FROM (lb_emp_crew INNER JOIN LB_EMP_SCHED_HRS ON lb_emp_crew.EMP_NO = LB_EMP_SCHED_HRS.EMP_NO) LEFT JOIN LB_WO_BACKLOG ON lb_emp_crew.CREW = LB_WO_BACKLOG.CREW WHERE ((((lb_emp_crew.CREW) LIKE  "PINE%") AND ((LB_EMP_SCHED_HRS.ACTUAL_DATE)=#1/31/2008#) AND ((LB_EMP_SCHED_HRS.SCHED_HRS)=0) AND ((LB_EMP_SCHED_HRS.AVAIL_HRS)>0)) OR (((lb_emp_crew.CREW) Like "PINE%") AND ((LB_EMP_SCHED_HRS.ACTUAL_DATE)=#1/31/2008#) AND ((LB_EMP_SCHED_HRS.SCHED_HRS)>0) AND ((LB_EMP_SCHED_HRS.AVAIL_HRS)=0)));

Open in new window

0
David ToddSenior DBACommented:
Hi,

Sorry that is MS SQL syntax, not Access.

Cheers
  David
0
Anthony PerkinsCommented:
See if this works for you:
IIF(IsNull([LB_WO_BACKLOG].[CREW_PRIORITY]), '25', [LB_WO_BACKLOG].[CREW_PRIORITY])
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tesla428Author Commented:
iif(isnull([LB_WO_BACKLOG].[CREW_PRIORITY]),'25',[LB_WO_BACKLOG].[CREW_PRIORITY]),

OK, Here it is.

While ADO would let the Nz go through, it would let iif.

Than
SELECT DISTINCT iif(isnull([LB_WO_BACKLOG].[CREW_PRIORITY]),'25',[LB_WO_BACKLOG].[CREW_PRIORITY]), lb_emp_crew.CREW FROM (lb_emp_crew INNER JOIN LB_EMP_SCHED_HRS ON lb_emp_crew.EMP_NO = LB_EMP_SCHED_HRS.EMP_NO) LEFT JOIN LB_WO_BACKLOG ON lb_emp_crew.CREW = LB_WO_BACKLOG.CREW WHERE ((((lb_emp_crew.CREW) LIKE  "PINE%") AND ((LB_EMP_SCHED_HRS.ACTUAL_DATE)=#1/31/2008#) AND ((LB_EMP_SCHED_HRS.SCHED_HRS)=0) AND ((LB_EMP_SCHED_HRS.AVAIL_HRS)>0)) OR (((lb_emp_crew.CREW) Like "PINE%") AND ((LB_EMP_SCHED_HRS.ACTUAL_DATE)=#1/31/2008#) AND ((LB_EMP_SCHED_HRS.SCHED_HRS)>0) AND ((LB_EMP_SCHED_HRS.AVAIL_HRS)=0)));

Open in new window

0
Tesla428Author Commented:
Gnoon, I gave you a few for being backwards but getting me over the hump as I hadn't tried %.

ACPerkins, Thanks for sticking with me. So the syntax is close to Access but not wildcards and apparently the Nz function.  Wizard seems appropriate.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.