make table query messes up data type 1

[pduncan]

I have several tables that are created by make table queries. Later, these tables are queried again, and the results are use to populate a report. The problem is that one field in particular is "Question number." When I sort this field for the report, it is all messed up.
i.e.
...
48
49
5
50
51
... etc
I looked at the table that was made by the make table query, and the field data type is a text value.

How can I force the query to make the question number to be a number data type?

below is the SQL from a typical query:
SELECT dbo_tblAssessment.AssessmentName, dbo_tblAssessment.AssessmentStatus, dbo_tblAssessmentControl.ControlNumber, dbo_tblAssessmentControl.ControlDataType, dbo_tblAssessmentControl.BooleanValue INTO tbl_AMS_YesNo
FROM (dbo_tblAssessment INNER JOIN dbo_tblAssessmentControlGroup ON dbo_tblAssessment.AssessmentID = dbo_tblAssessmentControlGroup.AssessmentID) INNER JOIN dbo_tblAssessmentControl ON (dbo_tblAssessmentControlGroup.ControlGroupID = dbo_tblAssessmentControl.ControlGroupID) AND (dbo_tblAssessmentControlGroup.AssessmentID = dbo_tblAssessmentControl.AssessmentID)
WHERE (((dbo_tblAssessment.AssessmentName) Like "ams*&quot AND ((dbo_tblAssessment.AssessmentStatus)="closed" Or (dbo_tblAssessment.AssessmentStatus)="review&quot AND ((dbo_tblAssessmentControl.ControlDataType) Like "Yes*&quot)
ORDER BY dbo_tblAssessment.AssessmentName, dbo_tblAssessmentControl.ControlNumber;



PDUNCAN - MEMPHIS, TN

When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[KenReay]

Hi

Not sure which column equates to Question No but assuming it is ControlNumber, then forcing it to be an integer might help, note this will change teh column naem in the 'made' table of course

SELECT dbo_tblAssessment.AssessmentName, dbo_tblAssessment.AssessmentStatus, CInt(dbo_tblAssessmentControl.ControlNumber) As QuestionNo, dbo_tblAssessmentControl.ControlDataType, dbo_tblAssessmentControl.BooleanValue INTO tbl_AMS_YesNo
FROM (dbo_tblAssessment INNER JOIN dbo_tblAssessmentControlGroup

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pduncan]

Ken - that worked nicely. Thanks


PDUNCAN - MEMPHIS, TN

When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.