473,322 Members | 1,734 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

query and dates...

I'm writing a query that will check for records in which a specific field
has a date that falls within this current year, i.e. any record in which the
date field contains a date that ends in 2006. Next year however, I want that
query to return records where that date ends in 2007. How do I right that
date? Right now, sql reads as follows...

WHERE (((tblClients.AccountType)="Residential") AND
((tblProjects.StartDate)>#1/1/2006#))
Oct 2 '06 #1
7 1257

Jimmy Stewart wrote:
I'm writing a query that will check for records in which a specific field
has a date that falls within this current year, i.e. any record in which the
date field contains a date that ends in 2006. Next year however, I want that
query to return records where that date ends in 2007. How do I right that
date? Right now, sql reads as follows...

WHERE (((tblClients.AccountType)="Residential") AND
((tblProjects.StartDate)>#1/1/2006#))
WHERE (((tblClients.AccountType)="Residential") AND
((Year(tblProjects.StartDate)=Year(Date)))

Oct 2 '06 #2
Not sure I understand the change.

If I enter exactly what you typed ...tblProjects.StartDate)=Year(Date)))...
access corrects the entry to ...tblProjects.StartDate)=Year("Date")))... and
I get a type mismatch. I suspect that you intended
....tblProjects.StartDate)=Year(Date()))... in which case, I get no results
at all. The field that I am referencing contains a complete date, i.e.
6/24/2006 or similar.

<pi********@hotmail.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
>
Jimmy Stewart wrote:
>I'm writing a query that will check for records in which a specific field
has a date that falls within this current year, i.e. any record in which
the
date field contains a date that ends in 2006. Next year however, I want
that
query to return records where that date ends in 2007. How do I right that
date? Right now, sql reads as follows...

WHERE (((tblClients.AccountType)="Residential") AND
((tblProjects.StartDate)>#1/1/2006#))

WHERE (((tblClients.AccountType)="Residential") AND
((Year(tblProjects.StartDate)=Year(Date)))

Oct 2 '06 #3

Jimmy Stewart wrote:
Not sure I understand the change.

If I enter exactly what you typed ...tblProjects.StartDate)=Year(Date)))...
access corrects the entry to ...tblProjects.StartDate)=Year("Date")))... and
I get a type mismatch. I suspect that you intended
...tblProjects.StartDate)=Year(Date()))... in which case, I get no results
at all. The field that I am referencing contains a complete date, i.e.
6/24/2006 or similar.
Did you include
Year(tblProjects.StartDate)=Year(Date())
?

Oct 3 '06 #4
Yes. My where statement is as follows...

WHERE (((tblClients.AccountType)="Residential") AND
Year(tblProjects.StartDate)=Year(Date()));

This returns all of the values where the year is 2006. So what exactly does
the Year(Date()) function do?

<pi********@hotmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
>
Jimmy Stewart wrote:
>Not sure I understand the change.

If I enter exactly what you typed
...tblProjects.StartDate)=Year(Date)))...
access corrects the entry to ...tblProjects.StartDate)=Year("Date")))...
and
I get a type mismatch. I suspect that you intended
...tblProjects.StartDate)=Year(Date()))... in which case, I get no
results
at all. The field that I am referencing contains a complete date, i.e.
6/24/2006 or similar.

Did you include
Year(tblProjects.StartDate)=Year(Date())
?

Oct 3 '06 #5
The Date function returns the current date; the Year function extracts the
year from a date. Thus the combination of Year(Date()) returns the current
year.

If you type any of Access builtin functions in any Module, place the cursor
on it, and press F1, you will see Help for that function which will tell
you, in detail, what I summarized above. (And, much quicker than asking and
getting an answer in a newsgroup.)

For good suggestions on effective use of newsgroups, see the FAQ at
http://www.mvps.org/access/netiquette.htm.

Larry Linson
Microsoft Access MVP

"Jimmy Stewart" <no**@none.comwrote in message
news:dz********************@fe02.news.easynews.com ...
Yes. My where statement is as follows...

WHERE (((tblClients.AccountType)="Residential") AND
Year(tblProjects.StartDate)=Year(Date()));

This returns all of the values where the year is 2006. So what exactly
does the Year(Date()) function do?

<pi********@hotmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
>>
Jimmy Stewart wrote:
>>Not sure I understand the change.

