Solved: Query for specific date, current month and year.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

bakatenshi

Thread Starter
Joined
Oct 19, 2012
Messages
20
I need to set up a query that pulls information matching a certain date of the current month and year. I'm not sure about the syntax. Someone help please?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
bakatenshi, welcome to the Forum.
Are you talking about Access?
 

bakatenshi

Thread Starter
Joined
Oct 19, 2012
Messages
20
If I need, say, the records concerning the 10th of the current month, would the following work?

#10/month(now())/year(now())#
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I have never seen that used.
Normally I would split the date in to 3 columns of day, month & year.
The criteria would be Year(Date()), Month(Date()) and the day that you want.
I use
Format([date field name], "yyyy")
Format([date field name], "mm")
Format([date field name], "dd")
for the 3 column headings.
 

bakatenshi

Thread Starter
Joined
Oct 19, 2012
Messages
20
I'm sorry. I'm not sure I really understand what you wrote.

When you say split to 3 columns, do you mean that I have to create 3 additional fields for the query for the date, month, and year? And where do I write the exact date that I want? I'm lost...
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Yes you create a column for each year and the date that you want is only the day of the month, so the criteria goes in the day column.

It can be done using just one column but you have to "create" both dates as a formatted date is not read the same by Access as a real date like Date().

I find the 3 column version more versatile. ie you can find all the records for a Month & Year etc
 

bakatenshi

Thread Starter
Joined
Oct 19, 2012
Messages
20
Okay. But I still don't quite get the Format([date field name], "dd") part. I do know what the Format function does, just that I've not used it for something like this before.

And could you clarify what you mean by "create" both dates?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
To "create" the dates you would use something like
Format([date field name], "dd") & "/" & Format([date field name], "mm") & "/" & Format([date field name], "yyyy")
and in the criteria something like
"10/" & Format(Date(), "mm") & "/" & Format(Date(), "yyyy")

Would you like an example of a query using the Column Headings?
 

bakatenshi

Thread Starter
Joined
Oct 19, 2012
Messages
20
Okay. I think I get the criteria half. Basically, what you've written allows me to set 10, in this case, for the date, whereas for month and year, I have to use the Date() function to obtain the current date, right?

Does the Format([date field name], "dd") pull the date values from [date field name] for me, and so on?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Yes, where date field name is the actual name of your field that contains the date.
 

bakatenshi

Thread Starter
Joined
Oct 19, 2012
Messages
20
Just got a chance to test it out. Works perfect. Thank you very much, especially for being patient with me.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top