Query to get previous Day and week based on today's date

sushmitha

Registered User.
Local time
Today, 14:53
Joined
Jul 30, 2008
Messages
55
Hello everybody..

I am using Access database to pull data from Excel sheet and produce out put to another Excel sheet using VBA modules

The database table has data from May 01 2008 till Jul 15 2008. Output table has five columns for each day MON, TUE, WED, THU, FRI. If I run the code on Monday it should populate values for the last one week under all days columns. If I run on Tue it should produce values of only yesterday Mon.
If run on Wed - Mon, Tue
Thur - Mon, Tue, Wed
Fri - Mon, Tue, Wed, Thur

How to write a query to get this. I have date in my table. I need to capture the query results
 
use this as a critiria:
Date - weekday(date(),2)+1 - iif(weekday(date(),2) = 1, 7-0)
 
Suspect that:
iif(weekday(date(),2) = 1, 7-0)

should be:
iif(weekday(date(),2) = 1, 7,0)

Bob
 
this may be hard to follow, but see what you might be able to get from it.

Select week-to-date under the Queries section and select the first day of the week you want to use...
 
Last edited:
Hi -

Namliam appears to be temporarily out of the net so allow me to respond.

Your problem is that you've referring to a field named [Date]. Date is a reserved word (name of a function, i.e. Date()) and should never be used as a field name.

The formula works fine. Example from the immediate (debug) window:

x = #7/28/08#
? x - weekday(x,2)+1 - iif(weekday(x,2) = 1, 7,0)
7/21/2008

It's the field name that's messing things up. Try renaming the field and it should work fine.

In order to meet your stated needs, I'd expand a bit on the criteria. Assuming that your date field contains only dates between Monday and Friday, try this:

Code:
>=Date - weekday(date(),2)+1 - iif(weekday(date(),2) = 1, 7,0) AND < Date()

This will ensure that you won't return records with the current date.

HTH - Bob
 
Oh no you didnt???

You didnt make a column named date?? Yes you did!!! Bad boy, bad bad boy!!!
Dont name columns as reserved words!!!

The formula I gave was to put in your criteria, like so
SELECT Table.Date, Table.TotalInvoice
FROM Table
WHERE Table.Date=Date()-Weekday(Date(),2)+1-IIf(Weekday(Date(),2)=1,7,0)

Edit:
Ah, thanks Bob and Adam, you covered it for me :)
 
Hi -

Namliam appears to be temporarily out of the net so allow me to respond.

Your problem is that you've referring to a field named [Date]. Date is a reserved word (name of a function, i.e. Date()) and should never be used as a field name.

The formula works fine. Example from the immediate (debug) window:

x = #7/28/08#
? x - weekday(x,2)+1 - iif(weekday(x,2) = 1, 7,0)
7/21/2008

It's the field name that's messing things up. Try renaming the field and it should work fine.

In order to meet your stated needs, I'd expand a bit on the criteria. Assuming that your date field contains only dates between Monday and Friday, try this:

Code:
>=Date - weekday(date(),2)+1 - iif(weekday(date(),2) = 1, 7,0) AND < Date()

This will ensure that you won't return records with the current date.

HTH - Bob

I am getting the following error when writing formula using
Connect Date - weekday(Connect Date(),2)+1 - iif(weekday(Connect Date(),2) = 1, 7,0)

Getting Syntax Error
 
Dont use spaces in your column names !!! Dont use any special characters either! Like *&^#$!@&*)_ etc

The formula I gave was to put in your criteria, like so
SELECT Table.Date, Table.TotalInvoice
FROM Table
WHERE Table.ConnectDate >= Date()-Weekday(Date(),2)+1-IIf(Weekday(Date(),2)=1,7,0)
 
Dont use spaces in your column names !!! Dont use any special characters either! Like *&^#$!@&*)_ etc

The formula I gave was to put in your criteria, like so
SELECT Table.Date, Table.TotalInvoice
FROM Table
WHERE Table.ConnectDate >= Date()-Weekday(Date(),2)+1-IIf(Weekday(Date(),2)=1,7,0)


