I have a union query that works pretty well.  But now I want to make the year
in a date field only two digits.  But the Union Query doesn't know that field
is a date.  The field isn't even a field in the first of the two queries.

Where is says:

   Null AS Start

I want it to know it is a date field, not a text.  So how do I get that first
field below to be a date?  I checked help and it isn't clear how to write the
Format function in this case.  Thanks.

SELECT Null AS Start, 0 AS SellingPrice, 0 AS StoreUnits, Null As StoreCancel,
Null AS RepName
FROM [DDAll-SchedQry]
UNION ALL SELECT StoreOrderStart, StoreOrderFOB, StoreOrderSizeM,
StoreOrderCancel, StoreOrderRepName
FROM [DDAll-OpenOrdersqry]

Thanks,

Matt

Youcan use the CDate() funtion to convert a string to a date field. ie
CDate("1/1/03"), then you can ensure it shows a two digit year by using the
Format() function.  Hope that points you in the right direction.
--
Scott N. Weber, MCP
www.AccessYourBiz.Com
Customizable Accounting Software
Written with Microsoft Access.

 SELECT Null AS Start, 0 AS SellingPrice, 0 AS StoreUnits, Null As
StoreCancel,
 Null AS RepName
 FROM [DDAll-SchedQry]
 UNION ALL
 SELECT Format$(StoreOrderStart, "mm/dd/yy"), StoreOrderFOB,
StoreOrderSizeM,
 StoreOrderCancel, StoreOrderRepName
 FROM [DDAll-OpenOrdersqry]

Recognize, though, that the dates will now be strings, so they won't sort
properly.

If you're using your Union query on a form, just format the Textbox bound to
Start. The Format function knows how to treat Nulls (or you can explicitly
tell it, such as mm/dd/yy;;;\N\u\l\l)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

Could you just reverse the order of the two queries making up the union?  Don't
know if that will work or not to force datatype but I would try that.

SELECT StoreOrderStart, StoreOrderFOB, StoreOrderSizeM,
StoreOrderCancel, StoreOrderRepName
FROM [DDAll-OpenOrdersqry]
UNION ALL
SELECT Null AS Start, 0 AS SellingPrice, 0 AS StoreUnits, Null As StoreCancel,
Null AS RepName
FROM [DDAll-SchedQry]

When you do a Union Query lining up a date field with a nondate field, the
resulting query marks the field as text.  So using mm/dd/yy failed me.  It
didn't change it in the form.  In other words, when I press that property, no
date choices come up.  

I cannot always place the query with the date first because both queries in the
Union Query have date fields that will be null.

My problem is that it is Null in one query AND I have to format it.  Even if I
knew what function, I simply don't have any idea how to write it.  Is there a
way to format this as a date field in the Union Query?  Is so, please show me.
If not, should I format it in another query?

My dirty solution is to actually give the nulls a date, a bogus date.  Than on
the form I write code that if field is that bogus date, make it invisible.  But
this seems sloppy to me.  I'd rather know how to really write the Union Query.

I don't understand the "\N\u\l\l" part of "mm/dd/yy;;;\N\u\l\l."  I'm pretty
sure the mm/dd/yy, however, didn't do a thing in my case.  

Thanks for your kind help,

Matt

This is confusing me: Date columns are simply dates, and don't have two-
figure years or four-figure ones or anything, they are just numbers. On the
other hand, if you want to present something like "dd/mm/yy" then you are
talking about string (text) values. Either of these is fine but the cboise
will affect what you want to do with the null values.

If you want Date values, then a NULL itself should do fine: it will
probably be formatted blank in the destination (form? worksheet?
textfile?).

If you are using Text values, (much easier in many ways) then you can
choose NULL itself, "" (empty string), "No Date" (helpful user message),
"######" (microsoft standard) and so on.

I think you have to make up your mind about what you actually want from the
resulting recordset, which depends on what you are going to do with it.

Hope that helps

Tim F

