SQL Update Query and variables

[Guest]

Good afternoon,
I'm having trouble getting my SQL Update Query to read a variable value into
the field specified (or any field for that matter). This is my first Access
application in over a year (still working on the Vacation Planner, but have
backtracked to the setup phase... where HR runs a form and fills in all the
Mondays of the bid year), so I'm horribly rusty. I need to populate the
"WeekStart" field in the table. Can someone please help?

The SQL statement was created by creating a query in the query builder and
converting it to SQL, then changing as needed. Code is:

Private Sub cmdSubmit_Click()
Dim dsetdate As Date
dsetdate = DateValue(Forms!frmCalSetup.txtdate)
Dim intcurweek As Integer
For intcurweek = 1 To 51
DoCmd.RunSQL "UPDATE [tblVacRestrict-Updated] SET
[tblVacRestrict-Updated].WeekStart = dsetdate WHERE
((([tblVacRestrict-Updated].WeekNo) = intcurweek))"
dsetdate = DateAdd("d", 7, dsetdate)
Next intcurweek
End Sub

Thank you for helping! I appreciate it!
Derek

 

[Guest]

Nothing like thinking it through with some help from the AccessWeb (MVP)
pages! The revised code is:

Private Sub cmdSubmit_Click()
Dim dsetdate As Date
dsetdate = DateValue(Forms!frmCalSetup.txtdate)
Dim intcurweek As Integer
For intcurweek = 1 To 51
DoCmd.RunSQL "UPDATE [tblVacRestrict-Updated]
SET[tblVacRestrict-Updated].WeekStart = #" & Format$(dsetdate, "mm/dd/yyyy")
& "# WHERE ((([tblVacRestrict-Updated].WeekNo) = " & intcurweek & "))"
dsetdate = DateAdd("d", 7, dsetdate)
Next intcurweek
End Sub

It was ALL in the SQL statement with the formatting of the date and the way
I was handling the quotations... those little things will be my demise!

Derek

 

[Guest]

Try this

DoCmd.RunSQL "UPDATE [tblVacRestrict-Updated] SET
[tblVacRestrict-Updated].WeekStart = #" & dsetdate & "# WHERE
((([tblVacRestrict-Updated].WeekNo) = " & intcurweek & "))"
dsetdate = DateAdd("d", 7, dsetdate)
===================================
For future reference
If you have a string variable you add single quote before and after

" Where FieldName = '" & Var & "'"

For date, you add #
" Where FieldName = #" & Var & "#"

For number, nothing
" Where FieldName = " & Var

 

[Tim Ferguson]

For date, you add #
" Where FieldName = #" & Var & "#"

<ahem> not for the other 94% of the world that don't use USA dates...


Tim F

 

[Guest]

I don't follow, what do you mean?
What is the connection to USA and #, I don't use USA date and still use #
before and after the date.
Unless you use it with SQL

 

[Tim Ferguson]

I don't follow, what do you mean?
What is the connection to USA and #, I don't use USA date and still
use # before and after the date.

If you really mean that the way you have typed it, then you are almost
certainly in trouble. Using a phrase like

WHERE SomeDate = #01/03/2006#

will return dates in January -- wherever you are in the world. And the
danger is that a VBA expression like

"WHERE SomeDate = #" & MyDate & "#"

will use whatever format is typed into the control panel Regional
Settings. If you are lucky, that is a USA or ISO format (the latter
extremely unlikely, I should guess). If you are a bit lucky, then it
might be one with english fullnames (like "dd mmmm yyyy") which will also
be parsed correctly. Otherwise, you'll put a dmy date into the string
which may or may not be parsed correctly. In any case, it's out of your
control as the developer and that is a Bad Thing. The only safe way to do
things is to manage the formatting yourself:

"WHERE SomeDate = " & Format(SomeDate, "\#yyyy\-mm\-dd\#")

Unless you use it with SQL

What else? From the original post:

Remember that DLookup() and related functions pass their criterion
strings straight through to the db engine, so the same syntax applies.


Best wishes


Tim F

 

[Guest]

I ment SQL Server then you use single quote (I'm not sure about other DB)

 

[Guest]

It's just that I'm a part of the 94%, and I never needed to set up the
format, unless sending parameters to the SQL Server, then I needed to change
the format on the date.
In my organization, the Regional Settings are set automatically as we want
when the user log on, so they all set the same, so I assume this is why I
never had the need to set the format.

Thanks for clarifying it for me.

 

[Tim Ferguson]

It's just that I'm a part of the 94%, and I never needed to set up the
format, unless sending parameters to the SQL Server, then I needed to
change the format on the date.

I am very suspicious about how this is working; unless you are using
exclusively GUI methods like DoCmd.RunSQL and so on, where the Access
expression evaluator will intercept and make silent changes to the queries.
Although this usually works out okay, it tends to creep me out: I like to
get the thing debugged and working and then left alone!

I ment SQL Server then you use single quote (I'm not sure about other DB)

I am not an SQL Server expert, but with the little work I have done using
ADP-ADO-MSSQLS the date format seems to be "\'yyyymmdd\'" -- I rather think
that you have Access doing this for you behind your back.

All the best


Tim F