It gives me only one date which is 3 days prior to the current system date. How to find out, if I run on every monday it should give me last whole weeks count in different columns ??

Also If I run on Tue it should give me Monday count and other columns should be zero and Wed should give me Mon, Tue and other remaining are zeros ..I need until friday

Please let meknow. I am new to access and db logic
 
The criteria is doing what it was intended to do, i.e. providing a start date.

If you run it on a Monday, it returns the preceding Monday. Run it on any other weekday and it returns the Monday of the current week.

In order to return records from either the preceding week (first example) or the current week, minus the current day (second example), it'll take some modification.

Change the WHERE statement to:

WHERE (((WHERE Table.ConnectDate >= Date()-Weekday(Date(),2)+1-IIf(Weekday(Date(),2)=1,7,0) AND Table.ConnectDate <= date() - iif(weekday(date(),2) = 1, 3, 1)

This query, however, is not going to populate five columns. For that, it'll take a cross-tab query. You'll need three fields -- you haven't furnished enough info about your table so I'll provide an example from tblExp (my table).
Fields:
1) HowPaidID - This will be the row heading. In your case it might be something like CustomerID.

2) ExpDte - The column heading, e.g. ConnectDate

3) PayAmt - The value shown under the column heading(s), e.g. TotalInvoice

Here's the sample query:

Code:
TRANSFORM Sum([COLOR="red"]tblExp[/COLOR].[COLOR="red"]PayAmt[/COLOR]) AS SumOfPayAmt
SELECT [COLOR="red"]tblExp[/COLOR].[COLOR="red"]HowPaidID[/COLOR]
FROM [COLOR="red"]tblExp[/COLOR]
WHERE ((([COLOR="red"]tblExp[/COLOR].[COLOR="red"]ExpDte[/COLOR])>=Date()-Weekday(Date(),2)+1-IIf(Weekday(Date(),2)=1,7,0) 
AND ([COLOR="red"]tblExp[/COLOR].[COLOR="red"]ExpDte[/COLOR])<=Date()-IIf(Weekday(Date(),2)=1,3,1)))
GROUP BY [COLOR="red"]tblExp[/COLOR].[COLOR="red"]HowPaidID[/COLOR]
PIVOT Format([[COLOR="red"]ExpDte[/COLOR]],"ddd mm/dd");

Suggest you copy/paste this to a new query, then replace the highlighted portions with your table/field names.

If you have a problem, please post back -- displaying a copy of your modified query SQL.

Bob
 
Last edited:
Also If I run on Tue it should give me Monday count and other columns should be zero and Wed should give me Mon, Tue and other remaining are zeros ..I need until friday
This is what it does...

To test my formula copy paste this into a module and run it.
Code:
Sub Testnamliam()
    Dim x As Integer
    For x = 1 To 7
        Debug.Print "Run on " & Format(Date + x, "DDD YYYY-MM-DD") & " will give " & Format(Date + x - Weekday(Date + x, 2) + 1 - IIf(Weekday(Date + x, 2) = 1, 7, 0), "DDD YYYY-MM-DD")
    Next x
End Sub
It will fake run for the next 7 days giving you the output to double check if this is what you want.
offcourse this formula now in this Sub will not work in a query... for that you need the one given earlier.
 
Hi -

The one problem with the cross-tab provided earlier is that if there's no record for a particular date/day, that date/day won't show up as a column.

In other words, run the query on Friday, but have only had input for the preceding Monday & Tuesday, and it'll return columns only for Monday & Tuesday, but not for Wednesday & Thursday.

Can't recall ever seeing a solution, using a query, that would rectify this situation.

Bob
 
Giving me error

Hi -

The one problem with the cross-tab provided earlier is that if there's no record for a particular date/day, that date/day won't show up as a column.

In other words, run the query on Friday, but have only had input for the preceding Monday & Tuesday, and it'll return columns only for Monday & Tuesday, but not for Wednesday & Thursday.

