Selecting by Day from Date

[dickohead]

Good Afternoon,

I am currently trying to create a query that will allow me to do the following:

Prompt the user with a pop up box, asking:

Please enter start date
Please enter end date

It will then select everything from my table between those two dates (this works), and then it will ignore all those items that have a time after 7am or before 7pm (this works), but I need it to also select all entries that fall on a saturday or sunday. Now because my raw data comes in the form of (australian dates): 7/09/2005 9:39:10 AM, I am using an update query to split the field into two fields: DateFrom and TimeFrom (both are date/time fields). I then need to create another field called Day, which takes the data in DateFrom and converts the date to dddd (using Format(Date()) i assume), giving me a field with the day name in it, ie: Sunday.

I can then use that to differentiate between weekdays and weekends.... as I use MySQL and PHP more often than I do access, I have no idea how to do this.

So in short:

How do I (this is what i think i need):

Select * from tblData WHERE Day!=Sunday OR Saturday AND TimeFrom > 7:00pm OR TimeFrom < 7:00am

Select * from tblData WHERE Day=Sunday OR Saturday

Both of those selections, will of course need to be performed within my date constraits specified by the user.

Am I making sense?

 

[gromit]

Hi -

Check out the DATEPART function

E.g. DatePart ("d", <some date variable>) returns the day of the week.

DatePart can also break out years, months, hours, etc.

There is also a WeekDay function that returns the day of the week.

hth,

-g

 

[dickohead]

Thanks for the reply!

But how do I allow for two different selection criteria in one Query?

ie: Select everything that is between my dates, within my hours on weekdays and anything on weekends?

 

[gromit]

You can nest multiple conditions in one WHERE clause, e.g.

WHERE ( ( (Condition1) AND (Condition2)) OR (Condition 3))

(Just keep track of those parentheses!).

- g

 

[dickohead]

I didn't even know I could use the WHERE function with Access? I am doing it with the Query Design interface (completely useless!).

Right now I am trying to do this:

Left(WeekdayName([DateFrom]),2)

But I have a problem where my dates are represented without 0's at the beginning. ie: 1/11/05 and 21/11/05 - so selecting 2 characters from the left will sometimes also return me with a / character.... I tried telling it to select 8 characters from the right negatively (i knew it wouldn't work):

Right(WeekdayName([DateFrom]),-8)

To try and give me the one or two characters for the day part of the date, after it had been converted by WeekdayName to my 1-7 value for the day, which would be fantastic if I could get that to work, but because the Day field, the one I am trying to output the data to is set to text - WeekdayName won't work, but when i set it to Date/Time - it doesn't like having only two characters in it....

This is so frickin confusing.

All I want is to turn this:

19/09/2005

into the numerical representation of the day, how do I do that without it adding /09/1900 to the end?

Another problem I am still presented with is I also don't know how to use a WHERE clause with my selection criteria in the Query window....

So very lost.

Here's a snippet of my SQL syntax (did you know that you can change views in access.... idiot!).

This is for the updating of some fields due to the raw data format being un-useable.

UPDATE tblData SET tblData.DateFrom = Left([Travel Date/Time From],InStr([Travel Date/Time From]," ")-1), tblData.TimeFrom = Mid([Travel Date/Time From],InStr([Travel Date/Time From]," ")+1), tblData.DateTo = Left([Travel Date/Time To],InStr([Travel Date/Time To]," ")-1), tblData.TimeTo = Mid([Travel Date/Time To],InStr([Travel Date/Time To]," ")+1);

Which works great at turning two fields into four, but I need to then turn one of those fields into the appropriate numerical representation of the day... which is what i'm stuffed on.

Here's the code for my selection query:

