Number fields being converted to text

[PatrickM]

Some of the number fields (long integer, single and double formats) in my
query are being converted to text but this is not what I want. It so happens
that I used the Nz function to subsitute dummy values into these fields. Is
this what converted them to text or is it something else? I'm using Access
2003.

Thanks,
Patrick

 

[Dale Fye]

Patrick,

It would help if we saw your query, . You may need to explicitly type some
of your computed fields. Something like:

SELECT clng(NZ([SomeField], 0)) as SomeInteger

you can use the clng( ), csng( ), and cdbl( ) functions for this.

HTH
Dale

 

[PatrickM]

Dale, here's the query:

SELECT [PSCP BT no nd sub].Works_num, [PSCP BT no nd sub].Works_name, [PSCP
BT no nd sub].Srce_type, [PSCP BT no nd sub].Srce_type2, [PSCP BT no nd
sub].Srce_name2, [PSCP BT no nd sub].BT_SCP, [PSCP BT no nd sub].Test_code2,
[PSCP BT no nd sub].Parameter, [PSCP BT no nd sub].PSCP, [PSCP BT no nd
sub].Res_ct, Nz([Det_ct],-9999) AS Det_ct1, Nz([Det_rt],-9999) AS Det_rt1,
Nz([Tr_det_ct],-9999) AS Tr_det_ct1, Nz([Tr_det_rt],-9999) AS Tr_det_rt1,
Nz([Pos_det_ct],-9999) AS Pos_det_ct1, Nz([Pos_det_rt],-9999) AS Pos_det_rt1,
Nz([MIN],-9999) AS MIN1, Nz([PERC_25],-9999) AS PERC_251, Nz([PERC_50],-9999)
AS PERC_501, Nz([PERC_75],-9999) AS PERC_751, Nz([PERC_90],-9999) AS
PERC_901, Nz([MAX],-9999) AS MAX1
FROM [PSCP BT no nd sub];

Thanks,
Patrick

Patrick,

It would help if we saw your query, . You may need to explicitly type some
of your computed fields. Something like:

SELECT clng(NZ([SomeField], 0)) as SomeInteger

you can use the clng( ), csng( ), and cdbl( ) functions for this.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Some of the number fields (long integer, single and double formats) in my
query are being converted to text but this is not what I want. It so happens
that I used the Nz function to subsitute dummy values into these fields. Is
this what converted them to text or is it something else? I'm using Access
2003.

Thanks,
Patrick

 

[KARL DEWEY]

What is presented that leads you to believe the results are text?

--
KARL DEWEY
Build a little - Test a little

Dale, here's the query:

SELECT [PSCP BT no nd sub].Works_num, [PSCP BT no nd sub].Works_name, [PSCP
BT no nd sub].Srce_type, [PSCP BT no nd sub].Srce_type2, [PSCP BT no nd
sub].Srce_name2, [PSCP BT no nd sub].BT_SCP, [PSCP BT no nd sub].Test_code2,
[PSCP BT no nd sub].Parameter, [PSCP BT no nd sub].PSCP, [PSCP BT no nd
sub].Res_ct, Nz([Det_ct],-9999) AS Det_ct1, Nz([Det_rt],-9999) AS Det_rt1,
Nz([Tr_det_ct],-9999) AS Tr_det_ct1, Nz([Tr_det_rt],-9999) AS Tr_det_rt1,
Nz([Pos_det_ct],-9999) AS Pos_det_ct1, Nz([Pos_det_rt],-9999) AS Pos_det_rt1,
Nz([MIN],-9999) AS MIN1, Nz([PERC_25],-9999) AS PERC_251, Nz([PERC_50],-9999)
AS PERC_501, Nz([PERC_75],-9999) AS PERC_751, Nz([PERC_90],-9999) AS
PERC_901, Nz([MAX],-9999) AS MAX1
FROM [PSCP BT no nd sub];

Thanks,
Patrick

Patrick,

It would help if we saw your query, . You may need to explicitly type some
of your computed fields. Something like:

SELECT clng(NZ([SomeField], 0)) as SomeInteger

you can use the clng( ), csng( ), and cdbl( ) functions for this.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Some of the number fields (long integer, single and double formats) in my
query are being converted to text but this is not what I want. It so happens
that I used the Nz function to subsitute dummy values into these fields. Is
this what converted them to text or is it something else? I'm using Access
2003.

