?
Solved

Sql date calculation

Posted on 2004-11-15
11
Medium Priority
?
169 Views
Last Modified: 2010-05-02
Hello,

When the qry below is fired i basically want it to display data 2 weeks before the current date. So I need some form of calculation similar to the following:



sSql1 = "select * from qryclosed where [date closed] = todays date - 14 days

so if i launched the qry today it should pull all data from the field 'closed date' starting from 2/11/04 through to 15/11/04.

I hope this makes sense.

Thanks in advance

0
Comment
Question by:jono1970
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 19

Expert Comment

by:Shauli
ID: 12588848
'For Access DB
sSql1 = "select * from qryclosed where [date closed] = #" & Date - 14 & "#;"

If you are using sql server then replace the # with a single quote (')

S
0
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 12589175
OR

sSql1 = "select * from qryclosed where [date closed] = #" & DateAdd("d", -14, Date()) & "#;"
0
 

Author Comment

by:jono1970
ID: 12592008
Hi,

Slight problem with code. it only displays data with a date of 2/11/2004.

in the example it shuld have displayed data from 2/11/2004 through to current date.

I have tried using the between command but to no avail.

thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 5

Expert Comment

by:ajgilbert
ID: 12592357
Jono,

Presuming you are querying an Access database, the following will work...

sSql1 = "select * from qryclosed where [closed date] between (date() - 14) and date()"


0
 
LVL 2

Expert Comment

by:kishore3576
ID: 12592435
Just change your query to

sSql1 = "select * from qryclosed where [date closed] between #" & DateAdd("d", -14, Date()) & "# and #" & date() & "#
0
 
LVL 2

Expert Comment

by:kishore3576
ID: 12592436
Add a double quote at the end also.
0
 

Author Comment

by:jono1970
ID: 12592446
Hi,

Still not working :O)

This is the error message:

No value given for one or more required parameters.

I am using VB6 and Access

Thanks
0
 
LVL 2

Expert Comment

by:kishore3576
ID: 12592601
Here is one of the query that I am using

"select * into tbldate from af where date between #" & Format(DateAdd("d", -14, Date), "mm/dd/yyyy") & "# and #" & Format(Date, "mm/dd/yyyy") & "#"

Hope this helps u
0
 
LVL 2

Expert Comment

by:kishore3576
ID: 12592626
using this your query will become

sql = "select * from qryclosed where [date closed] between #" & format(dateadd("d",-14,date), "mm/dd/yyyy") & "# and #" & format(date, "mm/dd/yyyy") & "#"
0
 
LVL 5

Accepted Solution

by:
ajgilbert earned 375 total points
ID: 12592636
Jono,

The example I put above works with VB6 and Access. Do you get that error message with my example ?



0
 

Author Comment

by:jono1970
ID: 12592671
Hi,

Your solution displayed the same error. I will close this ticket now as you have nearly solved my problem and I have learned something to say the least.

Thanks for your assistance.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question