Dcount with specific criteria based on time...

[WannabePrgmr]

I'm working in Access 2003 and most likely have another stupid question to ask! I have the following code:

Me.Text8 = DCount("[Call Log]", "[CALLTOTALQuery]", "[ChangedDate] BETWEEN #2/1/2010 8:00:00 AM# AND #2/1/2010 9:00:00 AM#")

It works perfect and returns the number of calls that were logged on Feb 1 from 8-9.

How can I change the code so that I am able to change just the month by entering it into a textbox on the same form called txtMonth?

For example, when I enter 5 into txtMonth, the above code would now have 5 in place of the 2 for the month. I can't seem to get it right no matter how I try it!
Here was one of my unskilled attempts:

Me.Text8 = DCount("[CallLog]", "[CALLTOTALQuery]", "[ChangedDate] BETWEEN #(Me.[txtMonth])/1/2010 8:00:00 AM# AND #(Me.[txtMonth])/1/2010  9:00:00 AM#")

I know that attempt is hard to look at! That's why my user name is "WannabePrgmr"! !!

Any help is greatly appreciated!

[Stewart Ross]

Hi. Answering what you have asked, you need to include the value of your textbox, and to do this you break up the DCount WHERE-clause text string into smaller sections of text into which you place the value you need.

Me.Text8 = DCount("[CallLog]", "[CALLTOTALQuery]", "[ChangedDate] BETWEEN #" & Me.txtMonth & "/1/2010 8:00:00 AM# AND #" & Me.txtMonth & "/1/2010  9:00:00 AM#")

As you are finding, though, working with date literal values in expressions limits the flexibility of what you can do. I would not suggest changing your approach just yet if it works for you, but in most cases it would provide more flexibility to have a calendar control available to the user to allow selection of start and end dates.

We have a large thread on just this topic (MS Access Calendar) which our very expert Access programmer ADezii has answered and guided over many months. I suspect you may find it too advanced to consider as yet, so try the modification suggested above before undoing all the work you've already done!

-Stewart

[WannabePrgmr]

Thanks Stewert! I originally programmed it where the user would enter a start date and end date for the data they wanted to see, but what I need to do is look at all calls in hour blocks throughout every day from 7AM to 6PM. So if they entered a start of "3-1-2010" and end of "3-5-2010", I had no idea how to gather all 5 dyas of just the 7 to 8, 8 to 9...........and display the counts for each.

I completely understand how that would work if I restrcted the user to a choice of only one day, but multiple days confused me. So I decided to show the data by a given month.

Thank you for the code example! That's exactly what I was looking for. I'll check out your link! I'm sure I'll learn a lot!!!!

[WannabePrgmr]

Stewert,

I went about it a different way. I now have the user enter a start and end date. I then have Dcounts for every block of hour. The problem is the following:
If the user enters 3-1-2010 through 3-5-2010, my code that counts each hour will count the total calls from 3-1-2010 from 7:00 AM to 3-5-2010 at 8:00 AM. I need it to count each days 7:00 to 8:00 AM slot.

So I figured that I need a way to sort the field "ChangeDate " in the query by the hour. That way, all the data returned will be in order by hour of the day.

Any suggestions on how to accomplish this? Currently I have the following criteria for "ChangeDate ":

Between [Forms]![frmCounts]![txtStartDate] And [Forms]![frmCounts]![txtEndDate]

I'm only showing this because I have a feeling this is where the sorting will need to take place.
Thanks again!

[WannabePrgmr]

I figured out a way. I formatted the ChangedDate in the same query for hour, then sorted it. Works perfect!

Thanks for the guidance!

[NeoPa]

Nice work :)

Simply format it in the order of the items you need in descending order from left to right, then order by that result as text. I would suggest :
HHyyyymmdd
HH = 24 hour format.

[WannabePrgmr]

NeoPa,

That is great! I didn't know you could do it that way! That's almost too simple - no wonder I missed it!!!
Thanks again! I'll definitely put that to use in many ways!

[WannabePrgmr]

This may be something you recommend I post under a new thread but it's similar so I thought I'd ask here. I am trying to identify what time zone a given phone number is in. The only way I can think of is to somehow sort/copy the 1st three numbers of each phone number and compare it to all the area codes I would have listed in a table with the time zone listed with each.

What I am visioning is that when a user runs a query, the query will have a list of phone numbers in one column and the time zone (Eastern, Pacific etc) next to each.

Any ideas?

Thanks!

[NeoPa]

That is great! I didn't know you could do it that way! That's almost too simple - no wonder I missed it!!!

It seems you weren't that far off already. I just clarified an issue or two maybe. Very pleased to help anyway.

[NeoPa]

Any ideas?

I would say you pretty well have it to be fair.

The one issue you may come across is the difficulty of linking a derived field (EG 1st X-digits) into another table in a query. For this, you can return the derived field in a sub-query and link the table into the results of the subquery.

SELECT tP.Phone,
       subArea.Area,
       tTZ.TimeZone

FROM   ([tblPhone] AS tP INNER JOIN (
       SELECT DISTINCT
              [Phone]
              Left([Phone],4) AS [Area]

       FROM   [tblPhone]) AS subArea
  ON   tP.Phone=subArea.Phone) INNER JOIN
       tblTimeZone AS tTZ
  ON   subArea.Area=tTZ.Area

PS. Generally a new thread as the question is separate, but we can skip it this once ;)

[WannabePrgmr]

Thanks NeoPa! I set it up different, but I'll check out what you have as it looks much less complicated (as usual).