Working with dates and days of the week in a query.

[Guest]

Hey there,

I'm trying to create two queries which will ultimately be used to create
reports based on them.

In both queries I want to work with date fields which I will call Date1 and
Date2. The dates that are contained in both these fields are all Thursdays.
Essentially, what I want is a query to select is all those records where
Date1 is last thursday (ie from the date the query is run) and another query
to select all those records where Date2 is next thursday (again from the date
of running the query.) If I run the queries on a thursday I want to select
that date's records, not last thursday's records.

I've been trying to do this for days with the opinion that it can't be that
tricky, but sadly my syntax has failed me.

Thanks for your help.
Joe

 

[Guest]

Surely this can't be that tricky?......can it?

 

[Andreas]

Hmmm.
Let's see.

Untested.
One of many possible solutions.

Criteria for Date1:
Choose(Weekday(Date1),Date()-3, Date()-4, .....)

Have a look at the Choose() function and the Weekday() function.
You can probably figure out the rest.

Regards,
Andreas

 

[Guest]

Andreas,

Thanks for replying. I've had another pop and come up with a solution,
albeit not your way. I tried the Choose/ Weekday combination you suggested
but I don't think its quite there yet. By setting Weekday(Date1) as the index
for the Choose function, you are asking it to match the integer 5 (for
thursday) with "Date()-1" which is a dd/mm/yyyy return - giving a data type
mismatch error.

I saw where you were going and it inspired me to come up with a simple
solution. Essentially all I require in the return is those records where
Date1 equals Date()-1,2,3,4,5,6 or 7. I'm lucky in the sense that all my
Date1 data is thursday data, so by using multiple OR criteria the return is
naturally going to be last thursday's records. I knew it was simple!

What would be interesting from a hypothetical point of view is how to do it
if the dataset did include records where Date1 was not a thursday! Consider
this if you wish, but do so in the knowledge that my problem is solved and I
offer you my thanks for your inspiration!

Joe

 

[John Spencer (MVP)]

Maybe even simpler than that

WHERE DATE1 Between Date()-6 and Date()

I modified it slightly since you said you wanted today's data if today is Thursday.

 

[Andreas]

If you had data with dates other than Thursday, you would want to return
records within a date range, rather than a single date.
It really would depend on the exact requirements.
In the meantime, everything is working, let's not break it

Regards,
Andreas