Tim,
All I know is what I see.  When I have a Union query and both query fields are
pure dates, Access will than see it as a date in the form based on that union
query.  When I build a date field from the Union query where one of the fields
is not a date, but Null, Access doesn't see it as a date in the form or report.
 It thinks it's a text field.

However, my dates are built and not pure.  I have to change a mainframe's date
which may be expressed as 20031231 (for December 31, 2003).  I use the left,
right and mid fuctions with & slashes (/) to make it into 12/31/2003.  

So this is where a misunderstanding may be.  

My solution was instead of calling the non-date field simply Null AS MyDate,
but to put #01/01/88# AS MyDate.  These makes it a date automatically.  No
nulls, no text.

But I must say I get the same problem with Nulls when one field is a number and
the other is a Null field.  If I make a union and bring it into a form, I
cannot add up the numbers.  Access sees the field as Text and not Numberic.
Therefore instead of writing Null as MyNumberField, I write 0 as MyNumberField.
 Then Access will see it as a number in the form or report and I can perform
additions, etc.

Instead of using my querky ways around this problem, I thought I could learn
how to define the fields in the Union Query first.  It seemed to me that that
would be the professional way to go about things.

Since it happens that way with a number field, I didn't think it mattered that
I "built" the date.  Perhaps it does.  I don't know.  

Matt

If you're using "the left, right and mid fuctions with & slashes (/) to make
it into 12/31/2003", you haven't made a date: you've made a string. You need
to either use CDate or DateSerial to create a date.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

Yes, I won't argue with you.  But when passing the built date through a Union
query with all results as dates, no nulls, Access will take it as a date
without the user having to format it.  Same happens with numbers.  But if you
have nulls, caused by one of the queries in the union query not having a
corresponding field, Access will not assume the field a date nor a number
field.  It will be a text field only.

Hence, my initial question.  The only way around this is to make sure you have
no nulls.  Create false dates or numbers.

Matt

The column types are defined by the column types in the first SELECT in
a union query. If subsequent selects present a data type that cannot be
coerced into that type then the query fails.

I didn't know that "SELECT NULL AS Nothing" would produce a text column,
so I have learned something, but I cannot think of a reason why I would
ever write a query like that -- it just seems a bit redundant.

To return to your original post, which contained the following:

I cannnot for the life of me understand why you want to return as many
blank rows as there are in DDAll-SchedQry, particularly when it's just
going to{*filter*}up the column definitions for the following part.

Perhaps if you explained a little of what you are trying to achieve, we may
be able to help a little more..?

Tim F

Well, I want to make it perfectly clear that my report works now and I have
succeeded very well.  My report combines two sets of data, both completely
different from each other, except for one Field.  That field is used to order
the data by date.  There are other date fields that don't have anything to do
with the order in both sides.

In short, one side represents the Production---with a date for when that
Production---panties, of all things---gets in the warehouse.  The other side
represents Store Orders---with dates for start ship and cancel ship.

Since I want all this information on a chart right and left, ordered very
specifically, and since I want to do sums, and running sums, format fields
(date or otherwise), etc, each field must be specific to what it actually is.
There is no way to do this report using subreports.

I even combine things that make no sense whatsoever.  Instead of the PO number
for Production, in one of the queries (there are actually 4), I tell the system
to put the words "On Hand" instead of the PO.  No problem because I wouldn't
want to sum a PO column.  I also put in the late days of the shipment if the
shipment doesn't have a shipment number.  Same column---makes report easier and
nicer looking.

I seem to do this sort of thing often.  I am very {*filter*}about making charts look
great!  Combining nulls with dates or substituting words instead is very
common.

Since each of the two queries have dates, I cannot solve the problem by having
one first.  Both need to be first. But in my experience even the first one
doesn't define it as a date.

But I was looking for an easy solution to formatting a null field in a union
query.  Since there doesn't seem to be one, I guess it doesn't matter.  I can
solve it my way.

Thanks for your kind attention,

Matt

I'm pleased about that. I still don't understand what you are doing but
then again I guess I don't have to!

B Wishes

Tim F