Data Request - Wrong Data Shown

[andy1210]

Hi,
Following on from my previous post regarding reports I now have another problem with the report I am creating. the intial problem I had was that I could not order a report properly as the column was a text field not a number field. I solved this problem with help from your good selves by using the Val([Fieldname]) function.
The next problem is that I now need to show data that is between two entered codes. The codes are numbers but are in a text field. I am using the BETWEEN command aswell as Val([Fieldname]) command in my SQL, however if I was to search between codes 1 and 2, it still shows all codes between 1 and 29. Obviously the data is still being seen as text and not a number.
If anyone can help with this problem then it would be greatly appreciated... also if I've not described the problem well enough or it doesnt make sense then let me know and I will try and ellaborate!!
Thanks in advance.
Andy

 

[Fear Naught]

I really think you need to change the data type for that particular column if you can.

I created a test table (tblTestTexttoInt) and query. The table contained:
id Autonumber and PK
Codetxt Text (3)

I select both columns for the query and added a calculated column (Integer:Val([codetxt]) and set the criteria for as "BETWEEN 1 AND 2"

The SQL for the query is:

SELECT tblTestTexttoInt.id, tblTestTexttoInt.Codetxt, Val([codetxt]) AS [Integer]
FROM tblTestTexttoInt
WHERE (((Val([codetxt])) Between 1 And 2));

The result of the query is:

id Codetxt Integer
1 1 1
2 2 2


Now I changed the query and set the crtieria to the [Codetxt] column. The resultant SQL is:

SELECT tblTestTexttoInt.id, tblTestTexttoInt.Codetxt, Val([codetxt]) AS [Integer]
FROM tblTestTexttoInt
WHERE (((tblTestTexttoInt.Code)txt Between "1" And "2"));

You will see how Access handles the criteria differently in that it recognises that the criteria is based on a text field.

The result of this query was:

id Codetxt Integer
1 1 1
7 10 10
8 11 11
9 12 12
11 13 13
2 2 2

I hope that this helps with your dilemma.

 

[andy1210]

Problem solved, just needed to use the VAL function. All I did was set the column that needed to be changed from Text to number by using VAL -:

AreaNum:Val([Area.Code])

Then in the criteria line, I just put VAL infront of the input box code, so the criteria line reads as-:

BETWEEN VAL([Please Enter 1st Code]) AND VAL([Please Enter 2nd Code]).

Thanks for the help.
Cheers
Andy