sql: limit number

[apple]

Hi,

Do anybody know how to write SQL to limit number? Thanks

[Bob Phillips]

Limit the number of records returned, or to limit the values queried? Ormaybe even something else.

[apple]

Hello,

I want to limit number in my query SQL. For example limit length to 10 digit number. For example every time i pull the data is 10 digit. For example number 10 digit is Z4561-45678

Thanks

[mattj]

Use Len(YourField) = 10 as the criteria in the query. This will only return records where the field is 10 characters long.

HTH
Matt

[apple]

Hi,

Is it i suppose to write : WHERE LEN(TABLE1.COLUMNA) = 10
That means every time i will pull 10 digit number

[mattj]

Yes. However, in your example number, you would have to use a length of 11 because of the hyphen.

[apple]

Hi,

My query is WHERE got 2 condition.

condition 1: "WHERE TABLE1.COLUMNA IN (" & item & ") " & _
condition 2 : "AND LEN TABLE1.COLUMNA= 11 "

My condition 1 is pull all the item in excel sheet

Condition 2 is pull length 11

Is it my SQL correct?

[mattj]

Are you trying to use the actual excel spreadsheet for your critera? If so, this will not work.

[apple]

Hi,

Yes, i use the excel SHEET1 to pull all the item number with the criteria limitation 11 digit. If cant work? What should i do?

[mattj]

You will need to import the data into Access or create a linked table to the excel spreadsheet. Then you can create a join between this linked table and the table you are performing the query on. This join will allow you to only display records from your table that have a match in the linked excel spreadsheet.

[apple]

Hi,

Thanks for your respond. Can you please explain as i am not understand. Thanks

[mattj]

1. Link the excel spreadsheet to Access as a linked table.
2. Create a query that includes the table you are getting records from AND the linked excel table.
3. Create a join between these two tables on the matching field
4. Right-click the line between the two tables (the join)
5. Choose join properties
6. Select the option that starts with Select All records from "your linked excel table". This is technically referred to as a "Left" join

[apple]

Hi,

Noted. Thanks

[apple]

HI,

Please do advise me how to write SQL to limit the
pattern number: Y1234-87498. (In front 5 digit and 5 digit at the back).

Is it i must use LEFT or RIGHT? Can anybody show me the way.

Example that i used but there is a syntax error in "RIGHT"

SELECT*
FROM TABLE1 COLUMNA
WHERE LEN (TABLE1.COLUMNA)= 11
AND RIGHT TABLE1.COLUMNA IN ('6')

[OBP]

AND RIGHT(TABLE1.COLUMNA, 6)

[apple]

HI,

Is it same apply in left too if i want to returm 6 number in front



SELECT*
FROM TABLE1 COLUMNA
WHERE LEN (TABLE1.COLUMNA)= 11
AND RIGHT(TABLE1.COLUMNA, 6)
AND LEFT(TABLE1.COLUMNA, 6)

I write this syntax SQL is to pull out item number type Y1234-87498, length 11. Is it correct i use Right and Left method as above in red font?

Thanks