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

 

[AlexMidd]

What about a query with criterion to filter the number range, then another query based on the first one (i.e. choose the first query to put in the design grid instead of a table) with the above criterion? Alex Middleton

 

[wally2321]

I thought of that,but I would like to incorporate it all in the same query.. Any ideas.

 

[JoeMiller]

You can put in another iif statement that evaluates the number:

IIf([MyNumber]>=3000000 AND [MyNumber]<=99999999,[IIfStatementAbove],&quot;&quot

Am I understanding you correctly? Or do you mean you want to put something in the criteria and not show the record altogether? In that case you can use the between operator in the criteria of your number field:

Between 3000000 and 99999999


HTH
Joe Miller
joe.miller@flotech.net

 

[wally2321]

Maybe I need to clarify further. The statement below is code I have already created:

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

As you can see, the code above removes the first character if it is a z, s, e, of f. If it meets that criteria, then the letter should be removed. i.e. F10000000 would now be 10000000.

From this point I would like to evaluate the finished product of that logic, and determine if it falls in the following range: 3000000 - 99999999

If it falls in that number range, then it should be displayed.

Ideas?

 

[JoeMiller]

Put the between statement in the criteria of that field with the &quot;finished product.&quot;

Between 3000000 and 99999999 Joe Miller
joe.miller@flotech.net

 

[wally2321]

Joe & Company:

The finished product field looks as follows:


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

Table:

Sort:

Show:

Criteria:
Between 3000000 and 99999999


This does not return values only between the desired number range.

As an additional problem I am having, I am bringing back all values that have an F,Z, S, or E. In my query I have another field that I use to limit the criteria. The field is as follows:

Field: PRODUCTION CODE

Criteria: HIBE


I thought by having this criteria it would only limit the records to the Production Code of HIBE. When examining the records that were returned, I found other Production Codes that were returned. Do I need to include this criteria of only having production code = HIBE in the IIF statement I have listed above.
Help Please.... I feel like I am going in reverse.

 

[wally2321]

I am desperate for help here....


As I stated above, I need to incorporate the number range in the existing statement. In addtion to this.. I have to ensure that one more criteria is met, which is that the PRODUCTION CODE field equals HIBE..

I tried this...

NO: IIf(Left([MATERIAL NUMBER],1)=&quot;F&quot; Or Left([MATERIAL NUMBER],1)=&quot;Z&quot; Or Left([MATERIAL NUMBER],1)=&quot;S&quot; Or Left([MATERIAL NUMBER],1)=&quot;E&quot;,Right([MATERIAL NUMBER],Len([MATERIAL NUMBER])-1),[MATERIAL NUMBER]) And IIf( Left[PRODUCTION CODE]=&quot;HIBE&quot


I get an error saying &quot;You may have entered an operand without an operator&quot;

What does this mean?

HELP!

 

[nickjar2]

looks like their may an error with your left function

I think it should say Left([PRODUCTION CODE],4)=&quot;HIBE&quot

Nick

 

[wally2321]

Ok I tried that, but I am still receiving an error.... Does anyone have idea of the problem?

NO: IIf(Left([MATERIAL NUMBER],1)=&quot;F&quot; Or Left([MATERIAL NUMBER],1)=&quot;Z&quot; Or Left([MATERIAL NUMBER],1)=&quot;S&quot; Or Left([MATERIAL NUMBER],1)=&quot;E&quot;,Right([MATERIAL NUMBER],Len([MATERIAL NUMBER])-1),[MATERIAL NUMBER]) And IIF (Left([PRODUCTION CODE],4)=&quot;HIBE&quot


Once again, I want only materials that have the field of PRODUCTION CODE equal to HIBE, but if it begins with a f,z,s, or e then the letter should be removed. Also included in the logic that only materials that fall between the following interval (3000000 - 99999999) should be returned in the table

 

[nickjar2]

The only other thing I can think of:

put your original expression

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

and in the criteria put

Between 3000000 and 99999999

and then insert a new field (Production code) and in the criteria type:

mid$([production code],1,4) = &quot;HIBE&quot;

 

[wally2321]

OK... I am getting frustrated now....I tried exactly as you suggested.. (which I appreciate)


First I tried just putting the following in the criteria the following:

Between 3000000 And 99999999


I keep getting an error that says the following:

&quot;Data type mismatch in criteria expression&quot;

I get a similiar error when putting the following in the criteria of the other field :

mid$([production code],1,4) = &quot;HIBE&quot;

Can someone please offer me some guidance on how I can accomplish the task at hand?

Wally

 

[nickjar2]

you may be getting a data type mismatch because the [material number] field is text but you're using 'between' which i think can only be used with a numeric field.

Nick

 

[JoeMiller]

You can change the data type of the resulting Material Number (after stripping the letters) by using the val function and then &quot;Between&quot; will work.

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

HTH Joe Miller
joe.miller@flotech.net

 

[JoeMiller]

Sorry!! Forget my prior post, and use this instead. I got confused with all those ['s, )'s and /'s!

You can change the data type of the resulting Material Number (after stripping the letters) by using the val function and then &quot;Between&quot; will work.

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

HTH Joe Miller
joe.miller@flotech.net

 

[tlbroadbent]

Wally,

Let's try a fresh approach to the problem, simpler perhaps.

I've created a new query which I would like you to paste into the SQL View of the query designer. You'll need to make sure the column and table names are correct. The column names should be if you've posted them correctly in this thread.

You can swith to design view after pasting the query so you can see what it looks like there. You should be able to see what I've done having made some assumptions. Let me know if the query works for you or if we still need to tweak it.

The query:

SELECT
MatlTbl.[Material Number],
MatlTbl.[Production Code]
FROM MatlTbl
WHERE (((Left([Material Number],1))=&quot;E&quot;
Or (Left([Material Number],1))=&quot;F&quot;
Or (Left([Material Number],1))=&quot;S&quot;
Or (Left([Material Number],1))=&quot;Z&quot
AND ((CLng(Right([Material Number],Len([Material Number])-1))) Between 3000000 And 99999999)
AND ((Left([Production Code],4))=&quot;HIBE&quot); Terry

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

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

 

[wally2321]

Ugh...I am still getting the same error.......I copied the code you had above exactly. That works fine by itself... but when I put in the following statement in the criteria:

Between 3000000 And 99999999

I receive an error that states there has been a mismatch statement.

Additionally, do you have an idea of how I can only return records that have a production code of &quot;HIBE&quot; ... I tried the earlier suggestion, which did not work......

If I put HIBE in the criteria field by itself, it returns other values as well????

HELP

 

[nickjar2]

maybe try cDbl instead of cLng

 

[tlbroadbent]

My query already includes

Between 3000000 And 99999999
AND Left([Production Code],4)=&quot;HIBE&quot;

You shouldn't need to add any additional criteria. What happened when you ran the query I provided? It ran fine but what? Returned too many rows? Didn't filter properly? Help me understand why you needed to add criteria that the query already contains. Terry

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

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

 

[wally2321]

Terry:

I took your suggestion and pasted the code in the SQL View. I had to change the table name and the field(s), but I think I did that correctly. The finished product looked as follows:

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

When trying to go back to the design view I am presented with the following error:

Syntax error (missing operator) in query expression 'FLORENCE SCRUBBER - MAKE.[MATERIAL NUMBER]'

What does that mean, and how do I fix it....