Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Upate query syntax for MS ACCESS

Posted on 2011-05-06
4
Medium Priority
?
310 Views
Last Modified: 2012-05-11
The query below works, but date is hard coded to add 1 day, however, I would like to add days to current date based on strDayInterval...



dim strDayInterval
strDayInterval = 1

  'UPDATE MAIN TABLE
  mySQL1 = "UPDATE CPSIProcessData SET CPSIProcessData.DtsRunning='Y', " _
  & "CPSIProcessData.DtsLastDateTime='" & Now & "', " _
  & "CPSIProcessData.DtsNextDateTime='" & Date + 1 + #6:00:00 AM# & "' " _
  & "WHERE (((CPSIProcessData.DtsNameOfFrmQryRpt)= '" & strDTSJob & "'));"
'  & "CPSIProcessData.DtsNextDateTime='" & Date + 1 + #6:00:00 AM# & "' " _

  DoCmd.RunSQL mySQL1
0
Comment
Question by:epicazo
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 35709817
Try this:

dim strDayInterval As Long
strDayInterval = 1

  'UPDATE MAIN TABLE
  mySQL1 = "UPDATE CPSIProcessData SET CPSIProcessData.DtsRunning='Y', " _
  & "CPSIProcessData.DtsLastDateTime='" & Now & "', " _
  & "CPSIProcessData.DtsNextDateTime='" & Date + strDayInterval + #6:00:00 AM# & "' " _
  & "WHERE (((CPSIProcessData.DtsNameOfFrmQryRpt)= '" & strDTSJob & "'));"
'  & "CPSIProcessData.DtsNextDateTime='" & Date + strDayInterval + #6:00:00 AM# & "' " _

  DoCmd.RunSQL mySQL1
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35709883
And since it is a Long that you need, better rename the variable lngDayInterval so as not to be misleading.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35709910
You might also want to create an actual Date variable for use in the update code, perhaps with DateAdd:

dteNewDate = DateAdd("d", lngDayInterval, Date) & " " & #6:00:00 AM#

This lets you check the syntax in the Immediate Window.
0
 

Author Closing Comment

by:epicazo
ID: 35710114
thank you experts...   :)
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

580 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