Understanding DATE fomat

[djclntn]

This queestion can also apply to Queries & Forms.



I understand that the DATE Data Type in varies formats.

eg.
- "ddd" displays the day of the week
- "mmm/yy" displays the month/year
- "q" displays the quarterly

What I don't understand is what if I have a Form which I want t display both the day of the week -&- the Date. Will I need to enter the date twice or how (or can I) just enter in the date once in the Date field & automatically also be entered into my 3-other "Date" Fields?

Will I only need the DATE Field in my Table & create queries to display my other 3-fields: weekday, month/year, & quarterly?

If my DATE Field is named "TDate" what expressions would I need to generate the weekday say in a query?

Much thanks!

[orange]

Try

select weekdayName(Weekday (Tdate)) & " ," & Tdate from yourTable

[djclntn]

I would put th expression in a query?

It didn't work-DAY: Weekday (Tdate) & " ," & Tdate [Employee/Payroll])

[orange]

I would put th expression in a query?

It didn't work-DAY: Weekday (Tdate) & " ," & Tdate [Employee/Payroll])

select weekdayName(Weekday (Tdate)) & ", "  & Tdate from  [Employee/Payroll]

You should not use special characters in field and object names.

[djclntn]

Thank you for your assistance! However, I found a block/problem with converting my WKDY fied into DATE Data Type. On my Forms, for "Sat" & for "Sun" I used Conditional Formatting. "Sat" & "Sun" are shaded in 2-different colors to stand out. Although it would be nice not to need to input the WKDY & Date (TDATE) every time (record) except in changing the WKDY Field to a DATE Field I lose the ability to Conditional Format that field which I want to obtain.

In essence, I'm trying to learn Access on my own which involves a lot of trials & errors. I hope by quite often asking questions here doesn't make me a nuiscance.

Thnks again!

[orange]

You don't have to convert anything.
You store a field called TDate (datatype Date/Time)

The WeekDayName and WeekDay functions just manipulate the Date for presentation.

[djclntn]

I'm sorry, but I don't follow; what do you mean, "for presentation?"

As a test I created a Report & I'm still unable to alter how some of the data is displayed in the Report.

eg. Tried changing the font color just for Sat & Sun to red in Conditional Formatting & nothing.

I'm giving up & going back to manually typing in the weekday. It's too frustrating!

Thanks

[orange]

I'm sorry, but I don't follow; what do you mean, "for presentation?"

What I'm saying is that you define a field TDate datatype Date/Time in a Table.
Then in a query or Form or Report, you can use Format statements, or assign values to a control such that TDate is "presented" in the query, or form or report in a different format.

Select Tdate from YourTable results in a General Date format say, 11/15/2011

Whereas
select weekdayName(Weekday (Tdate)) & " ," & Tdate from yourTable results in

Tuesday, 11/15/2011
same field - different presentation.

see http://office.microsoft.com/en-us/ac...001099015.aspx for more info


I don't have Access 2007 or 2010 so can not read accdb, but if you want to post an mdb version with no confidential data, I'll look at your report.

[djclntn]

Ok, let me go back to square one.

I have (either in a Table, Query, or Form) a
- Day of the week field
- date field

Now, I can make both field Date/Time Data Types, but I'll need to put in the date into both field. How or can I enter the date once, but have it coorspond (I'm lost for the right words) in my other Date/Time fields within a tbl?

Please excuse me if I'm not making sense here. I've been going back & forth on another Access forum about another unrelated question & my brain is like almost fried!

Thanks for your time!

[RayMilhon]

Square One.
Table has 1 Date field
Query, Form or Report. Uses that 1 date field to represent the date in any number of formats you desire using Format statements

[orange]

I have (either in a Table, Query, or Form) a
- Day of the week field
- date field

Now, I can make both field Date/Time Data Types,

Why do you need a field for day of week?

Let's say you have a date field in your table called TDate.

When you display it on a form or report, you assign a value based on your table field value to a control on the form or report. The value you assign could be the default table field value, or it could be the result of some formatting function

eg using 11/15/2011 as in previous post
MyControl = TDate ............... 11/15/2011
MyControl = format(Tdate,"yyyy-MMM-dd") .......... 2011-NOV-15
MyControl = Month(Tdate) ........ 11
MyControl = WeekdayName(Weekday(TDate))......... Tuesday
MyControl = WeekdayName(Weekday(TDate)) & ", " & Tdate ....Tuesday, 11/15/2011


After posting : I see ray has responded while I was typing.

[djclntn]

Yes people it's Thanksgiving & I'm working.

Ok, Stonehead here finally understands to have only 1-date field in a table & with that 1-date field I can use it in a qeury several times manupliate it to varies formats- mmm, ddd, mm/yy, etc.

However, I have a big problem. When I run my "Monthly" queries I put in my query:
- MNTH: TADTE (mmm in the format/Property sheet)
- HRS: SumOfHRS (Sum)
- ERNGS: SumOfERNGS (Sum)

*It's not just giving me the monthly figures I think because the "MNTH" field seems to be recognizing it as dates vs. months even though I have "mmm" in the format.

I don't understand, if I can use the Date field from the Table & manipulate to eg. "mmm" in a query then why I'm I having this proplem?

I like using Access & to be honest here it's more like my hobbpy & joy. I want to learn so much with Access, but it's frustrating because I think that I'm taking a step forward only to realize that I've taken a step backwards. Ok, now I've learned how to only type in the date once & it'll correspond/reflect with my weekday, months, & quartly field in a query, but now I've LOST my monthly summary queries which I want.
Here's the SQL I tried to run wantiing to see the monthly Sums of my Employee\Payroll
SELECT [Employees/Payroll].TDATE AS MNTH, [Employees/Payroll].Employee_ID, Count([Employees/Payroll].TDATE) AS CountOfDaysWorked, Sum([Employees/Payroll].HRS) AS SumOfHRS, Sum([Employees Info].Salery) AS SumOfERNGS
FROM [Employees Info] INNER JOIN [Employees/Payroll] ON [Employees Info].Employee_ID = [Employees/Payroll].Employee_ID
GROUP BY [Employees/Payroll].TDATE, [Employees/Payroll].Employee_ID;

[djclntn]

*Correction
- MNTH: TDATE.....

[orange]

Try something like this: untested , to get the Monthly Figures

SELECT Month([Employees/Payroll].TDATE) AS MNTH
, [Employees/Payroll].Employee_ID
, Count([Employees/Payroll].TDATE) AS CountOfDaysWorked
, Sum([Employees/Payroll].HRS) AS SumOfHRS
, Sum([Employees Info].Salery) AS SumOfERNGS
FROM [Employees Info] INNER JOIN [Employees/Payroll] ON
[Employees Info].Employee_ID = [Employees/Payroll].Employee_ID
GROUP BY
Month([Employees/Payroll].TDATE)
, [Employees/Payroll].Employee_ID;

[djclntn]

Orange,
It Worked! Now I need to apply it to other Tables like my TasksProjects Table Can I somehow look you up if I need help?

Thanks