I have the following in a Union Query.  But a report that runs off this doesn't
seem to know that some fields are Dates, some text and some numbers.  I need to
format the following:

SchedETD and SchedETDCom:  Date
SchedShipMode and SchedShipModeCom:  Text
SchedUnits and SchedUnitsCom: Number  (Fixed, no decimal).
___________________

SELECT PO, Counter, SchedETD, SchedShipMode, SchedUnits, POCom, CounterCom,
SchedETDCom, SchedShipModeCom, SchedUnitsCom
FROM SchedCompareqry1
UNION ALL SELECT PO, Counter, SchedETD, SchedShipMode, SchedUnits, Null, Null,
Null, Null, Null
FROM SchedCompareMissingqry2;
______________________

I can't seem to do it.  The reason why this seems to have to be done is, I have
the following code in the report:

    If SchedUnits = SchedUnitsCom Then
        Me.SchedBox.Visible = 0
    Else
        Me.SchedBox.Visible = 1
    End If

    If SchedShipMode = SchedShipModeCom Then
        Me.ModeBox.Visible = 0
    Else
        Me.ModeBox.Visible = 1
    End If

It doesn't seem to recognize that the fields are dates.  I also wish to
subtract the two dates: =[SchedETD]-[SchedETDCom].

It can't do that as of now.  All this used to work when it was based on a
regular query.  But since I changed to a union query (for some other data), it
failed.  Thanks,

Matt

Hi,

The supplied CDate, DateDiff, and StrComp functions (see online help) might
be able to solve all of those issues for you.

--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"

Mass Emailer - http://www.CalvinSmithSoftware.com/MassEmailer.htm

VB/VBA Tools - http://www.CalvinSmithSoftware.com/AllComboDeals.htm

Thanks but that doesn't seem to be my problem.  The query doesn't know that the
field is a date.  I need to format the query field as a date or a number or a
text.  The reason I need to do this is so I can use the functions you list
below.

Union Queries have a tendency to loose the format of fields.  I've dealt with
this before but can't find how I solved it before.

Matt

Okay, well, if you mean that using a...

Select CDate(MyTextDateColumn1) As NewDate, blah...
Union [All]
Select CDate(MyTextDateColumn2) As NewDate, blah...

...is NOT going to give you a Date formatted output column, then you may
want to append your Union query results into a temp table first and base
your report off that pre-formatted temp table.

--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"

Mass Emailer - http://www.CalvinSmithSoftware.com/MassEmailer.htm

VB/VBA Tools - http://www.CalvinSmithSoftware.com/AllComboDeals.htm

Yes, that is what happens.  The Union Query loses the fact that it is a date or
a number where as when the report was based on a regular query, all was
hunky-dory.  Your solution is exactly what I have done in the mean time!  Is
there no way to format these Union Queries?  That's a shame.

Matt