Can't recall ever seeing a solution, using a query, that would rectify this situation.

Bob

Bob,
Thank you very much for the query. But I am getting error when doing this. So here I am attaching both ExcelInput (Access table has data from this file), and required Output file. The output file is created using VBA Modules written in Access. Now using queries, I need to develop the same out put file. Please advise

Also I need to get all days eventhough there are no records on a particular days. Inthat case it should show '0'
 

Attachments

OK -

To start, I freely admit to not being an Excel whiz. When confronted with an Excel problem, my immediate reaction is to get it into an Access format where I can work with it. (Macros = #1, Excel = #2, both straight from the Devil's workshop and to be avoided at all costs.)

Uploading your Excel Input file to an Access table (tblCDC), first thing noted was that all fields imported as Text (which may be the norm) and that Date and Time were in separate fields.

Dates & Times:
As a general rule, in Access Date and Time are combined in a date/time data type field (see this MSKB article that shows how Access stores dates/times: http://support.microsoft.com/kb/q130514/). Although not particularly relevant to this exercise, would still prefer to work with a standardized data structure. So:

1) Added date/time field ConnectDteTime.

2) Used this Update query to populate ConnectDteTime:
Code:
UPDATE tblCDC SET tblCDC.ConnectDateTime = DateValue([ConnectDate])+TimeValue([ConnectTime]);
3) After verifying that the query had provided the desired result, deleted fields [ConnectDate] and [ConnectTime]

***********************
CallDispositionCode(s):
Text field CallDepositionCode contained both a 'numerical' code and an explanation of the code, e.g. "31-Busy/RNA (0 to 5 secs)". This violates normalization rules and is an absolute disaster if the operator must be expected to input all of this (without ever a typo) every time a new record is added. To create a more normalized approach:

1) Used this Make-Table query to create and populate tblCDCodes with the unique codes/ explanations as two fields (CDC Number, Remarks Text)
Code:
SELECT DISTINCT Left([CallDispositionCode],InStr([CallDispositionCode],"-")-1) AS CDC, 
Mid([CallDispositionCode],InStr([CallDispositionCode],"-")+1) AS Remarks INTO tblCDCodes

2) It took a little manual intervention since, in at least one case, different remarks had been attached to the same code number (think it was 32 or 33)

3) Added CDC 99, with Remarks: "No entries". This will come into play further on. Once satisfied that tblCDCodes was properly populated:

a. Used this query SQL to remove the remarks portion of CallDispositonCode:
Code:
UPDATE tblCDC SET tblCDC.CDC = Left([CallDispositionCode],InStr([CallDispositionCode],"-")-1);
FROM tblCDC;
b. Manually changed the data type of Call DispositionCode from Text to Number. This processed without problem (I was a little dubious and half-way expected a disaster).

c. Created a relationship between tblCDC and tblCDCodes so that if there was later a need to display Remarks, it could be easily done.

***********************
Days with no entries.
You've now got the problem that you need to show an entry for each 'eligible' date, but in many cases there are no entries. Thus, in a crosstab query, there will be no column entry. My solution was to create a 'dummy'
record for those eligible dates (Mon - Fri) that otherwise didn't have any entries. Used 99 (see 3), above) to represent an 'eligible' date without a corresponding entry.

1) Created and ran this sub which added a 'dummy' record for each eligible date that otherwise had no other entries.

Code:
Public Sub AddDates()
Dim db      As Database
Dim rs      As Recordset
Dim strSQL  As String
Dim strDte  As String
Dim dteHold As Date

Set db = CurrentDb
strSQL = "SELECT tblCDC.* " _
& "FROM tblCDC " _
& "WHERE (((datevalue(tblCDC.ConnectDteTime)) >= #7/1/2008#)) " _
& "ORDER BY datevalue(tblCDC.ConnectDteTime);"
Set rs = db.OpenRecordset(strSQL)

