selecting specific day in criteria field

[Cosette]

Hi all,

I am stuck on something basic. I have a list of dates, and I want only to return Sundays. How do I select Sunday in the Criteria field? The date field is in short date format.

Thanks for your help.

Cosette

 

[lynchg]

Use the weekday function, Sunday = 1

Ex: Where weekday(yourDate) = 1

 

[lynchg]

Your entry in the criteria field would just be:

weekday(yourDate) = 1

or:

weekday(yourDate) = vbSunday

They both accomplish the same thing, but you'll remember what is happening better by using the second one. Access will create the proper SQL syntax for you.

 

[dhookom]

You can't use vbSunday in the criteria of a query. Constants such as vbSunday are available only in code.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Cosette]

I guess I didn't express myself correctly. I have a table with a column called InputDate that contains many, many records. Through the use of a query, I want to filter to only get those dates that are Sundays. I have aprox. 10,000 records with many different dates. Can I do that through the use of a criteria selection. Needless to say, IU am not going to test for every day to see if it was a Sunday or not.

I apologize if I wasn't clear before.

Cosette

 

[lespaul]

Can I do that through the use of a criteria selection. Needless to say, IU am not going to test for every day to see if it was a Sunday or not.

if you're not going to test to see if it was a sunday or not, how do you expect the query to return the records where the day is Sunday?

Here's the basic SQL you need:

SELECT * FROM TableName WHERE Weekday(InputDate) = 1

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Cosette]

Lespaul,

Thank you for your reply. I now understand what I need to do. I have to create a query beforehand selecting the Sunday. I was trying to select sunday in the criteria field and I now see why it couldn't work.

What I meant was that with so many different dates, I couldn't test for every specfic date. Thanks for the code, I now undesrtand how to go about it.

Cosette