If I enter exactly what you typed
...tblProjects.StartDate)=Year(Date)))...
access corrects the entry to ...tblProjects.StartDate)=Year("Date")))...
and
I get a type mismatch. I suspect that you intended
...tblProjects.StartDate)=Year(Date()))... in which case, I get no
results
at all. The field that I am referencing contains a complete date, i.e.
6/24/2006 or similar.

Did you include
Year(tblProjects.StartDate)=Year(Date())
?


Oct 3 '06 #6
So in this code ...Year(tblProjects.StartDate)=Year(Date()));...
Year(tblProjects.StartDate) is extrapolating the year from my date field
(i.e. 1/1/2006 becomes 2006) and Year(Date()) takes todays date and
extrapolates the year (i.e. 10/3/2006 becomes 2006) and compares the
two...2006 = 2006 = True? Am I understanding correctly?

Access help tends not to explain why things work very well! So I was just
trying to get a handle on how things where working.
The Date function returns the current date; the Year function extracts the
year from a date. Thus the combination of Year(Date()) returns the current
year.
>WHERE (((tblClients.AccountType)="Residential") AND
Year(tblProjects.StartDate)=Year(Date()));

This returns all of the values where the year is 2006. So what exactly
does the Year(Date()) function do?

<pi********@hotmail.comwrote in message
news:11**********************@i42g2000cwa.googleg roups.com...
>>>
Jimmy Stewart wrote:
Not sure I understand the change.

If I enter exactly what you typed
...tblProjects.StartDate)=Year(Date)))...
access corrects the entry to
...tblProjects.StartDate)=Year("Date")))... and
I get a type mismatch. I suspect that you intended
...tblProjects.StartDate)=Year(Date()))... in which case, I get no
results
at all. The field that I am referencing contains a complete date, i.e.
6/24/2006 or similar.
Did you include
Year(tblProjects.StartDate)=Year(Date())
?



Oct 3 '06 #7
"Jimmy Stewart" wrote
So in this code ...Year(tblProjects.StartDate)=Year(Date()));...
Year(tblProjects.StartDate) is extrapolating the year from my date field
(i.e. 1/1/2006 becomes 2006) and Year(Date()) takes todays date and
extrapolates the year (i.e. 10/3/2006 becomes 2006) and compares the
two...2006 = 2006 = True? Am I understanding correctly?
Yes, your WHERE condition is testing for those StartDates which fall in the
current Year. It'll be testing for "2006" this year, but "2007" next year.

It sometimes is not easy for me to find Access' help on a particular topic,
but generally, if I do find it, it seems pretty clear to me. Help content
seemed to be neglected when they went to HTML Help in Access 2000, but it
has improved with every release since then.

Larry Linson
Microsoft Access MVP

Oct 3 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: paii | last post by:
I have a table that stores job milestone dates. The 2 milestones I am interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18. All jobs have TypeID 1 only some jobs have TypeID 18....
8
by: DH | last post by:
Say I have a table with two columns that matter to this example, and . And here is an example of what the rows currently look like: , 1,6/27/2001 1,6/27/2001 1,5/31/2001 2,6/27/2001...
8
by: Donna Sabol | last post by:
First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless in it's operation from their point of view. I...
1
by: Mike Cooper | last post by:
Hi everyone, This is a tough one. I have a database full of solicitations, identifying a customer and recording initial call, first followup, second followup, etc. My boss want to be able to...
6
by: Bill R via AccessMonster.com | last post by:
I have a query: SELECT tblCalendar.CalendarDay AS LastSunday FROM tblCalendar WHERE (((tblCalendar.CalendarDay)>=(Now()-7) And (tblCalendar.CalendarDay)...
2
by: Julie Wardlow | last post by:
Help! I am calculating a future date using the DateAdd function in a query (the calculation also involves an IIf statement), and have managed to get this formula to produce the required result....
2
by: Mark Roughton | last post by:
I have a form where the users need to view records for various criteria, one of which is a date field on which they may wish to view all related data for the selected date, for all dates upto and...
13
newnewbie
by: newnewbie | last post by:
I have an Access database that I upload a data extract intoto daily. I want to create a query that will give me a list of dates that no data can be found for. E.g. there are no records created on the...
5
by: veaux | last post by:
I'm thinking this is easy but can't get it. I have a table with following: Table1 Date 1/1/2007 Table2 Type 0107 (This is MMYY of above) So I'm having trouble using a query to turn the...
2
by: markcarroll | last post by:
I have a rather complicating query (the SQL is about a page long) so I hope I can solve this without needing to get into specifics. Basically, the database I am working on has information about...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.