Cast string data type to Int in SQL QUery for MS Access

Question

I am trying to write a query in access that will pull results that are in the database in a text Acually,I have RECEIPTNO Column whose datatype is TEXT in Table Membership, & I want to pull all the results from RECEIPTNO column where RECEIPTNO is BETWEEN 1 AND 10

And I tried Below Code.

SELECT Cint(RECEIPTNO) FROM MEMBERSHIP where Cint(RECEIPTNO) BETWEEN 1 AND 10

Result is: Overflow , Any Idea?

Senility

Are you confusing the Jet/ACE/Access Integer data type with SQL Server Integer? The corresponding Jet/ACE/Access data type is Long Integer, so CLng() might work. But @Remou's suggestion of Val() avoids the problem entirely. – Altarpiece

Answer 1

Do you want:

SELECT RECEIPTNO FROM MEMBERSHIP
WHERE Val(RECEIPTNO) BETWEEN 1 AND 10

Synovitis

Answer 2

Ohhh I got the answer, & it's working

& the Query Like

SELECT Cint(RECEIPTNO) FROM MEMBERSHIP where RECEIPTNO BETWEEN 1 AND 10

Senility

sorry Above query not working,Coz For Above query Working Only When RECEIPTNO Column DataType Must be Number.But I want Query When RECEIPTNO Datatype is TEXT PleaseHelp – Senility

CInt doesn't work on strings, only on values. Use Val instead - see Fionnuala's answer! – Davidoff

Answer 3

This gives you what you want. ss

SELECT RECEIPTNO FROM MEMBERSHIP
WHERE Val(RECEIPTNO & "") BETWEEN 1 AND 10

Others using Val(RECEIPTNO) will only TRY to work somehow half-heartedly where it does beacuse the system doesn not recognise the RECEIPTNO as text and so, it is funny to it to see you passing a number instead of a string. However, to convince the system that the format is actually a number, concatenate it with an empty string which I have done.

Scyphate