Change format of date in query?


F

Flopbot

Can this be done? I hope I’m missing something small. A query named “Qry
Monthly Volunteer Email†has a field named [Date]. Right now it returns
4/24/2010 but I would like for it to return “Sat, 4/24/2010†to solve any
confusion as to day of the week. I modified the following from other
discussions on this subject, but it continues to return 4/24/2010.

SELECT [Tbl Vol Opportunities].Date, Format([Date],"ddd, mm/dd/yy")

Thank you for your help!
 
Ad

Advertisements

J

John W. Vinson

Can this be done? I hope I’m missing something small. A query named “Qry
Monthly Volunteer Email” has a field named [Date]. Right now it returns
4/24/2010 but I would like for it to return “Sat, 4/24/2010” to solve any
confusion as to day of the week. I modified the following from other
discussions on this subject, but it continues to return 4/24/2010.

SELECT [Tbl Vol Opportunities].Date, Format([Date],"ddd, mm/dd/yy")

Thank you for your help!

Are you viewing the query datasheet itself? If so, don't; table and query
datasheets are very limited and not really appropriate for viewing or editing
data. Instead use a Form (for onscreen) or Report (for printing). Bind the
datefield to a textbox on the form or report, and set that textbox's Format
property to

"ddd, mm/dd/yy"

If you're using the query as the rowsource for a combo box, set its
ColumnCount property to 2 (to include both the actual date/time and the
formatted string) and ColumnWidths to

0";1"

to display only the string and conceal the unformatted date.
 
F

Flopbot

Actually, I answered my own question. It worked great! I didn't realize
that Access added a "column" at the end of the query which I couldn't see
without scrolling over. The new "Field" is labeled:

Expr1: Format([Date],"ddd, mm/dd/yy")

If anyone else needs to know, this works great!
 
Ad

Advertisements

F

Flopbot

John,

Thank you for responding. You always have great ideas and tips. Yes, I was
looking at the datasheet but it’s good to know that forms and reports are a
more appropriate venue. I was planning on cutting & pasting the results from
this query into a mail merged email (for some reason Word’s import database
wizard won’t recognize half my queries). Therefore, I wanted the day to be
listed right in the query. However, would there be a better method?

Chris


John W. Vinson said:
Can this be done? I hope I’m missing something small. A query named “Qry
Monthly Volunteer Email†has a field named [Date]. Right now it returns
4/24/2010 but I would like for it to return “Sat, 4/24/2010†to solve any
confusion as to day of the week. I modified the following from other
discussions on this subject, but it continues to return 4/24/2010.

SELECT [Tbl Vol Opportunities].Date, Format([Date],"ddd, mm/dd/yy")

Thank you for your help!

Are you viewing the query datasheet itself? If so, don't; table and query
datasheets are very limited and not really appropriate for viewing or editing
data. Instead use a Form (for onscreen) or Report (for printing). Bind the
datefield to a textbox on the form or report, and set that textbox's Format
property to

"ddd, mm/dd/yy"

If you're using the query as the rowsource for a combo box, set its
ColumnCount property to 2 (to include both the actual date/time and the
formatted string) and ColumnWidths to

0";1"

to display only the string and conceal the unformatted date.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top