Thanks,
Patrick

 

[PatrickM]

Dale,
- is it the Nz function that converts the numbers to text?
- Do you know how to prevent the Nz function from doing this in the first
place or is this just something that the Nz function always does? Converting
the numbers to text and then back to numbers is dodgy, maybe there's another
operator or function that I should be using instead of Nz.

Thanks,
Patrick

Patrick,

It would help if we saw your query, . You may need to explicitly type some
of your computed fields. Something like:

SELECT clng(NZ([SomeField], 0)) as SomeInteger

you can use the clng( ), csng( ), and cdbl( ) functions for this.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Some of the number fields (long integer, single and double formats) in my
query are being converted to text but this is not what I want. It so happens
that I used the Nz function to subsitute dummy values into these fields. Is
this what converted them to text or is it something else? I'm using Access
2003.

Thanks,
Patrick

 

[PatrickM]

When I exported the query into dBASEIV and uploaded the dBASEIV file into
ArcGIS, ArcGIS showed the data to be text. So I went back to my query in
Access, converted it from a select query to a make-table query, ran it, and
looked at the resulting table in design view. Design view confirmed that the
numeric fields on which I had used the Nz function were now in text format.

Thanks,
Patrick

What is presented that leads you to believe the results are text?

--
KARL DEWEY
Build a little - Test a little

Dale, here's the query:

SELECT [PSCP BT no nd sub].Works_num, [PSCP BT no nd sub].Works_name, [PSCP
BT no nd sub].Srce_type, [PSCP BT no nd sub].Srce_type2, [PSCP BT no nd
sub].Srce_name2, [PSCP BT no nd sub].BT_SCP, [PSCP BT no nd sub].Test_code2,
[PSCP BT no nd sub].Parameter, [PSCP BT no nd sub].PSCP, [PSCP BT no nd
sub].Res_ct, Nz([Det_ct],-9999) AS Det_ct1, Nz([Det_rt],-9999) AS Det_rt1,
Nz([Tr_det_ct],-9999) AS Tr_det_ct1, Nz([Tr_det_rt],-9999) AS Tr_det_rt1,
Nz([Pos_det_ct],-9999) AS Pos_det_ct1, Nz([Pos_det_rt],-9999) AS Pos_det_rt1,
Nz([MIN],-9999) AS MIN1, Nz([PERC_25],-9999) AS PERC_251, Nz([PERC_50],-9999)
AS PERC_501, Nz([PERC_75],-9999) AS PERC_751, Nz([PERC_90],-9999) AS
PERC_901, Nz([MAX],-9999) AS MAX1
FROM [PSCP BT no nd sub];

Thanks,
Patrick

Patrick,

It would help if we saw your query, . You may need to explicitly type some
of your computed fields. Something like:

SELECT clng(NZ([SomeField], 0)) as SomeInteger

you can use the clng( ), csng( ), and cdbl( ) functions for this.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Some of the number fields (long integer, single and double formats) in my
query are being converted to text but this is not what I want. It so happens
that I used the Nz function to subsitute dummy values into these fields. Is
this what converted them to text or is it something else? I'm using Access
2003.

Thanks,
Patrick

 

[Dale Fye]

Patrick,

NZ( ) can convert an expression or field to almost any data type. That is
why I would explicitly type those computed fields; especially if they are
being exported to another database format.

Dale

Dale,
- is it the Nz function that converts the numbers to text?
- Do you know how to prevent the Nz function from doing this in the first
place or is this just something that the Nz function always does?
Converting
the numbers to text and then back to numbers is dodgy, maybe there's
another
operator or function that I should be using instead of Nz.

Thanks,
Patrick

Patrick,

It would help if we saw your query, . You may need to explicitly type
some
of your computed fields. Something like:

SELECT clng(NZ([SomeField], 0)) as SomeInteger

you can use the clng( ), csng( ), and cdbl( ) functions for this.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Some of the number fields (long integer, single and double formats) in
my
query are being converted to text but this is not what I want. It so
happens
that I used the Nz function to subsitute dummy values into these
fields. Is
this what converted them to text or is it something else? I'm using
Access
2003.

Thanks,
Patrick