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?
 
Anthony PerkinsConnect With a Mentor Commented:
See if this works for you:
IIF(IsNull([LB_WO_BACKLOG].[CREW_PRIORITY]), '25', [LB_WO_BACKLOG].[CREW_PRIORITY])
0
 
gnoonConnect With a Mentor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.