Simple question

[wally2321]

Once again I have a question... I have the following code in my query -

NO: IIf(Left([MATERIAL NUMBER],1)="F" Or Left([MATERIAL NUMBER],1)="Z" Or Left([MATERIAL NUMBER],1)="S" Or Left([MATERIAL NUMBER],1)="E",Right([MATERIAL NUMBER],Len([MATERIAL NUMBER])-1),[MATERIAL NUMBER])


I am using the Design feature of the Make query. If I wanted to further define the data to only return if the number falls in a certain range, how could that be done?

So if the above is true, but only should be returned if the number falls between .......

3000000 - 99999999

Anyone have an idea on how I can add this logic to my existing line of code above?

Wally

 

[nickjar2]

FLORENCE SCRUBBER - MAKE.[MATERIAL NUMBER],
FLORENCE SCRUBBER - MAKE.[PRODUCTION CODE]

This contains the '-' sign which will confuse it. It will think you are trying to do a substraction.

try

[FLORENCE SCRUBBER - MAKE].[MATERIAL NUMBER],
[FLORENCE SCRUBBER - MAKE].[PRODUCTION CODE]

or remove the - from your table name

 

[tlbroadbent]

nickjar2 is correct. Just enclose your table names in []. Embedded spaces cause problems. Terry

http://members.home.net/tlbroadbent/prog.htm

"I'm not dumb. I just have a command of throughly useless information." - Calvin, of Calvin and Hobbes

 

[wally2321]

Ok guys........I am still having a problem...after taking your advise, I have the following code:

SELECT
[FLORENCE SCRUBBER - MAKE].[MATERIAL NUMBER],
[FLORENCE SCRUBBER - MAKE].[PRODUCTION CODE]
FROM [ FLORENCE SCRUBBER - MAKE]
WHERE (((Left([MATERIAL NUMBER],1))="E"
Or (Left([MATERIAL NUMBER],1))="F"
Or (Left([MATERIAL NUMBER],1))="S"
Or (Left([MATERIAL NUMBER],1))="Z&quot
AND ((CLng(Right([MATERIAL NUMBER],Len([MATERIAL NUMBER])-1))) Between 3000000 And 99999999)
AND ((Left([PRODUCTION CODE],4))="HIBE&quot) ;


When trying to run the program, I am given the following error:

Invalid bracking of name 'FLORENCE SCRUBBER - MAKE'.

Any ideas?!!!

 

[tlbroadbent]

Looks like an extra space in the FROM clause - between [ and FLORENCE.

FROM [ FLORENCE SCRUBBER - MAKE]
Terry

http://members.home.net/tlbroadbent/prog.htm

"I'm not dumb. I just have a command of throughly useless information." - Calvin, of Calvin and Hobbes

 

[wally2321]

Ok... Now I have the following:

SELECT
[FLORENCE SCRUBBER - MAKE].[MATERIAL NUMBER],
[FLORENCE SCRUBBER - MAKE].[PRODUCTION CODE]
FROM [FLORENCE SCRUBBER - MAKE]
WHERE (((Left([MATERIAL NUMBER],1))="E"
Or (Left([MATERIAL NUMBER],1))="F"
Or (Left([MATERIAL NUMBER],1))="S"
Or (Left([MATERIAL NUMBER],1))="Z&quot
AND ((CLng(Right([MATERIAL NUMBER],Len([MATERIAL NUMBER])-1))) Between 3000000 And 99999999)
AND ((Left([PRODUCTION CODE],4))="HIBE&quot) ;

I receive the following error code now:

Data type mismatch in criteria expression

Any ideas?!

 

[tlbroadbent]

I suspect that the [Material Number] isn't all numeric after the letters "E", "F", "S" and "Z". This poses some problems with search criteria but initially causes the Type Mismatch error in the CLng function. Replace OLD section of the SQL with the NEW SQL.

OLD:
AND ((CLng(Right([MATERIAL NUMBER],Len([MATERIAL NUMBER])-1))) Between 3000000 And 99999999)

NEW:
AND ((CLng(IIf(IsNumeric(Right([Material Number],Len([Material Number])-1)),Right([Material Number],Len([Material Number])-1),0)) Between 3000000 And 99999999)
Terry

http://members.home.net/tlbroadbent/prog.htm

"I'm not dumb. I just have a command of throughly useless information." - Calvin, of Calvin and Hobbes