Query Help 1

[jalenben1]

I have a query in Access and I need help on a formula. I have a feild called DateCreated and I would like to create a formula in the query with a fieldname called Week Beginning in which the beginning of the week would start off as 1/1/2011 based off the DateCreated fieldname; next create a formula in the query with a fieldname called Week Ending in which the ending of the week would end on 1/7/2011 based off the DateCreated fieldname. So I would have a fieldname called Week Beginning and Week Ending. The last field name I would like to create is Month. This would be based off of the Restoral Date. Let's say a restoral date is 1/31/2011; I would like to have a formula that would take the "mm" to say January. In this case 1/31/2011 the "MM" is 1 which would be January. So that makes it 3 field names I would need help to create in my existing query.

 

[dhookom]

You don't mention anything about what day of the week is the start. Is it Saturday since 1/1/2011 is a Saturday?

Can we assume you don't plan on breaking normalization rules by storing these values?

Typically you can find the Sunday start date of a week by

 DateAdd("d",-Weekday([DateCreated])+1,[DateCreated])

For the end of the week, add 6 to the week start.
To get eh month, use Month() or Format() or DatePart().

Duane
Hook'D on Access
MS Access MVP

 

[jalenben1]

I am sorry. The week starts from Saturday to Friday. In this case 1/1/2011 to 1/7/2011

 

[jalenben1]

Maybe this will help: Here is my SQl statement below

SELECT qryHuaweiTicketsQuery.AssignedToGroup, qryHuaweiTicketsQuery.IncidentID, qryHuaweiTicketsQuery.Node, qryHuaweiTicketsQuery.AssetType, qryHuaweiTicketsQuery.Description, qryHuaweiTicketsQuery.CreatedBy, qryHuaweiTicketsQuery.Status, qryHuaweiTicketsQuery.Priority, qryHuaweiTicketsQuery.Category, qryHuaweiTicketsQuery.Type, qryHuaweiTicketsQuery.ResolutionCategory, qryHuaweiTicketsQuery.ResolutionSubCategory, qryHuaweiTicketsQuery.ResolutionSpecifics, qryHuaweiTicketsQuery.[Occurrence-Date], qryHuaweiTicketsQuery.[Restoral-Date/Time], qryHuaweiTicketsQuery.DateCreated, qryHuaweiTicketsQuery.OutageMinutes, qryHuaweiTicketsQuery.Impact, qryHuaweiTicketsQuery.Owner, qryHuaweiTicketsQuery.Severity, qryHuaweiTicketsQuery.Region, qryHuaweiTicketsQuery.AssignedToFullName, qryHuaweiTicketsQuery.DeviceFirstOccurrence, qryHuaweiTicketsQuery.Service, qryHuaweiTicketsQuery.NEType, qryHuaweiTicketsQuery.AlarmCondition, qryHuaweiTicketsQuery.AcknowledgedTime, qryAlertKeyQuery.AlertKey
FROM qryHuaweiTicketsQuery LEFT JOIN qryAlertKeyQuery ON qryHuaweiTicketsQuery.IncidentID = qryAlertKeyQuery.UNOIncidentID;


In this SQL statemement is where I would like to incorprate the formula for the week beginning fieldname and week ending feildname and Month field name. Our reporting week is from Saturday to Friday so in this case the occuring week will start from 1/1/2011 to 1/7/2011 so forth to the end of the year 2011.

 

[dhookom]

jalenben1,
Using my suggested expression for calculating the week beginning date, how far did you get in modifying it for your use? What were your results for various values of [DateCreated]?


Duane
Hook'D on Access
MS Access MVP

 

[jalenben1]

dhookum:

When I tired your suggestion, I got the following error message:

Extra ) in query expression 'qry.HuaweiTicketsQuery.[DateAdd("d",-Weekday([DateCreated])+1, [DateCreated])

 

[dhookom]

I'm not sure what your SQL statement looks like that generates that error since it wasn't in your SQL provided on Feb. 4.

Try:

