Forcing a specific type for a query field

[takatam]

Hi,

In my query I list some fields from a table and would then like to
calculate another one based on the presented values.

The query that I now have is the following:

SELECT DocumentTable.Document, DocumentTable.[Visio Status],
DocumentTable.[HL Status], DocumentTable.[TC Status], DocumentTable.
[Script Status], Switch([Visio Status]<3 Or [HL Status]<3,1,[Visio
Status]=6 And [HL Status]=6 And [TC Status]=6,3,True,2) AS [Overall
Status]
FROM DocumentTable
WHERE (((DocumentTable.Document)=[Forms]![FormDocument]!
[DocumentCombo]));

How can I force the last column (Overall Status) to be of numerical
value? All used columns are numerical, and the values from the Switch
clause are (at least should be) also numbers. So why does it not let
me define the column to be of numerical type?

What I'm trying to do is to assign the last column a value based on
combinations of the other columns.

Any help is appreciated.

Thanks a lot,
Tom

 

[Golfinray]

You have to define datatype in your table and then it will be what you want
it to be in the query. Go to the design view of the table. Another way might
be to put the Val command in front New Field:Val([overall status])

 

[Klatuu]

Maybe it is a matter of where you are doing it. See if this works.

SELECT DocumentTable.Document, DocumentTable.[Visio Status],
DocumentTable.[HL Status], DocumentTable.[TC Status], DocumentTable.
[Script Status], CLng(Switch([Visio Status]<3 Or [HL Status]<3,1,[Visio
Status]=6 And [HL Status]=6 And [TC Status]=6,3,True,2)) AS [Overall
Status]
FROM DocumentTable
WHERE (((DocumentTable.Document)=[Forms]![FormDocument]!
[DocumentCombo]));

 

[John W. Vinson]

Hi,

In my query I list some fields from a table and would then like to
calculate another one based on the presented values.

The query that I now have is the following:

SELECT DocumentTable.Document, DocumentTable.[Visio Status],
DocumentTable.[HL Status], DocumentTable.[TC Status], DocumentTable.
[Script Status], Switch([Visio Status]<3 Or [HL Status]<3,1,[Visio
Status]=6 And [HL Status]=6 And [TC Status]=6,3,True,2) AS [Overall
Status]
FROM DocumentTable
WHERE (((DocumentTable.Document)=[Forms]![FormDocument]!
[DocumentCombo]));

How can I force the last column (Overall Status) to be of numerical
value? All used columns are numerical, and the values from the Switch
clause are (at least should be) also numbers. So why does it not let
me define the column to be of numerical type?

What I'm trying to do is to assign the last column a value based on
combinations of the other columns.

Try wrapping the Switch() function call in a CInt() or CLng() function.

 

[takatam]

Hi All,

thanks a lot for your answers. I'm currently trying to check them.

However, it seems that I have another problem elsewhere so I need to
fix that one first.

Thanks again,
T

Hi,

In my query I list some fields from a table and would then like to
calculate another one based on the presented values.

The query that I now have is the following:

SELECT DocumentTable.Document, DocumentTable.[Visio Status],
DocumentTable.[HL Status], DocumentTable.[TC Status], DocumentTable.
[Script Status], Switch([Visio Status]<3 Or [HL Status]<3,1,[Visio
Status]=6 And [HL Status]=6 And [TC Status]=6,3,True,2) AS [Overall
Status]
FROM DocumentTable
WHERE (((DocumentTable.Document)=[Forms]![FormDocument]!
[DocumentCombo]));

How can I force the last column (Overall Status) to be of numerical
value? All used columns are numerical, and the values from the Switch
clause are (at least should be) also numbers. So why does it not let
me define the column to be of numerical type?

What I'm trying to do is to assign the last column a value based on
combinations of the other columns.

Try wrapping the Switch() function call in a CInt() or CLng() function.