Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Syntax for putting a varible in a date in SQL

Posted on 2015-01-18
7
Medium Priority
?
202 Views
Last Modified: 2015-01-18
I need to know the syntax for putting a date in SQL in Access with a variable.

This is what i want:

dim vYear as Date
vYear = Year(Now)

WHERE FinalDate = 1/1/vYear

vYear is the of course the variable.

Thanks,
Scott
0
Comment
Question by:Scott Palmer
  • 3
  • 2
  • 2
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40556655
try this

WHERE FinalDate = dateserial(year(now()),1,1)
0
 

Author Comment

by:Scott Palmer
ID: 40556663
I am trying to find the data parameters for each year for year to date reporting so the whole WHERE column is this dummy code.

WHERE FinalDate Between 1/1/vYear and 12/31/vYear

What i currently have is this:

WHERE FinalDate Between 1/1/2014 and 12/31/2014

And i was updatting so I would not have to change my code every January with the new year

Also the variable vYear could be the current year or it could be the previous month's year when it is january.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 40556673
then you need to use this


WHERE FinalDate = Between dateserial(year(now()),1,1)  and  dateserial(year(now()),12,31)
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 52

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 40556717
>  it could be the previous month's year when it is january.

Then it would be:

WHERE FinalDate Between
    DateSerial(Year(DateAdd("m", -1, Date())),1,1)
    And
    DateSerial(Year(DateAdd("m", -1, Date())), 12, 31)

/gustav
0
 

Author Comment

by:Scott Palmer
ID: 40556741
Gustav is correct.  That is why I was hoping to just add variable vYear after 1/110 or 12/31/ that way I would not have to add the longer SQL statement all throughout out my, I could do it once when the form is opened.  So there is no way to do this?

Scott
0
 

Author Closing Comment

by:Scott Palmer
ID: 40556751
Thanks, I can use the code once when the form opens and store that in a variable for the from and to date.

Scott
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40557129
You are welcome!

/gustav
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

782 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