SELECT qryHuaweiTicketsQuery.AssignedToGroup, qryHuaweiTicketsQuery.IncidentID, qryHuaweiTicketsQuery.Node, qryHuaweiTicketsQuery.AssetType, qryHuaweiTicketsQuery.Description, qryHuaweiTicketsQuery.CreatedBy, qryHuaweiTicketsQuery.Status, qryHuaweiTicketsQuery.Priority, qryHuaweiTicketsQuery.Category, qryHuaweiTicketsQuery.Type, qryHuaweiTicketsQuery.ResolutionCategory, qryHuaweiTicketsQuery.ResolutionSubCategory, qryHuaweiTicketsQuery.ResolutionSpecifics, qryHuaweiTicketsQuery.[Occurrence-Date], qryHuaweiTicketsQuery.[Restoral-Date/Time], qryHuaweiTicketsQuery.DateCreated, qryHuaweiTicketsQuery.OutageMinutes, qryHuaweiTicketsQuery.Impact, qryHuaweiTicketsQuery.Owner, qryHuaweiTicketsQuery.Severity, qryHuaweiTicketsQuery.Region, qryHuaweiTicketsQuery.AssignedToFullName, qryHuaweiTicketsQuery.DeviceFirstOccurrence, qryHuaweiTicketsQuery.Service, qryHuaweiTicketsQuery.NEType, qryHuaweiTicketsQuery.AlarmCondition, qryHuaweiTicketsQuery.AcknowledgedTime, qryAlertKeyQuery.AlertKey,
DateAdd("d",-Weekday([DateCreated])+1,[DateCreated]) As WeekStart,
DateAdd("d",-Weekday([DateCreated])+7,[DateCreated]) As WeekEnd
FROM qryHuaweiTicketsQuery LEFT JOIN qryAlertKeyQuery ON qryHuaweiTicketsQuery.IncidentID = qryAlertKeyQuery.UNOIncidentID;

Duane
Hook'D on Access
MS Access MVP

 

[jalenben1]

dhookum:

I modified your suggestion as follows:

SELECT qryHuaweiTicketsQuery.AssignedToGroup, qryHuaweiTicketsQuery.IncidentID, qryHuaweiTicketsQuery.Node, qryHuaweiTicketsQuery.AssetType, qryHuaweiTicketsQuery.Description, qryHuaweiTicketsQuery.CreatedBy, qryHuaweiTicketsQuery.Status, qryHuaweiTicketsQuery.Priority, qryHuaweiTicketsQuery.Category, qryHuaweiTicketsQuery.Type, qryHuaweiTicketsQuery.ResolutionCategory, qryHuaweiTicketsQuery.ResolutionSubCategory, qryHuaweiTicketsQuery.ResolutionSpecifics, qryHuaweiTicketsQuery.[Occurrence-Date], qryHuaweiTicketsQuery.[Restoral-Date/Time], qryHuaweiTicketsQuery.DateCreated, qryHuaweiTicketsQuery.OutageMinutes, qryHuaweiTicketsQuery.Impact, qryHuaweiTicketsQuery.Owner, qryHuaweiTicketsQuery.Severity, qryHuaweiTicketsQuery.Region, qryHuaweiTicketsQuery.AssignedToFullName, qryHuaweiTicketsQuery.DeviceFirstOccurrence, qryHuaweiTicketsQuery.Service, qryHuaweiTicketsQuery.NEType, qryHuaweiTicketsQuery.AlarmCondition, qryHuaweiTicketsQuery.AcknowledgedTime, qryAlertKeyQuery.AlertKey,DateAdd("d",-Weekday([qryHuaweiTicketsQuery.DateCreated])+1,[qryHuaweiTicketsQuery.DateCreated]) As WeekStart,DateAdd("d",-Weekday([qryHuaweiTicketsQuery.DateCreated])+7,[qryHuaweiTicketsQuery.DateCreated]) As WeekEnd FROM qryHuaweiTicketsQuery LEFT JOIN qryAlertKeyQuery ON qryHuaweiTicketsQuery.IncidentID = qryAlertKeyQuery.UNOIncidentID

where the DateCreated field name is I replaced it with qryHuaweiTicketsQuery.DateCreated and that seemed to work. However can I changed the formula to reflect the days from Saturday to Friday you have from Sunday to Sunday

 

[jalenben1]

dhookum:

I changed the 7 to a 6 and I have the correct week days beginning and ending.

 

[jalenben1]

Now that I have the week start and week ending dates how to format it to show only the dates itself and not the times with it?

 

[jalenben1]

Can you tell me if I am calculating this right. I am trying to gett the difference between two times in hours:Minutes:seconds.

