Format of dates in a union query different from the table

[Spudlover]

I have created a union query combining dates of birth and wedding dates where
the date shows mmddyyyy. Sorted by the year.

The table it came from is medium date, ddmmmyyyy.

Part 1-How do I get the union query to be medium date (like the table)?

Part 2 -I want to sort so that any event on January 1 is followd by any
events on January 2...., reguardless of the year. How do I write the
forumula?

Part 3-Where do I find properties so that I can change union queries?

Thanks

 

[Tom Ellison]

Dear Spud:

This seems to confuse me.

Why would you create a union query if it comes from a single table?

I recommend you provide the SQL code and details, such as datatypes for what
you're doing here.

Tom Ellison

 

[Pat Hartman\(MVP\)]

Dates are stored as double precision numbers. The integer portion of the
number counts the days since Dec 30, 1899 and the decimal portion represents
a time of day. How dates are formatted is simply a display issue and has
NOTHING to do with how they are stored. If you are having sorting issues
with a date field, it is because you formatted it in your query. Formatting
turns a date field into a text field. That makes it sort like a text field
rather than a date field. The only way you can properly sort a text date is
if the text field is formatted in year, numeric month, day order as in
yyyymmdd. When you format it as ddmmmyyyy, the primary sort order is day,
then text month, then year.

The solution is to sort on the unformated date field. You can still format
the date field in the select clause if you want some format other than the
system default.

 

[Spudlover]

It did come from the same table, but previous instructions said I need a
union query to put BIRTH DA and WED DA in the same column of a query. And
that part works OK except the query shows the date as mmddyyyy. The table it
is from shows the date ddmmmyyyy (medium date) I want the query results to
be medium date like the table. ( I can't find anyway to check or change
properties on a union query)

My titled DCC has fields of CALLED, LAST NAME, SPOUSE, BIRTH DA, and WED DA
among others. The end result is that I want a single list of events on
January 1 followed by any event on January 2... Year is of no consequence.

Currently the column sorts on the year. I want it to sort on the month then
the day.

I want to send both types of greeting cards to everyone on the roll. Until
now I have been using two different lists, one for birthdays and one for
anniversaries, I would like them combined

This is what I was given to bring the two together. SELECT [BIRTH DA] AS
EDate, [CALLED] & " " & [LAST NAME] AS who FROM DCC WHERE [BIRTH DA] IS NOT
NULL
UNION ALL
SELECT [WED DA], [CALLED] & " & " & [SPOUSE] & " " & [LAST NAME] FROM DCC
WHERE [WED DA] IS NOT NULL

Hope you can wade through this additional information and help me solve my
dilema.

 

[Van T. Dinh]

Union Queries don't have facility for us to format the Field (AFAIK) since
in a Union Query, we can combine 2 or more different data types in one
column.

Use the Format Property of the Control when you use this combined Field in
Forms / Reports instead.

I think you can use the Format() function in the Union Query to format to
your requirement but Format() returns Variant of String subtype so you have
to set additional column(s) for sorting, not using the calculated column
with Format() for sorting.

 

[Tom Ellison]

Dear Spud:

You are being forced to use a UNION query because the table is malformed. A
good design for the table would have had only one date per row, with
separate rows for birth and wedding dates. A UNION query is a patchwork
solution for this failure, and that's not uncommon when you have a problem
like this.

Medium date is a format, not a datatype. Formatting is the LAST thing that
is performed. The union query will produce date/time values (if that's the
source of the column) which you may format as you wish. But a format is
just a way of displaying the data, not the data itself. That's a very
important thing not to be confused.

In a query, sorting on a date is just like sorting on anything else. Use
ORDER BY in the text version of the query.

I'm not sure what properties you want to change. The Design View is never
available for a UNION query. Everything that is a property of a query can
be coded in the SQL instead. So, what properties do you need?

Tom Ellison

It did come from the same table, but previous instructions said I need a
union query to put BIRTH DA and WED DA in the same column of a query. And
that part works OK except the query shows the date as mmddyyyy. The table
it
is from shows the date ddmmmyyyy (medium date) I want the query results
to
be medium date like the table. ( I can't find anyway to check or change
properties on a union query)

My titled DCC has fields of CALLED, LAST NAME, SPOUSE, BIRTH DA, and WED
DA
among others. The end result is that I want a single list of events on
January 1 followed by any event on January 2... Year is of no
consequence.

Currently the column sorts on the year. I want it to sort on the month
then
the day.

I want to send both types of greeting cards to everyone on the roll.
Until
now I have been using two different lists, one for birthdays and one for
anniversaries, I would like them combined

This is what I was given to bring the two together. SELECT [BIRTH DA]
AS
EDate, [CALLED] & " " & [LAST NAME] AS who FROM DCC WHERE [BIRTH DA] IS
NOT
NULL
UNION ALL
SELECT [WED DA], [CALLED] & " & " & [SPOUSE] & " " & [LAST NAME] FROM DCC
WHERE [WED DA] IS NOT NULL

