help with the following statement

[zhuanyi]

Could anyone please let me know what is wrong with the following statement? I have been trying for 3 hours to debug this:

WHERE [inf 06/06].[JOINT TYPE]<>"F" AND [inf 06/06].[JOINT TYPE]<>"L" AND [inf 06/06].DOB Is Not Null AND [inf 06/06].DOB<>%00/00/0000% AND [inf 06/06].CLASS <>"AG" AND [inf 06/06].MORT = 1000 AND [inf 06/06].[TEMP FLX]="00000" AND [inf 06/06].[PERM FLX]="00000" AND [inf 06/06].MORT=1000 OR [inf 06/06].MORT=0 AND [inf 06/06].NAR >=1000 AND [inf 06/06]![NAR]-[inf 06/06]![FACE]<=0;

Thanks!

 

[WayneRyan]

z,

WHERE [inf 06/06].[JOINT TYPE]<>"F" AND 
      [inf 06/06].[JOINT TYPE]<>"L" AND 
      [inf 06/06].DOB Is Not Null AND 
      [inf 06/06].DOB <> %00/00/0000% AND <-- change % to #, and what is 00/00/0000?
      [inf 06/06].CLASS <>"AG" AND 
      [inf 06/06].MORT = 1000 AND 
      [inf 06/06].[TEMP FLX] = "00000" AND 
      [inf 06/06].[PERM FLX]="00000" AND 
      [inf 06/06].MORT = 1000 OR   <-- Probably need (), can't help you there
      [inf 06/06].MORT = 0 AND 
      [inf 06/06].NAR >=1000 AND 
      [inf 06/06]![NAR] - [inf 06/06]![FACE]<=0; <-- change ! to .

hth,
Wayne

 

[zhuanyi]

Thanks, I tried this but it is still giving me an error:

WHERE [inf 06/06].[JOINT TYPE]<>'F' AND [inf 06/06].[JOINT TYPE]<>'L' AND [inf 06/06].DOB Is Not Null AND [inf 06/06].DOB <> #00/00/0000# AND [inf 06/06].Class <> 'AG' AND [inf 06/06].MORT = 1000 AND [inf 06/06].[TEMP FLX] = '00000' AND [inf 06/06].[PERM FLX]= '00000' AND ([inf 06/06].MORT = 1000 OR [inf 06/06].MORT = 0) AND [inf 06/06].[NAR] >=1000 AND [inf 06/06].[NAR] - [inf 06/06].[FACE]<=0;

00/00/0000 is just to get rid of the garbage data, some dates were put in this way.

 

[boblarson]

00/00/0000 will not resolve to a date so you will always get an error on that one.

 

[WayneRyan]

z,

You're only using the DOB field as:

[inf 06/06].DOB Is Not Null AND <-- OK
[inf 06/06].DOB <> %00/00/0000% <-- ???

I'd guess that it's a text field and you need

[inf 06/06].DOB <> "00/00/0000"

If you ever need to compare the DOB field, I'd suggest converting it to
a Date datatype or at least use the CDate function.

BUT, with that OR in the Where clause, I'm guessing that you also need
some parentheses.

Wayne

 

[zhuanyi]

ok, it worked, thx for all the help