Date format in Access Union Query

Question

Access 2013

1. I have a number of tables linked to Excel
2. Each table in excel has a date field formatted as mmm-yyyy
3. I have a query for each table to strip out any unwanted rows.  In the query design view I have entered the format mmm-yyyy in the property sheet for the date field
4. When I run the queries in 3) above the datasheet shows the dates in the required format
5. I have a Union query that copies the SQL from the queries in 3) and joins the SQL together with a UNION command
6. The results of the union query returns some dates in the format mmm-yyyy but others in the format dd/mm/yyyy

Does anyone know how to fix this please

Answer 1

Your problem is likely that you are putting the format in to the query field's format property instead of actually formatting it directly in the query like

SELECT Format([FieldNameHere], "mmm-yyyy") As NewFieldNameHere

FROM tableNameHere

When you use the SQL for the queries, you don't have the formatting in the property anymore.  So, the Union query would just use whatever it is currently formatted as unless you use what I wrote above.  The other option is to use saved queries and just create your Union Query like

Select * From Query1NameHere

UNION

Select * From Query2NameHere

The number of fields have to match in each query though.

Answer 2

Please don't piggyback your question on another thread. Please use the participate link to start your own thread. We will help there.

Answer 3

I used this, as shown above, in my union query. I modified it to include the day of the month.  MS Access 2013

SELECT Format([FieldNameHere], "mm-dd-yyyy") As NewFieldNameHere

FROM tableNameHere

Using my own field names.

When the above query is the source for a report the Expiration Date field was not a date format that I could work with in the report.  And I could not set it in the report.  I needed to do some Conditional Formatting on that date field and I used, IIF([Expiration Date]>Now(),1,0)  If 1 is returned the date field is set to Red Bold.  Not working at all.  I have used this in other places where a simple select query is the source to a Form or Report.

Any ideas, please?

Thanks,

John

Answer 4

Thank you for replying.  Unfortunately I have not managed to make either suggestion work.

1. I don't understand the first option.

a) Do I need to put the line of code you suggest in the SQL of the query as mentioned in my original point 3?

b) Do I need to create a new table (what you describe as tableNameHere)  What do I put in this table?

c) Do I need to create a new field (what you describe as NewFieldNameHere)?  If so where do I create this field?

2 Second option - I like the look of the simplicity of this option but I can't get it to work

a) I  got the error "The number of columns in the two selected tables or queries do not match".  I have counted the number of columns in the datasheet of the two selected queries (Query 1 and Query 2) and they are both 10

b) Out of interest I tried running just the first line of the code you suggested e.g.

Select * From Query1NameHere

This returned results but it ignored the selection criteria in Query1 and just returned everything unfiltered.  It also showed 12 columns.  One of the extra ones was a hidden column in the original linked spreadsheet (see my original point 1)) and another had a field name F12

I did the same for Query2 and although the original worksheet from which the data is drawn is identical in structure to the one from which the data from which Query1 is drawn(including having a hidden column) this returned 10 columns i.e. without the hidden column and without the F12)

Thanks again for taking the trouble and time to look at this.