Number fields being converted to text

  • Thread starter Thread starter PatrickM
  • Start date Start date
P

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
 
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
 
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

Dale Fye said:
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.



PatrickM said:
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
Click to expand...
Click to expand...
 
What is presented that leads you to believe the results are text?

--
KARL DEWEY
Build a little - Test a little


PatrickM said:
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

Dale Fye said:
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.



PatrickM said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Dale Fye said:
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.



PatrickM said:
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
Click to expand...
Click to expand...
 
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

KARL DEWEY said:
What is presented that leads you to believe the results are text?

--
KARL DEWEY
Build a little - Test a little


PatrickM said:
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

Dale Fye said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

PatrickM said:
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

Dale Fye said:
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.



PatrickM said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top