Updating Datees In an Access Query

[ChiTownDiva]

I have to run a daily report in Access 2000 based on orders placed for the day before.

How can I:

1. Automatically update the date criteria in the query to "yesterday's" date? That date is actually a date/time field.

2. Work around the "Saturday and Sunday" issue? On Monday morning I want to pull Friday's numbers, not Sunday's.

3. Update the text field or label field on the report generated from the query to reflect the date criteria of the query.

I don't have any experience in creating modules, but I'm eager to learn.

I tried:

Public Function YesterDate() as Date ()

YesterDate = CDate(DateAdd("d",-1, Date()))

End Function

I have no idea how to apply this module, and I'm not sure if if solves the "weekend" problem.

Thanks for your time.

 

[MichaelRed]

O.K. a little bit at a time.

This returns the previous Weekday for a date. The optional parameter can - generally be ignored, so just setting some (date Type) variable = to the function will place the previous WEEKDAY's date in that variable. If (for instance) you need to do Friday's stuff on Tuesday (to tired from dancing all night Sunday to get into the office?), then you can put Mondays date into the optional parameter and still retrieve Friday's date.

Public Function basLastWeekDay(Optional DateIn As Variant) As Date

    'Usage:
    '? basLastWeekDay()                 Returns 2/26/01
    '? basLastWeekDay(#2/25/001#)       Returns 2/23/01

    If (IsMissing(DateIn)) Then
        DateIn = Format(Now, "Short Date")
    End If

    Select Case WeekDay(DateIn)

        Case Is = vbSunday
            basLastWeekDay = DateAdd("d", -2, Format(DateIn, "Short Date"))

        Case Else
            basLastWeekDay = DateAdd("d", -1, Format(DateIn, "Short Date"))

    End Select

End Function

I e-maailed the thread to myself, and will do some more on it later.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ChiTownDiva]

MichaelRed:

Hopefully, I figured this out.

I created a second field in my query and called it:

Date2: DateValue([Order_Date].[dbo_ebus_receipt] and used IIf(Weekday(Date())=2,Date()-3,Date()-1) in the criteria and it worked.

We'll see what happens on Monday!

Thanks!

 

[MichaelRed]

Your function will appear to work. There are some issues. When you subtract the "whole number" of days from a date type variable, the "DATE" is changed, but not the time (Fractional part of the day). So when you run the process at 10:00 AM, [Date2] will not reflect any orders earlier than 10:00 AM. Like wise, when you re-run it at 11:00 AM, you wont see the items from before that time.

Even worse, it looks like - in your post - that you are setting the criteria to an EXACT value, where it should be >= (Greater than Or Equal). I think you should use the function and set the criteria as:

>= basLastWeekDay


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ChiTownDiva]

Hi Michael:

Are you saying that it should look like this:

IIf(basLastWeekday(Date())>=2,Date()-3,Date()-1) or

IIf(Weekday(basLastWeekday())>=2,Date()-3,Date()-1) ???

You have to excuse my ignorance...I'm not a programmer so I'm kinda struggling with this.

Thanks

 

[MichaelRed]

I think it should look like:

>= basLastWeekDay

look at the function. It returns a date (formatted as a short date, so the time part is 0 - implies 12:00 AM) which is the previous weekday. So, the 'result' (e.g. Return Value) is the date you want. It does not need to be further adjusted by the subtraction. The Greaterthan Or Equal (">=&quot) operator simply says any value (Date/Time) after the return value is what you want.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Here is a sample, rather hurridly concoted, so pplease bear w/ the explain.

the table:

EmailAddr	JobId	Date
progjobs_usenet@jfpresources.com		2/21/01
kc@mindfinf.com		2/21/01
progjobs_usenet@jfpresources.com		2/21/01
datefix2k@yahoo.com		2/21/01
progjobs_usenet@jfpresources.com		2/21/01
progjobs_usenet@jfpresources.com		2/21/01
mark@phoenixhealthcorp.com		2/26/01
autocon@net-temps.com	134400	2/21/01
gkline@lifebridgehealth.org	2/26/01	2/26/01
resumes@oleen.com	Access Programming {7}	2/26/01
ajburt0@net-temps.com	bp5227bal	2/22/01
jluhrman@mattech.com	Job 014 on DICE	2/23/01
recruiter@radcor.com	Job 566 on DICE	2/23/01
kkane@hilljobs.com	Job 604 on DICE	2/23/01
recuriter@aci.com	Job 73440 on DICE	2/23/01
BaltimoreJobs@MetrolS.com	Job de400-3086 On DICE	2/23/01
BaltimoreJobs@MetrolS.com	Job de400-326 On DICE	2/23/01
pwilkins@maximgroup.com	Job maxm018-58674 on DICE	2/23/01
nhornber@maximgroup.com	Job maxm030-62484 on DICE	2/23/01
ajburt0@net-temps.com	MP081	2/21/01
ajburt0@net-temps.com	MP5398	2/21/01
webmaster@leakdetect.com	MS Access / VB	2/26/01
recruiter@pointetech.com	Ms Access / Visual Basic	2/26/01
jayne@hts.co.uk	NG_amsjh7477	2/21/01
jayne@hts.co.uk	Opportunities	2/21/01
alan@paracruit@net-temps.com	Opportunities	2/21/01
Tracey Willis	Programmer Position	2/26/01
recuriter@pointetech.com	Programming Jod (MS Access /V)	2/26/01
mark@phoenixhealth.com	resume	2/26/01
HR@paragon.com	Visual Basic / MS Access	2/26/01
resumes@oleen.com	WEB Site Job Opportunity {7}	2/26/01

the SQL Statement
SELECT tblJobsApplied.Date, tblJobsApplied.JobId, tblJobsApplied.EmailAddr
FROM tblJobsApplied
WHERE (((tblJobsApplied.Date)>=basLastWeekDay(#2/23/2001#)));


The Where (Criteria) Clause. I needed to put some date in because the data (table) doesn't have any entries from Yesterday. This (the DATE #2/23/20001#) doesn't need to be in yours.

the REsults:

Date	JobId	EmailAddr
2/22/01	bp5227bal	ajburt0@net-temps.com
2/23/01	Job 014 on DICE	jluhrman@mattech.com
2/23/01	Job de400-326 On DICE	BaltimoreJobs@MetrolS.com
2/23/01	Job de400-3086 On DICE	BaltimoreJobs@MetrolS.com
2/23/01	Job maxm030-62484 on DICE	nhornber@maximgroup.com
2/23/01	Job 73440 on DICE	recuriter@aci.com
2/23/01	Job 604 on DICE	kkane@hilljobs.com
2/23/01	Job maxm018-58674 on DICE	pwilkins@maximgroup.com
2/23/01	Job 566 on DICE	recruiter@radcor.com
2/26/01		mark@phoenixhealthcorp.com
2/26/01	Access Programming {7}	resumes@oleen.com
2/26/01	Ms Access / Visual Basic	recruiter@pointetech.com
2/26/01	MS Access / VB	webmaster@leakdetect.com
2/26/01	Visual Basic / MS Access	HR@paragon.com
2/26/01	2/26/01	gkline@lifebridgehealth.org
2/26/01	resume	mark@phoenixhealth.com
2/26/01	WEB Site Job Opportunity {7}	resumes@oleen.com
2/26/01	Programming Jod (MS Access /V)	recuriter@pointetech.com
2/26/01	Programmer Position	Tracey Willis

You do need to include the elipiss "()" in the criteria arg, so the previous post shoulh have been:

>= basLastWeekDay()

Look at the table & results. See how the "function" is invlked in the query.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over