SQL format

[frederik]

Hi,


I'm using this sql syntax to append data into a new table (+ some additional columns 'test1' and 'test2'):

strsqlb = "SELECT *, format('25','currency') as test1, 0 as test2  INTO table2 FROM (select * from table1);"

I would have expected that in field 'test1', the format of the data is CURRENCY.

What I see in reality, is that the field 'test1' is formatted as SHORT TEXT and shows $25.00 when I open the table.

What do I have to do/change so the datatype is CURRENCY instead of SHORT TEXT?

 

[Ranman256]

What is the actual FIELD TYPE of the field? NOT the format.
if it is currency then you can post it as 25,
NOT $25. dont use the currency sign. You can format it anyway you want.

if the type is Text, then the 'currency' FORMAT may not work on text, but does work on numbers.

 

[pbaldy]

The Format() function returns a string, so that's what gets created. Shot in the dark, but try CCur(25) instead.

 

[frederik]

okay, it becomes clear, format() always returns text.

Say I have the following SQL:

strsqlb = "SELECT * INTO table2 FROM (select * from table1);"

what do I have to change in this syntax if I want to add one extra column to table2 (let's call it test1) which datatype is a double?

 

[pbaldy]

Did you try the conversion function? Try

SELECT *, CDbl(123.45) AS Test INTO table2 FROM table1

 

[frederik]

indeed, works well. just one more question: what if I want no initial value for this variable in the first place?
I tried :
SELECT *, CDbl(null) AS Test INTO table2 FROM table1

but that doesn't seem to work.

 

[The_Doc_Man]

If the table doesn't exist yet, try a two-step operation.

First, create the table (empty) with all fields correctly declared by type and name.

Next, use an ordinary append such as:

INSERT INTO [I]destination-table[/I] ([I]field-list[/I]) SELECT [I]field-list-with-conversions[/I] FROM [I]source-table[/I] ;

where the field-list-with-conversions includes such things as CDbl(x), CCur, CLng(z), etc. - with the conversions corresponding to the field types intended for the destination table.

 

[Gasman]

indeed, works well. just one more question: what if I want no initial value for this variable in the first place?
I tried :
SELECT *, CDbl(null) AS Test INTO table2 FROM table1

but that doesn't seem to work.

You'd omit it from the sql, wouldn't you?

 

[frederik]

You'd omit it from the sql, wouldn't you?

Not really, I would like to have an empty column with the correct datatype, which can be filled in later. I thought a one-step solution was possible, but I understand from The_Doc_Man it has to be done in 2 steps.
Thank you all.

 

[The_Doc_Man]

If you want that empty column, you need to do either of two things:

1. Pre-create the table as I suggested, and this is never a BAD thing, just a little tedious sometimes.

2. Use the SELECT ... INTO syntax, but have a place-holder for the empty column, as

SELECT CLng(X), CDate("1-Jan-100"), CCur(Z), etc. INTO etc.

For dates, pick a "dummy" date that makes no sense as anything other than a place-holder. For my recent geneaology project, I found that a date such as #1-1-100# (yes, January 1st of the year 100 CE, the earliest date that Access can represent) was a good enough "impossible" date that nonetheless conformed to date-handling rules. The other impossible date is #12-31-9999# (the day before year 10,000 CE and the latest date that Access can represent).

You can also use the date corresponding to a date value of 0 (which is the Access "reference date"). I always get these confused because Excel and Access use two different reference dates. I THINK 31-Dec-1899 is the Access reference day (=0), but if not, then it is 1-Jan-1900 (and Excel uses the other one as its reference). Doesn't matter which date you want, it is the fact that the date variable contains 0.0 as its value.