Date format in Union Query 1

[FoxProProgrammer]

Hi,

I created two Queries that contain a date field, and then combined them into a Union. Each individual query displays the date field in Short Date format (MM/DD/YYYY). The Union query displays the date field in General Date format (MM/DD/YYYY HH:MM:SS). I want the date in the Union query to be in Short Date format. There must be an easy fix for this, but I can't seem to find it.

Thank you!

dz

 

[dhookom]

Why are you worried about how the date displays in your query? You can set the format in your form or report controls to whatever you want.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[genomon]

Format(YourDateField,"mmddyyyy")

It's always darkest before dawn. So if you're going to steal your
neighbor's newspaper, that's the time to do it.

 

[FoxProProgrammer]

Thanks, genomon...it worked! I used "mm/dd/yyyy" to separate the parts of the date.

Duane, the Query is the source for a list box. I couldn't find a way to change the format of the column in the list box. Is there a way to do this other than formating the data in the Query? Thank you!

Incidentally, I set the format of the field in the table to Short Date because all the Queries other than the Unions return the data in the same format as the table it pulled from. It seems kind of quirky that the Union queries are different.

dz

 

[dhookom]

I missed the "listbox source" in your original posting ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SkipVought]

FYI,

Format(YourDateField,"mmddyyyy")

returns a STRING. Dates are NUMBERS. What you see is a DISPLAY FORMAT (short/long/and a myriad of other Display Formats)

This STRING will not colate as you expect a date to sort.

As Duane stated, "Why are you worried about how the date displays in your query? You can set the format in your form or report controls to whatever you want."

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[genomon]

Good call on all fronts.
If you need a date, CDate(Format(YourDateField,"mm/dd/yyyy"))

It's always darkest before dawn. So if you're going to steal your
neighbor's newspaper, that's the time to do it.

 

[FoxProProgrammer]

Hey, Skip...thanks for your input. The Query is not sorted by date, nor is the date used as the criteria in any comparisons. From that perspective, it doesn't matter if the date is converted to a string because I am only interested in how the date looks to the user in the list box. The user doesn't collect the time, and it shouldn't be displayed because it is meaningless. The date is entered on a Form through a Date Picker control. I assume that the time stored in the record is whatever time the user happened to save the record. I went ahead and included the Cdate function to remove any ambiguity about the data type, and in case I later decide to filter the data by date.

You may not have read my clarification before you posted that the Query is the Rowsource for a listbox. The best that I can tell, and it looks like Duane agreed, the format has to be set in the Query because it can't be done in the listbox. I know how to set the format of a combo box and text box, but don't see any way to set the format of a specific column in a list box.

Thanks, and best regards to everyone...

dz