Access Union Query - Inconsistent Date formatting

Hi, I'm working with a union query that brings together 8 or so queries with all identical columns, separated for ease of user entry due to the volume of information in each.

I've formatted the date field in each query to Short Date.  The individual queries look perfect.  When they're viewed in the Union Query, some (seemingly random) rows also show the time alongside the date.  

Any suggestions as to why or how to fix?  Thanks.
cdarbonneAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
you have to use this format, test this one

select [Planned/Actual SOT], IIF([Planned/Actual SOT]<> Null, dateValue([Planned/Actual SOT]),Null)  
from Diesel
0
 
Rey Obrero (Capricorn1)Commented:
try using
 Datevalue([DateField])

instead of format
0
 
cdarbonneAuthor Commented:
Sorry to sound like an idiot - but where do I put that?  the format setting was in the properties dialog.  Is this added to the SQL in the Union Query?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Rey Obrero (Capricorn1)Commented:
<s this added to the SQL in the Union Query?> yes


select datevalue([dateField]) from table1
union all
select datevalue([dateField]) from table2

etc..
0
 
cdarbonneAuthor Commented:
Ok, here's how it looks.  I added the 'as' becuse it was naming it Exp0001.  I must still be doing something wrong becuase I get a "#Error" for some dates now

SELECT Diesel.[Product Family], Diesel.Status, Diesel.[Property/UUT], Diesel.[Test Type], Diesel.Fuel, datevalue (Diesel.[Planned/Actual SOT]) as [Planned/Actual SOT], Diesel.[ESpec CRD], Build_Schedule.[Property/UUT], Build_Schedule.[Customer MRD], Build_Schedule.[Revised Promise Date], Build_Schedule.BSS, Build_Schedule.BAS, Build_Schedule.[Build Location], Diesel.Modified, Diesel.[On HOLD?], ([Planned/Actual SOT]-[Revised Promise Date])/7 AS [PSOT-RPD DTime], (Round([PSOT-RPD DTime],1)) AS [Delta Time]
FROM Diesel LEFT JOIN Build_Schedule ON Diesel.[Property/UUT] = Build_Schedule.[Property/UUT]



Thanks again for the help
0
 
Rey Obrero (Capricorn1)Commented:
check for NULL values on field [Planned/Actual SOT]


run a simple select query

select [Planned/Actual SOT], dateValue([Planned/Actual SOT])
from Diesel

then run this one

select [Planned/Actual SOT], dateValue([Planned/Actual SOT])
from Diesel
where [Planned/Actual SOT] <> Null
0
 
cdarbonneAuthor Commented:
Ok, did it.  Attaching results...

thanks. 1st one 2nd one
0
 
Rey Obrero (Capricorn1)Commented:
do you want to SHOW the NULL values on field [Planned/Actual SOT] in your query?
0
 
Rey Obrero (Capricorn1)Commented:
i am also wondering, how come some records have Time values on field [Planned/Actual SOT].
do you know how this field [Planned/Actual SOT] got filled in or updated?
0
 
cdarbonneAuthor Commented:
No, I'm totally confused about that.  These tables are pulled into Access via Sharepoint.  They're linked tables.  The users enter their data in sharepoint and I pull it in to Access.  I double checked the date/time option in sharepoint and they're all set to date only.  I can't change the table formatting in Access (because Sharepoint is the boss) but I did change it in the query.  In all the queries, it looks great.  Except the union query.  That's where I'm having this issue.
0
 
cdarbonneAuthor Commented:
Oh and yes, I do want to show null values.
0
 
cdarbonneAuthor Commented:
That works great!  Do I need to use "As" to create the properly named column?

Thank you
ee3.JPG
0
 
Rey Obrero (Capricorn1)Commented:
<Do I need to use "As" to create the properly named column?> Yes you can.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.