dteHold = #7/1/2008#
Do While WeekDay(dteHold) = 1 Or WeekDay(dteHold) = 7
   dteHold = dteHold + 1
Loop

Do While dteHold <= Date
   strDte = "datevalue(ConnectDteTime)= #" & dteHold & "#"
   With rs
      .FindFirst strDte
      If .NoMatch Then
         .AddNew
         !ConnectDteTime = dteHold
         !CDC = 99
         .Update
         .Requery
      End If
   End With
   dteHold = dteHold + 1

' this little routine clunks and I know it, but wasn't able to
' quickly refine it.
   Do While WeekDay(dteHold) = 1 Or WeekDay(dteHold) = 7
      dteHold = dteHold + 1
   Loop
Loop

rs.Close
db.Close
Set db = Nothing

End Sub

2) You aren't going to like it, because you wanted to display a zero (0) for a day with no entries, and this solution is going to display CDC 99 with a count of 1 (not 0). Maybe someone can provide a more elegant solution, otherwise you'll need to 'Suck it up', i.e. change your way of thinking.

***********************
The final product:

We're down to producing the results. Post #12 uses the current date, as you'd indicated was required. However, after seeing the range of dates, it seems that you may want to to produce output based on a date other than today's date.

In response, I've introduced a parameter asking that you input a date. In response, the query returns results based on that date. It incorporates all of the criteria discussed in previous posts:
Code:
PARAMETERS [Enter date] DateTime;
TRANSFORM Count(tblCDC.CDC) AS CountOfCDC
SELECT tblCDC.CDC
FROM tblCDC
WHERE (((DateValue([tblCDC].[ConnectDteTime]))>=[Enter date]-Weekday([Enter date],2)+1-IIf(Weekday([Enter date],2)=1,7,0) And (DateValue([tblCDC].[ConnectDteTime]))<=[Enter date]-IIf(Weekday([Enter date],2)=1,3,1)))
GROUP BY tblCDC.CDC
PIVOT Format([ConnectDteTime],"mm/dd ddd");

***********************
Finally:

Althought I've got all of this up and working, I've intentionally not included a zip file. Think it's important that you walk thru the process yourself and not just rely on a 'canned' solution.

If you find something isn't working for you, please post back.

Best Wishes - Bob
 
Last edited:
Days with no entries

I didnt read your complete post but cought this...
It is real easy to add fake values to your crosstab.

Just go to design view and add an IN clause, like so:
TRANSFORM Count( ) AS CountOf
In (Value, Value, Value)
SELECT
FROM
GROUP BY
PIVOT ;

Down side of this is that you need to list all values (IIRC), so any new values will fail to display unless added. But be sure to double check that cause I may be mistaken.
 
Hi -

I like it. Problem being, as you stated, is that every value must listed. So, this is going to require additional code in order to capture those values.

Bob
 
Code:
it is asking to enter parameter values for CalldispostionCode

Misspelling: Should be CalldispositionCode.

Also do we need to select from table tblCDC ??

My fault. The end of the SQL got clipped. Should be:

Code:
SELECT DISTINCT Left([CallDispositionCode],InStr([CallDispositionCode],"-")-1) AS CDC, Mid([CallDispositionCode],InStr([CallDispositionCode],"-")+1) AS Remarks INTO tblCDCodes
[COLOR="red"]FROM tblCDC[/COLOR];

If I run today on Aug 03 20008 (Monday), ...

Today is Monday, August 04 2008. Code, as written, is intended to run on Monday thru Friday (not Sunday).

HTH - Bob
 
Last edited:
I cannot change anything in the table format as we are uploading data everyday from Excel sheet to table.
This is a relatively simply query not any change to any tables...??
 
Just open tblCDCCodes.

Add a new record: CDC 99; Remarks: "No entries" (minus the quotes).

Then run AddDate(). Anytime you add new records to tblCDC, you'll need to call this sub to ensure that you've accounted for dates without entries. Don't worry, it'll only add one dummy record for any given date.


Bob
 

Users who are viewing this thread

Back
Top Bottom