union querry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm joining two files, one has anumeric filed that after the querry runs the
out put is text. This has been work fine for years, just happened in the last
week. access2003
 
when i run a union querry acces is changeing the field format from a number
to text. I use the output of this querry to make a chart in excell. before i
would have months 1-12 now 1,10,11,12,2....
 
when i run a union querry acces is changeing the field format from a number
to text. I use the output of this querry to make a chart in excell. before i
would have months 1-12 now 1,10,11,12,2....
Click to expand...

Please post the SQL view of the query. It's absolutly impossible for
anyone to debug a query that they cannot see.

John W. Vinson[MVP]
 
SELECT* FROM[LABOR CHART SHIPPED UNITS 2005 BY OP SUMMARY5 ]

UNION SELECT * FROM [LABOR CHART SHIPPED UNITS BY OP TARGET SUMMARY3]:;
Thi is the sql . summary3 has the months as a number (1-12) after union
query runs the out put is text (1,10,11,12,2...)
 
SELECT* FROM[LABOR CHART SHIPPED UNITS 2005 BY OP SUMMARY5 ]

UNION SELECT * FROM [LABOR CHART SHIPPED UNITS BY OP TARGET SUMMARY3]:;
Thi is the sql . summary3 has the months as a number (1-12) after union
query runs the out put is text (1,10,11,12,2...)
Click to expand...

Then the definition of the months field in SUMMARY3 (which, again, *I
CANNOT SEE*) is apparently incorrect. We're happy to try to help...
but please, please look at your posts as if you knew nothing about
your database and see if maybe you need to add a bit more information!

Guessing: are you using the Format() function to extract the month
from the date field? If so, be aware that the Format() function
returns a string. Try using Month([datefield]) instead.

John W. Vinson[MVP]
 
BEFORE JOIN
FROM SUMMARY3 FROM SUMMARY5 AFTER JOIN
MONTH MTH MONTH EXPR1 MONTH EXPR1
JANUARY 1 2005 0 2005 0
FEBRUARY 2 JANUARY 1
MARCH 3 OCTOBER 10
APRIL 4 NOVEMBER 11
MAY 5 FEBRUARY 2
JUNE 6 MARCH 3
JULY 7 APRIL 4
AUGUST 8 MAY 5
SEPTEMBER 9 JUNE 6
OCTOBER 10 JULY 7
NOVEMBER 11 AUGUST 8
SEPTEMBER 9
SUMMARY3
FROM A TABLE
TEXT NUMBER
INTEGER

I'm sorry if I am not explaining myself.
This union query and others union queries that I have didn't change the
format of the fields before last week. This is the smallest one I have to
show you.
I have a table with the month name (text) and month number( number, long
integer) that I use in a select query to create summary3. This union would
just merge the two files together and mth would remain a number. Now after
the union query the format changes to text. I did no changes to this query
(or the others) don't know what changed in access to make this happen.
Because of the format change to text my charts are Jan,Oct,Nov … or any
calculations (number of units X hours )no longer works.













John Vinson said:
SELECT* FROM[LABOR CHART SHIPPED UNITS 2005 BY OP SUMMARY5 ]

UNION SELECT * FROM [LABOR CHART SHIPPED UNITS BY OP TARGET SUMMARY3]:;
Thi is the sql . summary3 has the months as a number (1-12) after union
query runs the out put is text (1,10,11,12,2...)
Click to expand...

Then the definition of the months field in SUMMARY3 (which, again, *I
CANNOT SEE*) is apparently incorrect. We're happy to try to help...
but please, please look at your posts as if you knew nothing about
your database and see if maybe you need to add a bit more information!

Guessing: are you using the Format() function to extract the month
from the date field? If so, be aware that the Format() function
returns a string. Try using Month([datefield]) instead.

John W. Vinson[MVP]
Click to expand...
 
I have a table with the month name (text) and month number( number, long
integer) that I use in a select query to create summary3. This union would
just merge the two files together and mth would remain a number. Now after
the union query the format changes to text. I did no changes to this query
(or the others) don't know what changed in access to make this happen.
Because of the format change to text my charts are Jan,Oct,Nov … or any
calculations (number of units X hours )no longer works.
Click to expand...

Please post the SQL view of the select query. Posting the *results* of
the query is not helpful (it's impossible to tell if that's 10 or
"10", even without the word wrap illegibility!)

You might possibly have some database corruption; have you tried
Tools... Database Utilities... Compact and Repair, and/or deleting the
UNION query, compacting, and reentering the UNION query? Are you
certain that each of the queries which make up the UNION query in fact
return a number?

John W. Vinson[MVP]
 
Got a work around from mgfoster on my second posting. in the select query add
cbyte(). Still don't know why the format change ifrom number to text in the
union query. The select query column is set to standard, the table it is
using the field is number. still had to do cbyte to make it work.
 
Back
Top