Missing operator for MS Access

[msaurea]

Hi,

I have getting the error about this. Hope you could help

 SELECT [NM_DATA clean].UPC_ID, 
DB2SH000_LU_NUTRIENT_FACTOR.UPC_ID, 
DB2SH000_LU_NUTRIENT_FACTOR.NDB_NBR, 
[NM_DATA clean].Source
 
FROM [NM_DATA clean] LEFT JOIN DB2SH000_LU_NUTRIENT_FACTOR 
ON [NM_DATA clean].UPC_ID = DB2SH000_LU_NUTRIENT_FACTOR.UPC_ID
 
where
 
[NM_DATA clean].Source = DB2SH000_LU_NUTRIENT_FACTOR.NDB_NBR
 
if [NM_DATA clean].Source like '9*' and  [NM_DATA clean].Source > 99999999 then [NM_DATA clean].Source= 'NIMS'
else if [NM_DATA clean].Source like '8*' and    [NM_DATA clean].Source > 99999999  then [NM_DATA clean].Source= 'corporate'
else  [NM_DATA clean].Source= 'FNDDS'
 
ENDIF
 

the error is missing operator from here

 [NM_DATA clean].Source = DB2SH000_LU_NUTRIENT_FACTOR.NDB_NBR
 
if [NM_DATA clean].Source like '9*' and  [NM_DATA clean].Source > 99999999 then [NM_DATA clean].Source= 'NIMS'
else if [NM_DATA clean].Source like '8*' and    [NM_DATA clean].Source > 99999999  then [NM_DATA clean].Source= 'corporate'
else  [NM_DATA clean].Source= 'FNDDS'
 
ENDIF
 

I always made some revision but nothing happens. Hope you can help me on this. thanks

[missinglinq]

 if [NM_DATA clean].Source like '9*' and  [NM_DATA clean].Source > 99999999 then

I suspect your error is coming from lines like the above. The Like operator takes a string for an argument, but

[NM_DATA clean].Source > 99999999

would seem to indicate that [NM_DATA clean].Source is a numeric datatype.

Depending on whether [NM_DATA clean].Source is text or numerical, you need to convert it to the opposite, using either the str() or val() function.

In the future, please use code tags when posting code, it makes it much easier to read.

Welcome to Bytes!

Linq ;0)>

[NeoPa]

I'm curious as to what language(s) are being used. There seems to be a mish-mash of SQL & VBA and the explanation doesn't help as it makes so little sense.

@Linq, The line quoted used "Like" against the '9*' which seemed ok - but it also had an "If" in there so who knows whether to check VBA syntax or SQL :S