VB.NET MS Access DATEADD.. Help!!!

Question

0.00/5 (No votes)

See more:

Hi,

When I go into the Query Builder of my Table Adapter(VB.NET), and add the following code, I keep getting errors.:

Code:

SQL

SELECT        CPR, FName, LName, CPRIdt, CPREdt, nation, Gender
FROM            Emp_master
where CPREdt>= date() and CPREdt<= dateadd(day , 15 ,date())

I want to find the records only between the dates: today and 15 days ahead.

For example: say I have 2 records where the CPREdt is 12/15/2013 and 12/18/2013.

Assuming today as 12/1/2013, the query must return all the values within the 15 days period. Which means the record with the CPREdt 15/15/2013 must be returned.

I tried with the above code (replacing date() with getdate()) and it worked with MS SQL Express 2008 successfully. I am currently using MS Access as the database for this project.

I would really appreciate if anybody can help me. Thanks!

Posted 25-Nov-13 1:38am

shyam2020

Updated 25-Nov-13 2:35am

v2

Add a Solution

Comments

ZurdoDev 25-Nov-13 7:47am

What's the error? As I recall in Access you need # around dates.

shyam2020 25-Nov-13 8:17am

I get the error in the query builder.

"SQL Syntax Errors Encountered"

The following error were encountered while parsing the contents of the SQL pane:

Error in WHERE clause near ')'.
Error in WHERE clause near ','.
Unable to parse query text.

What can this mean? I couldn't find any syntactical errors in the query though..

Dave Kreskowiak 25-Nov-13 8:28am

Well, the first thing I noticed is that you cannot have a space between DateAdd and the opening parenthesis.

shyam2020 25-Nov-13 8:35am

Sorry for the typo, actually there is no space between the DATEADD and the opening parentheses. I just copied the query from the Query Builder and the space just appeared in this page.

2 solutions

Solution 1

This will work i think

SQL

SELECT CPR, FName, LName, CPRIdt, CPREdt, nation, Gender
FROM Emp_master 
where CPREdt>=Date() and CPREdt <= Date() + 15

Posted 25-Nov-13 2:37am

karthikeyan_kk

Comments

shyam2020 25-Nov-13 8:50am

Thanks for the reply. Although it still doesn't work, there is a change in the error message now. It now only shows the below:

Error in WHERE clause near ')'.
Unable to parse query text.

I'm still wondering where is this mysterious ')' ??

Mike Meinz 25-Nov-13 9:00am

What if you used NOW() instead of DATE()? Does that work?

SELECT        CPR, FName, LName, CPRIdt, CPREdt, nation, Gender
FROM            Emp_master
where CPREdt>= NOW() and CPREdt<= dateadd(day , 15 ,NOW())

shyam2020 25-Nov-13 9:10am

Hi!

Thanks for the code, I tried with the same but it still doesn't work..
Another error message is being displayed instead.:

SQL Execution Error.

Executed SQL statement: SELECT CPR, FName, LName, CPRIdt, CPREdt, nation, Gender FROM Emo_master WHERE (CPREdt >= NOW()) AND (CPREdt <= dateadd([day], 15, NOW()))

Error Source: Microsoft Access Database Engine

Error Message: No value given for one or more required parameters.

Mike Meinz 25-Nov-13 9:20am

Does TODAY() work?

SELECT        CPR, FName, LName, CPRIdt, CPREdt, nation, Gender
FROM            Emp_master
where CPREdt>= TODAY() and CPREdt<= dateadd(day , 15 ,TODAY())

shyam2020 25-Nov-13 9:25am

Hi..Nope, it say: Same error except for the message which now says:

Undefined function 'TODAY' in expression.

Mike Meinz 25-Nov-13 9:30am

I found CURDATE() on this web page Access 2010 Scalar Functions. Please try this:

SELECT        CPR, FName, LName, CPRIdt, CPREdt, nation, Gender
FROM            Emp_master
where CPREdt>= CURDATE() and CPREdt<= dateadd(day , 15 ,CURDATE())

shyam2020 25-Nov-13 9:40am

Uhm, no, it shows the same error: Undefined function 'CURDATE' in expression.
I checked the link you provided and do see the Date Time functions for available for Access, but why is it not being accepted in the Query builder?

Mike Meinz 25-Nov-13 9:55am

I am starting to wonder if DATEADD() is not allowed in ACCESS SQL. I haven't had any luck finding examples.

How about this?

SELECT        CPR, FName, LName, CPRIdt, CPREdt, nation, Gender
FROM            Emp_master
where CPREdt>=NOW and CPREdt<= (15 + NOW))

shyam2020 25-Nov-13 10:06am

Hi Mike, Thank you so much for the code! I got the query working below:
WHERE (CPREdt >= now) AND (CPREdt <= now + 15)

For some reason Access is not willing to recognize CURDATE and yes, DATEADD is allowed in Access, but it kept giving me errors. The above method with 'Now' is viable... :)

Mike Meinz 25-Nov-13 10:29am

I agree that DATEADD is allowed in ACCESS. I just don't think it is allowed in ACCESS SQL statements.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

shyam2020 · Accepted Answer · 2013-11-25T03:56:00

Solution 2

Wow! Finally I found the solution to this error. As it turns out it was a combination of the query statements from 'Mike Meinz' and 'karthikeyan_kk 730'.

Code:

SQL

SELECT        CPR, FName, LName, CPRIdt, CPREdt, nation, Gender
FROM            Emp_master
WHERE        (CPREdt >= now) AND (CPREdt <= now + 15)

As it turns out, the 'now' function is what Access takes as the current DateTime. Adding additional days would be simply like 'now+15'. I implemented Mike's idea of the now function into karthikeyan's code and voila, it works like a charm.

Thank you very much to 'Mike Meinz' and 'karthikeyan_kk 730' for your guidance.

Regards.

Posted 25-Nov-13 3:56am

shyam2020

Comments

Mike Meinz 25-Nov-13 9:59am

Does CPREdt contain any time component? It would be safer to use the following Where clause:
(CPREdt >= now) AND (CPREdt < now + 16)

shyam2020 25-Nov-13 10:09am

Actually, when opened via MS Access, I don't see any time component, although, while pulling the data in Query builder, I saw the date was along with the time component..

Will that be a problem, also by removing the '=' symbol and increasing the numeric, what actually happens? Thanks.

Mike Meinz 25-Nov-13 10:28am

When you say CPREdt <= (now + 15), it will return CPREdt where the time component on (Now + 15) is 12:00:00AM but it will not return CPREdt on (NOW + 15) with any time greater than 12:00:00AM. By saying CPREdt < (now + 16), you get all CPREdt times on (NOW + 15).

We have a similar problem with CPREdt >= now. NOW will contain a time component. Any CPREdt < the current date/time will not be selected.
Try the following Where clause:
CPREdt > (now - 1) AND (CPREdt < now + 16)

This will ensure all times are included on the current date and the current date + 15.

shyam2020 25-Nov-13 10:54am

Thanks a ton Mike! I will set the query with (now - 1) and (now + 16). :)

Mike Meinz 25-Nov-13 12:26pm

And (now - 1)
CPREdt > (now - 1) AND (CPREdt < now + 16)