UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Union Query - Field Formatting, Access 2016    
 
   
Trelaina
post Aug 16 2019, 01:23 PM
Post#1



Posts: 24
Joined: 17-June 13



Hi, I have written a union query with two tables - one with details of complex projects and one for minor project/tasks. Table1 has a LOT of fields, Table2 only has a handful. I allowed for that by mapping "Null AS...." for any column in my union query didn't have an equivalent Table2 field.

The issue I'm now having is that most of the Table1 fields in question are date fields, and in subsequent forms and reports, the fields are being treated as text and not date. I did some searching online and it seems that in a union query, if one field is text, the resulting field is always treated as text no matter what the format is in the other table.

Does anyone have a way that I can rewrite the union query so that the resulting combined field is treated as a date? It wouldn't be my preference (as the subsequent reports/forms would be impacted), but would hardcoding a dummy date (such as 1/1/1900) do the trick?
Go to the top of the page
 
theDBguy
post Aug 16 2019, 01:31 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,290
Joined: 19-June 07
From: SunnySandyEggo


Hi. In your union query, did you add the data from Table1 first? Just curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Aug 16 2019, 02:09 PM
Post#3


UtterAccess VIP
Posts: 10,954
Joined: 10-February 04
From: South Charleston, WV


You say the resulting column is being treated as text. Confirm that if you omit the Null file from the union, or if you use #01/01/1900# instead of null, that the result is treated as a date and it's only when you change #01/01/1900# to Null that it changes into text.

--------------------
Robert Crouser
Go to the top of the page
 
Trelaina
post Aug 16 2019, 03:07 PM
Post#4



Posts: 24
Joined: 17-June 13



Hi, table2 is listed first in my query. I can try flipping it and see if that helps. If not I'll try the other suggestion that was made. Will report back shortly, hopefully with good news!
Go to the top of the page
 
Trelaina
post Aug 16 2019, 03:21 PM
Post#5



Posts: 24
Joined: 17-June 13



Unfortunately none of those suggestions worked. Switching the order of the tables and changing the "null" mapping to #01/01/1900# had no effect. Removing the null entries caused an error that the number of columns didn't match (which I believe is why I added them in the first place).

I'm more surprised by the hardcoded date not working than the others. I'm going to dig a little deeper.

EDITED for typo
This post has been edited by Trelaina: Aug 16 2019, 03:26 PM
Go to the top of the page
 
theDBguy
post Aug 16 2019, 03:46 PM
Post#6


Access Wiki and Forums Moderator
Posts: 76,290
Joined: 19-June 07
From: SunnySandyEggo


Hi. If you still need help figuring it out, you might consider posting a demo copy of the db with test data.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
dale.fye
post Aug 16 2019, 03:57 PM
Post#7



Posts: 160
Joined: 28-March 18
From: Virginia


I would create a new query with the Table1 fields first, followed by table2 fields as the 2nd part of the Union.

Date fields can be NULL, so I would be surprised if this doesn't work

The reason I would try this with a new query is that somtimes, when you save a query and then edit it, the columns don't change order as you expect unless you create a new query with the columns in the order you want. You might find that Access has already evaluated those columns and decided they are text. Give it a try.

Dale

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
projecttoday
post Aug 16 2019, 07:33 PM
Post#8


UtterAccess VIP
Posts: 10,954
Joined: 10-February 04
From: South Charleston, WV


What I meant was for you to eliminate the query with the nulls in it completely from the union and see what that does. As Dale says, null is allowed in dates. If the result of the table with the dates is still text then the problem was never in the nulls to begin with.

--------------------
Robert Crouser
Go to the top of the page
 
Trelaina
post Aug 22 2019, 07:48 AM
Post#9



Posts: 24
Joined: 17-June 13



Thank you everyone. Deleting and recreating the union query from scratch, with table1 listed first, appears to have done the trick. This seems to be the "turn it off and back on" tip for Access smile.gif The DB I'm working on is a complete rewrite of an older one as well - as it had become massive and full of errors over a couple of years of use.

This forum has been a huge help to me - and once I roll this database out, to my team as well!! Thanks again.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th September 2019 - 09:12 AM