Hope you can wade through this additional information and help me solve my
dilema.

Dear Spud:

This seems to confuse me.

Why would you create a union query if it comes from a single table?

I recommend you provide the SQL code and details, such as datatypes for
what
you're doing here.

Tom Ellison

 

['69 Camaro]

Hi, Joy.

You asked me in another thread to take a look at your date formatting
problem for your UNION query. Sorry I took so long. The queries were the
easy part, but I also wanted to explain the "how" so that you'll understand
it better.

Part 1-How do I get the union query to be medium date (like the table)?

Use the Format( ) function with "medium date" as the parameter. For example:

Format([BIRTH DA], "medium date")

Part 2 -I want to sort so that any event on January 1 is followd by any
events on January 2...., reguardless of the year. How do I write the
forumula?

You'll need to identify the month and day of each date, place them each in
separate columns, and then sort the query on those two columns. For example,
the DatePart( ) method can provide the month, in digits:

DatePart("m", [BIRTH DA])

From the other thread, here's your query with these changes:

SELECT Format([BIRTH DA], "medium date") AS EDate,
[CALLED] & " " & [LAST NAME] AS who,
DatePart("m", [BIRTH DA]) AS Mo,
DatePart("d", [BIRTH DA]) AS Da
FROM DCC
WHERE [BIRTH DA] IS NOT NULL
UNION ALL
SELECT Format([WED DA], "medium date"),
(Called & " & " & Spouse & " " & [LAST NAME]) AS Couple,
DatePart("m", [WED DA]) AS Mo,
DatePart("d", [WED DA]) AS Da
FROM (SELECT IIf((P.ID>S.ID),P.ID,S.ID) AS Cut
FROM qryFindNearDuplicates AS P INNER JOIN
qryFindNearDuplicates AS S ON (P.Pair=S.Swap)
AND (P.[LAST NAME]=S.[LAST NAME])) AS Q
RIGHT JOIN DCC ON Q.Cut = DCC.ID
WHERE (ISNULL(Cut) = TRUE) AND [WED DA] IS NOT NULL
ORDER BY 3, 4;

.. . . where ID is assumed to be the name of the primary key in your table
(you'll need to replace it with the name of your primary key that used to be
named "Index"). However, this displays two extra calculated columns in your
query, Mo and Da. If this is not acceptable, then you can use an interim
query so that the final query only displays what you want, the date of the
event, and the "who" of the event. Save the following SQL statement in the
SQL View pane, and name it qryEvents:

SELECT Format([BIRTH DA], "medium date") AS EDate,
[CALLED] & " " & [LAST NAME] AS who,
DatePart("m", [BIRTH DA]) AS Mo,
DatePart("d", [BIRTH DA]) AS Da
FROM DCC
WHERE [BIRTH DA] IS NOT NULL
UNION ALL
SELECT Format([WED DA], "medium date"),
(Called & " & " & Spouse & " " & [LAST NAME]) AS Couple,
DatePart("m", [WED DA]) AS Mo,
DatePart("d", [WED DA]) AS Da
FROM (SELECT IIf((P.ID>S.ID),P.ID,S.ID) AS Cut
FROM qryFindNearDuplicates AS P INNER JOIN
qryFindNearDuplicates AS S ON (P.Pair=S.Swap)
AND (P.[LAST NAME]=S.[LAST NAME])) AS Q
RIGHT JOIN DCC ON Q.Cut = DCC.ID
WHERE (ISNULL(Cut) = TRUE) AND [WED DA] IS NOT NULL;

This is the interim query. You might notice that it's identical to the
earlier query, but it has no sorted columns. The sorting will be done in the
final query.

And just to clarify a little bit further on the matter of duplicates (or
near duplicates) in the table, due to the unnormalized structure of the
table, the second half of the UNION query (for the wedding dates) is
specially designed to eliminate these duplicates. However, the first half of
the UNION query (for the birthdates) doesn't eliminate duplicates because you
weren't complaining of duplicate birthdates, so that part of the query was
left as is. And when you look at the two halves of the query, you can see
that fixing the structure of the table would prevent having to create a
convoluted query just to display the data correctly. If you want help and
have the time to fix this problem, please post new questions asking how to
normalize the table, and how to change any of the queries you have already
created to accommodate this unnormalized table into queries for the new table
structure.

For the final query, create a new query and paste the following into the SQL
View pane:

SELECT EDate, who
FROM qryEvents
ORDER BY 3, 4;

Part 3-Where do I find properties so that I can change union queries?

The field properties that you've asked about are so that you can format the
dates, but that's not available in UNION queries, as you've already found
out. The solution is to use the Format( ) method in the SQL statement as
shown above.

If you have any further problems with this query, please post back.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.