CAST in SQL Statement

[Nats]

I have the following SQL statement set out in my union query.

SELECT [Field1], [field 2]
FROM [Table 1]
UNION
SELECT [Field 1], [field 2]
FROM [Table 2]

However I need to make field one numeric, so I can have this 00000 format in the column. I think I need to do this by using CAST and NUMERIC (5) but I am not sure how I would set this out in my SQL statement as when I do it comes back with Syntax error or argument.


Please can anyone help?

 

[chergh]

I believe that neither CAST or CONVERT are available to use in Access. However you can use the VBA function "CLng". Try this:

SELECT CLng([Field 1]), [field 2]
FROM [Table 1]
UNION
SELECT CLng([Field 1]), [field 2]
FROM [Table 2]

If field 1 may contain null values you will want something like:

SELECT CLng(nz([Field 1],0)), [field 2]
FROM [Table 1]
UNION
SELECT CLng(nz([Field 1],0)), [field 2]
FROM [Table 2]

 

[Nats]

Thanks for you help with this - that did do something but what I am trying to get is in the table for field 1, I have say for example '00002' (and numbers like that i.e. 00003 however when I created the sql union query they became in column as just '3' or '2'.

Please can you suggest what you think I should do to my SQL statement (set out like you did in the last email) so they are presented like they were in the table '00002' or '00003'.

Thank you advance

Nats

 

[chergh]

Try:

SELECT RIGHT(FORMAT(CLng(nz([Field 1],0)),"00000#####"),5), [field 2]
FROM [Table 1]
UNION
SELECT RIGHT(FORMAT(CLng(nz([Field 1],0)),"00000#####"),5), [field 2]
FROM [Table 2]

 

[Nats]

Chergh - Thank you so much

Chergh

It worked! Just want to say a massive thank you to you. You are a genis!

Thanks

Nats

 

[neileg]

Just be a little careful here. Using FORMAT() like this renders the field as text so if you want to use the field to do calculations, you're going to have problems. It may be prefereable to leave the field as numeric and apply the format in the form or report where the data is displayed.

 

[chergh]

Just be a little careful here. Using FORMAT() like this renders the field as text so if you want to use the field to do calculations, you're going to have problems. It may be prefereable to leave the field as numeric and apply the format in the form or report where the data is displayed.

Hmmm given that also makes the use of the CLng function in the statement rather useless, rather annoyed that my favoured access book doesn't give you this info.

 

[neileg]

Hmmm given that also makes the use of the CLng function in the statement rather useless.

Well, except that if you don't render the field as an integer then you can't apply the required format. I think you answered Nats original question exactly. I just wondered if it was the right question!