Make Table Query looses Format

[Guest]

PersonID: Nz([tblClients.RepresentativeID],[tblClients.ClientID])

I am using the above query to display the RepID if there is one, if there
isnt, then to display the ClientID. However, when I used a make table query,
the new field in the new table is created as a text field and not a number
field. Is there a way to force it to save the field as a number? (I have
Access 2003)

Many thanks

 

[Allen Browne]

JET (the database engine in Access) is unable to determine the data type
with Nz(), but you can explicitly typecast it.

Assuming [tblClients.ClientID] can never be null, use:
PersonID: CLng(Nz([tblClients.RepresentativeID],[tblClients.ClientID]))

More about typecasting:
http://allenbrowne.com/ser-45.html

A better solution might be to set up the table exactly as you want it (data
types, formatting, AutoNumber if you wish, ...), and then use an Append
query to populate it, instead of a Make Table. It's easy enough to clear the
table out again, with a macro that uses RunSQL with a statement like this:
DELETE FROM [Table1];