By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,341 Members | 1,693 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,341 IT Pros & Developers. It's quick & easy.

Dcount with specific criteria based on time...

P: 78
I'm working in Access 2003 and most likely have another stupid question to ask! I have the following code:
Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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!
Mar 27 '10 #1

✓ answered by 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.

Share this Question
Share on Google+
10 Replies


Expert Mod 2.5K+
P: 2,545
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.

Expand|Select|Wrap|Line Numbers
  1. 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
Mar 27 '10 #2

P: 78
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!!!!
Mar 27 '10 #3

P: 78
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":

Expand|Select|Wrap|Line Numbers
  1. 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!
Mar 28 '10 #4

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

Thanks for the guidance!
Mar 28 '10 #5

NeoPa
Expert Mod 15k+
P: 31,768
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.
Mar 30 '10 #6

P: 78
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!
Mar 31 '10 #7

P: 78
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!
Apr 2 '10 #8

NeoPa
Expert Mod 15k+
P: 31,768
@WannabePrgmr
It seems you weren't that far off already. I just clarified an issue or two maybe. Very pleased to help anyway.
Apr 8 '10 #9

NeoPa
Expert Mod 15k+
P: 31,768
@WannabePrgmr
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT tP.Phone,
  2.        subArea.Area,
  3.        tTZ.TimeZone
  4.  
  5. FROM   ([tblPhone] AS tP INNER JOIN (
  6.        SELECT DISTINCT
  7.               [Phone]
  8.               Left([Phone],4) AS [Area]
  9.  
  10.        FROM   [tblPhone]) AS subArea
  11.   ON   tP.Phone=subArea.Phone) INNER JOIN
  12.        tblTimeZone AS tTZ
  13.   ON   subArea.Area=tTZ.Area
PS. Generally a new thread as the question is separate, but we can skip it this once ;)
Apr 8 '10 #10

P: 78
Thanks NeoPa! I set it up different, but I'll check out what you have as it looks much less complicated (as usual).
Apr 9 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.