Make query - Selecting specific values & adding characters 1

[wally2321]

Everyone:

I would like some help please. I am currently trying to create a Microsoft Access "make" query using the design feature. The following holds true for my senario:

Source Table: FLORENCE SCRUBBER - MAKE

Make Query Name: MAKE - KAUF (PDM)

Key field for query: MATERIAL NUMBER

Destination Table (made table): KAUF - MAKE PDM


I would like to do the following:


If the number of digits equals 11 digits, then select those records and add one zero. ( this should be placed at the beginning of the number)

If the number of digits equals 10 digits, then select those records and add two zero. ( this should be placed at the beginning of the number)


If the number of digits equals 12 digits, then just select the record.

All of these numbers should be put into one table.

Does anyone have a suggestion.

Again this should use the design view of the "Make query" function.

Thanks..

 

[JoeMiller]

Expr1: format([Material Number],!000000000000)

This will return your Material Number in 12 Digit format adding 0's on to the END of your number. If you want the 0's at the beginning then delete the ! from my code. Add that code to the Field line in your Make Table query.

Joe Miller
joe.miller@flotech.net

 

[wally2321]

Joe:

Thank you for your reply, but I am not sure this will meet my needs.

If I apply this code, will it not take all numbers that have 9 digits (or 8, or 9, or 7, etc.) and add leading zeros?

I really only need the following senario:


If the number of digits equals 11 digits, then select those records and add one zero. ( this should be placed at the beginning of the number)

If the number of digits equals 10 digits, then select those records and add two zero. ( this should be placed at the beginning of the number)


If the number of digits equals 12 digits, then just select the record.

All of these numbers should be put into one table.



Any ideas on how I can fulfill the above senario.

Thank you!

 

[JoeMiller]

The ! in my format command adds the 0's to the end.. Go back and read my previous post again.

Joe Miller
joe.miller@flotech.net

 

[JoeMiller]

Wait Wally, I spoke too soon. You didn't say that you only want digits of length 10 and 11 formatted. You can easily change my expression like so:

Expr1: IIf(Len[Material Number]>9,Format([Material Number],!000000000000),[Material Number])

Sorry for the mixup! Joe Miller
joe.miller@flotech.net

 

[LonnieJohnson]

Nice save Joe! ljprodev@yahoo.com
ProDev
MS Access Applications

 

[wally2321]

Joe & Company:

I got an error from the code. It gives me a syntax error. Additionally, what if the number is 13 digits.

This code should only select numbers with 10, 11, and 12 digits. In the line of code you have the following:

Expr1: IIf(Len[Material Number]>9,Format([Material Number],!000000000000),[Material Number])

Does that mean all numbers with 13 digits (or 14, 15, 16, etc.) will be effected since the digits are greater than 9?

Additionally the zeros should be in the beginning for the number with "10" and "11" digits.

Let me know thoughts or comments

 

[wally2321]

Does anyone have any suggestions? I know I am terribly new to Microsoft Access, so it could be the obvioius, so please be patient with me. I took the code that Joe wrote down above, and received an error. I noticed that the field names in the code were not capitalized, so I capitalized the [MATERIAL NUMBER] field. The code now looks as follows:


Expr1: IIf(Len[MATERIAL NUMBER]>9,Format([MATERIAL NUMBER],!000000000000),[MATERIAL NUMBER])


I receive the follow error when trying to leave the field:


"The expression you entered contains invalid syntax.
You may have entered an operand and without an operator"


Does this mean I need to define the field name with the table. I tried that, but that did not seem to work.

Once again, I would like to make a table using the Design feature of the make query. In the new table there should only be materials with 12,11, & 10 digits (coming from the original table). All numbers with 11 digits should have leading zero at the beginning. All numbers with 10 digits should have two leading zeros. All numbers with 12 digits stay the same format.

Does anyone have an idea on what I am doing wrong. Sorry if I am missing something obvious!

 

[Aivars]

myValue= iif(len(myValue)=10,"00" & myValue,iif(len(myValue)=11,"0" & myValue,myValue))

Aivars

 

[JoeMiller]

Try this Wally:

Expr1: IIf(Len([MATERIAL NUMBER]) = 10, [MATERIAL NUMBER] & "00", IIf(Len([MATERIAL NUMBER]) = 11, [MATERIAL NUMBER] & "0", [MATERIAL NUMBER]))

That will only modify numbers that are 10 or 11 digits in length otherwise, you get the number as is.

Joe Miller
joe.miller@flotech.net

 

[wally2321]

Thank you Joe Miller!