SELECT tblData.LPN, tblData.[Toll Zone First], tblData.[Toll Zone Last], tblData.DateFrom, tblData.DateTo, tblData.TimeFrom, tblData.TimeTo, tblData.Value
FROM tblData
GROUP BY tblData.LPN, tblData.[Toll Zone First], tblData.[Toll Zone Last], tblData.DateFrom, tblData.DateTo, tblData.TimeFrom, tblData.TimeTo, tblData.Value
HAVING (((tblData.LPN)=["Which Car are you after?"]) AND ((tblData.DateFrom) Between [Please Enter Starting Date] And [Please Enter Ending Date]) AND ((tblData.TimeFrom)>#12/30/1899 19:0:0# Or (tblData.TimeFrom)<#12/30/1899 7:0:0#));

So that so far lets me select everything that is between my dates and times. But it also selects days that are weekends, where I need ALL the data from weekends, and anything outside of 7 and 7 on weekdays.....

 

[gromit]

Yeah, I think that people who don't work with Access even understand what *real* frustration can be....

1. Converting Dates to Days
If your date fields are actual dates (and not just text), then I still think that DatePart could be of help. E.g.

WHERE (( DatePart("d", tblData.DateFrom) > 1) AND (DatePart("d", tblData.DateFrom) < 15))

this should give you any dates between 2 and 14 inclusive.

Note: I'm having problem getting the exact same query to work with parameters instead of fixed values, which I don't quite understand...

2. Using WHERE Clauses
You can toggle between the design view and SQL view of the query. There is the little icon at the upper left that lets you do this...

If you are in design view (grid), then the WHERE information gets put in the Criteria line. The syntax however is a little abbreviated. E.g. the

WHERE (( DatePart("d", tblData.DateFrom) > 1) AND (DatePart("d", tblData.DateFrom) < 15))

turns into
> 1 AND < 15
under the tblData.DateFrom column.

Sometimes I find it is easier to just work with the SQL statement, especially for complex queries.

3. Another Thought
You might find it convenient to turn the raw dates into the appropriate day and time columns in a separate query and then use that one as the basis for your final query. See attached for an example. qryDateBreakdown breaks down the days and times, and qryFilteredRecords shows only the selected records (which might not be exactly your criteria).

Hope that this is making sense...

- g

 

[raskew]

Hi -

If you'll copy/paste the following query-SQL into a new query, then modify the table & field names in accordance with your application, it should produce a query that will:
- prompt for a start and end date (this originates in US short date format), I'm not sure how it'll sort out when your regional format is something other.
- return records between 7:00 and 19:00 (7:00 PM) for Monday thru Friday (weekdays 2 - 6).
- return all records for Saturday and Sunday (weekdays 7 and 1)

SELECT
    Step
  , Item
  , startTime
  , Weekday([startTime]) AS mywd
  , TimeValue([startTime]) AS mytime
FROM
   tblDevProcess
WHERE
   (((startTime) Between [enter start date] 
AND
   [enter end date]) 
AND
   ((Weekday([startTime])) In ("1","7"))) 
OR
   (((startTime) Between [enter start date] 
AND
   [enter end date]) 
AND
   ((Weekday([startTime])) Not In ("1","7")) 
AND
   ((TimeValue([startTime])) Between #12/30/1899 7:0:0# 
AND
   #12/30/1899 19:0:0#))
ORDER BY
   tblDevProcess.Step;

Provided it survives the regional gap, you should see--in design view--a logical manner for specifying the weekday/time criteria.

HTH - Please post back with any problems.

Bob

Tested in A97

 

[dickohead]

Alright, starting to get somewhere here now! I have converted the numerical day to the text day: 1 = Monday etc.

And at one stage i'm entirely confident that it was selecting data between my dates.... at least i'm sure it was! I am in australia, and as such my dates are in Australian format, so too is the input when it searches for dates... do i need to convert either or both of them?

IE: In Aus the 1/11/2005 - 1st November 2005
I'm guessing it's the same in the UK too.

Here is my SQL syntax as it stands right now... it doesn't select the correct dates though....

Your help with this so far has been fantastic guys!

SELECT 
   tblData.DateTo
 , tblData.TimeTo
 , tblData.[Toll Zone First]
 , tblData.[Toll Zone Last]
 , tblData.Value
FROM 
   tblData

#Section for the Weekdays
WHERE 
   (((tblData.LPN)=["Which Car are you after?"]) 
AND 
   ((tblData.DateFrom) Between [enter start date] And [enter end date]) 
AND
   ((tblData.TimeFrom)>#12/30/1899 19:0:0# Or (tblData.TimeFrom)<#12/30/1899 7:0:0#) 
AND
   ((tblData.Day)<>"Sunday" Or (tblData.Day)="Saturday")) 

#Section for the weekends
OR 
   (((tblData.LPN)=["Which Car are you after?"]) 
AND 
   ((tblData.DateFrom) Between [enter start date] And [enter end date]) 
AND
   ((tblData.Day)="Sunday" Or (tblData.Day)="Saturday"));

 

[raskew]

What results do you get if you use US short-date (mm/dd/yyyy) format to respond to [enter start date] and [enter end date]?

 

[gromit]

I'll defer to raskew on most of this, but I'll throw out a question.

Have you checked your use of the TimeFrom criteria?

raskew's example uses the TimeValue function to convert this. I'm not sure what is in your table.

 

[dickohead]

My TimeFrom field is selecting the correct criteria.
It's just my DateFrom field that is not working correctly.
When i search by date like this:
1/11/05 - 31/11/05
I get data, just the wrong data.

When i search like this:
01/11/2005 - 31/11/2005
or
11/1/05 - 11/31/05 (american dates)
or
11/01/2005 - 11/31/2005
I get an error: "The expression is typed incorrectly, or is too complex to be evaluated."

 

[dickohead]

I just discovered another issue...

I have been trying to search between the 1/11/05 (1st Nov 2005) and 31/11/05 (31st Nov 2005), and getting incorrect data.

But i just changed my range to 1/10/05(1st Oct 2005) to 31/10/05 (31st Oct 2005)

And now I only get dates where the Month is the 10th, and times are correct.... But then took it further and tested September and December:
September returns one date from August, and December returns me with correct data......

So that leads me to conclude that perhaps the problem is with Access?

 

[raskew]

Try entering your dates in medium date format (e.g. 10-Dec-05).

When specifying end dates, ensure you use the correct date for the specified month (Sep and Nov have only 30 days).

Bob

 

[dickohead]

The information comes directly from CityLink (people living in melbourne will know them), so there is nothing wrong with the dates. But what i have done nopw is this:
Month field
Year field

What Year are you after?
What Month are you after?

And now it works.

So i guess everything that my original post was asking has been solved.

Many, many thanks to you all for your help with this, going to save a lot of time!

I do have one other question, but i'll post that elsewhere and link off this.

Thank you.

Link to other question: http://www.access-programmers.co.uk/forums/showthread.php?p=445543#post445543