Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

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.
0
cdarbonne
Asked:
cdarbonne
  • 7
  • 6
1 Solution
 
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
 
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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
 
Rey Obrero (Capricorn1)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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now