Is it possible to maketable and change field?

[BurtonBach]

I have a very simple make table query and creates a table in a separate database. Is it possible to also change the type of field of the first field at the same time?

Specifically, the original table has this field as 'text'. While I would like the new table in the separate database to have this field as a 'number' (single). All the data except one line fits this type and I was going to exclude that line in my where statement as follows:

SELECT [jcdetail].[jobnum], [jcdetail].[phasenum], [jcdetail].[billqty] 
INTO jcdetail IN 'C:\Documents and Settings\jbach\Desktop\Jworking.mdb'
FROM jcdetail
WHERE ((([jcdetail].[jobnum])<>"T2006"));

[BurtonBach]

I believe I may have figured out my own answer. Does anyone have a better way to do this?

SELECT Format([jobnum],"General Number") AS jobnumn, [jcdetail].[phasenum],  [jcdetail].[billqty] 
INTO jcdetail IN 'C:\Documents and Settings\jbach\Desktop\Jworking.mdb'
FROM jcdetail
WHERE (((Format([jobnum],"GeneralNumber"))<>"T2006"));

[Stewart Ross]

Hi. The format function is perhaps not your best choice, as it returns a text string (a formatted text string for sure, but still a text string). I'd suggest you use one of the explicit type conversion functions instead - and in particular CLng, CSng or CDbl (conversion to long integer, single-precision or double-precision values respectively).

If your job number field is a whole number it is not a single-precision value you need (which is a low-precision floating-point type) but a long integer, unless the job numbers would exceed 2147483647 (the maximum for a long type). If you need more range you'd need to change to a double-precision value instead - a single will not do, as it has only seven significant digits of precision.

The function is used like this:

SELECT CLng([jobnum]) as [JobNumN], ...

ps your WHERE clause does not need to use the format function at all to exclude the line where the job number is invalid (just as well, because "T2006" will not format as a number). You just need

WHERE [jobnum] <> "T2006";

-Stewart

[BurtonBach]

I appreciate your detailed response. It is very helpful!!

[BurtonBach]

I have a new wrinkle on this. How do I deal with Null values? There are about 10% null values in the jobnum field that we are converting using the CLng and those fields are coming up #Error.

Here is my current code using the suggestions received:

SELECT CLng([jobnum]) AS jobnumn, salesregister.cusnum, salesregister.cusname, salesregister.invnum, salesregister.invdate, salesregister.grossamt, salesregister.taxableamt, salesregister.nontaxableamt, salesregister.deptnum 
INTO [Sales Register] IN 'C:\Documents and Settings\jbach\Desktop\Jworking.mdb'
FROM salesregister;

[Stewart Ross]

Leaving aside what it means to have no job number in the table, the simplest way to deal with nulls is convert them to zeros using the Nz function.

SELECT CLng(Nz([jobnum], 0)) As JobNumN, ...

Nz actually returns a text value, which is no problem here as the non-null numeric text string returned by Nz in this case is converted to a long by CLng just as before.

-Stewart