I have the DateCreated fieldname and I have the Occurence-Date fieldname. In the same query as above I want to add that. So far I have DateDiff([qryHuaweiTicketsQuery.DateCreated])-[qryHuaweiTicketsQuery.Occurence-Date]). Not sure if this is right.

 

[dhookom]

Did you search from DateDiff() in Help? You can typically just subtract the earlier date/time from the later date/time. The units will be in full days. Doug Steele has A More Complete DateDiff Function

Your expression has unbalanced ()s. Also, a field name with a minus sign in it is bound to cause issues. You need to wrap the field name in []s.

DateDiff([qryHuaweiTicketsQuery.DateCreated])-[qryHuaweiTicketsQuery.Occurence-Date])

Duane
Hook'D on Access
MS Access MVP

 

[jalenben1]

Could I use the formula in a text box in a form

DateDiff([qryHuaweiTicatetsQuery.DateCreated])-[qryHuaweiTicketsQuery.Occurence-Date])?

 

[dhookom]

Could I use the formula in a text box in a form

DateDiff([qryHuaweiTicatetsQuery.DateCreated])-[qryHuaweiTicketsQuery.Occurence-Date])?

The quickest answer is to try it.

Any text box with an expression in the control source must begin with "=". You don't need the table/query qualifier if there are no duplicate field names in the form's record source.

You can get the difference using a control source of:

  =[DateCreated]-[Occurence-Date]

If [DateCreated] is earlier, you will get a negative number.

Duane
Hook'D on Access
MS Access MVP

 

[jalenben1]

dhookum:

The formula you gave me initially worked but I had to tweak it toget what I needed:

DateAdd("d",-Weekday([qryHuaweiTicketsQuery.DateCreated])+1,[qryHuaweiTicketsQuery.DateCreated]) As WeekStart,DateAdd("d",-Weekday([qryHuaweiTicketsQuery.DateCreated])+7,[qryHuaweiTicketsQuery.DateCreated]) As WeekEnd

What I need to have is a formula for the month. For instance from the ([qryHuaweiTicketsQuery.DateCreated])+7,[qryHuaweiTicketsQuery.DateCreated]) As WeekEnd

I need to show the month of the week end date. So far have
Month('m"([7,[qryHuaweiTicketsQuery.DateCreated]) As Month

 

[dhookom]

Try wrap the WeekEnd expression in Month(...)

[red][b]Month([/b][/red]DateAdd("d",-Weekday([qryHuaweiTicketsQuery].[DateCreated])+7,[qryHuaweiTicketsQuery].[DateCreated])[red][b])[/b][/red]

Duane
Hook'D on Access
MS Access MVP

 

[jalenben1]

That formula is not giving me the month

Month(DateAdd("d",-Weekday([qryHuaweiTicketsQuery].[DateCreated])+7,[qryHuaweiTicketsQuery].[DateCreated]))

Basically I need a formula that is based on the DateCreated fieldname and the Restoraldate. If there is a date in the DateCreated then I want to see the name of the month and If there is a date in the restoral date then I want to see the name of the month. For instance if there is a datecreated but not in the restoral date that says 1/31/2011 then I want to see the month name January. However if there is a restoral date that say, for example, 2/1/2011 then I want to see the month name February. When I created my spreadsheet I created a formula with this formula that represent this as: =IF(O2<>"",TEXT(O2,"mmmm"),TEXT(P2,"mmmm")). Is there a way to have a formula created in a query?

 

[PHV]

see the month name
MonthName(Month(...))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

To get the month name of a date, you can use

Format([YourDateField],"mmmm")

If you want to choose another field if one of them might be null, you can use Nz().
Nz([OneField], [AnotherField])

I expect the value you might want is:
Format(Nz([RestoralDate], [DateCreated]),"mmmm")


Duane
Hook'D on Access
MS Access MVP

 

[jalenben1]

Thanks dhookum and PHV

I believe this formula will work for me:


Month: Format([qryHuaweiTicketsQuery.DateCreated],"mmmm")

It is giving me the name of the month from the created date.

Earlier you gave me the formulas for week start:
WeekStart: DateAdd("d",-Weekday([qryHuaweiTicketsQuery.DateCreated]),[qryHuaweiTicketsQuery.DateCreated])

and week end: WeekEnd: DateAdd("d",-Weekday([qryHuaweiTicketsQuery.DateCreated])+6,[qryHuaweiTicketsQuery.DateCreated])

Is there any way to format thhose formulas to just give me the date without the time?