Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a vb.net windows form project that uses an Access database backend. I am trying to write a query to show only one month of data with the month specified. The query I use in VB has the WHERE clause thus; WHERE Month([Date]) = 9.
This works in the Access query, but not in the VB form. I get an error that Month is not a recognized function. It works with access, and when I use it elsewhere on the form. The query filters with the form when I use a different criteria. I have been unable to find a solution.

What I have tried:

I have no idea how to fix this. My search has shown nothing.
Posted
Updated 18-Dec-22 7:45am
Comments
Graeme_Grant 14-Dec-22 20:03pm    
We can not see your screen on this page. Please update your question with the code and the complete error message.

You cannot use 'Month'. It is a function built into Access. It's not even part of the SQL specification.

I assume you're using the Select method on a Datatable object to filter records. The supported query language is described here[^].

You would have to create a range of dates, not just the month number, like:
C#
DateRow[] filtered = table.Select("DateCol >= #9/1/2022# AND DateCol <= #9/30/2022#");
 
Share this answer
 
Comments
BobbyStrain 14-Dec-22 22:31pm    
Dave, Thank you for your response. I looked again after I posted this question and found your answer here in 2011. It's not easy to find. I don't use SQL often, so I sometimes get stuck with simple things. Now I'll move to the next challenge. I am building a front-end to an Access database for a garden club to track their budget. They have been using Word to post to members. So tracking and auditing can take days. Add to this they are mostly Seniors, so I have to make it simple. I couldn't find anything free for the task.
CHill60 15-Dec-22 4:31am    
"I couldn't find anything free for the task." - have a look at Featured Access templates - Microsoft Support[^] and see if you can adapt one of those - in particular one of Budgets - Office.com[^]
BobbyStrain 15-Dec-22 23:34pm    
I did find one but it seemed too complex for my simple needs. I prefer a single table if it fits. My databases are never very large, and only used by a single user.
Use sql datepart to separate month from date.
like

Select * from Table where Datepart(Month,Field_name)=9
 
Share this answer
 
Comments
BobbyStrain 18-Dec-22 13:54pm    
Thank you.
Amitkumaryadava 18-Dec-22 17:40pm    
mark my solution as answers
Richard Deeming 19-Dec-22 5:17am    
Begging people to "accept" your solution is seen as rep-point hunting, and is not tolerated here.
BobbyStrain 19-Dec-22 10:28am    
Richard, I'll take responsibility for this. I inadvertently rejected the solution. It was correct, however. So he was just asking for a correction. I wasn't paying enough attention to what I was doing. So, please accept my apology.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900