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?

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. — David-W-Fenton
– David-W-Fenton, Commented Jul 16, 2010 at 19:33

Fionnuala · Accepted Answer · 2010-07-16 08:41:33Z

5

Do you want:

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

answered Jul 16, 2010 at 8:41

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

amol kadam · Accepted Answer · 2010-07-16 06:20:33Z

3

Ohhh I got the answer, & it's working

& the Query Like

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

answered Jul 16, 2010 at 6:20

amol kadam

1192 gold badges4 silver badges12 bronze badges

2 Comments

amol kadam Over a year ago

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

Peter Albert Over a year ago

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

Alexan · Accepted Answer · 2017-06-04 00:11:35Z

2

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.

edited Jun 4, 2017 at 0:11

Alexan

8,71514 gold badges83 silver badges107 bronze badges

answered Jun 3, 2017 at 23:39

OpeBams

212 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related