automate date increments by weeks and add records

[Pragv]

Hi,
I have a database that stores hours for each employee by week.
Something like this

EmployeeiD ProjectID Store_Hrs Cleaning_Hrs Cashier_Hrs Date
1 1 2 1
3 01/02/2006
1 1 1 3
2 01/09/2006
2 2 2 2
1 01/02/2006
2 1 0 1
0 01/09/2006

I have several employees and that are assigned work for each week
through out 2006.
I would like to be able to enter the same hours for several consequtive
weeks.
I currently have a form where the employees can enter a week date (like
01/15/2006) and the hours for each category (store_Hrs etc).
I would like to have a form where the employee enters a begin date and
an end date in two text boxes. If the end date is greater than the max
date available for that employee in the database, then the dates should
be added by increments of weeks until the end date and the hours
entered for a particular category for every date. This is because the
employee is assigned to work a fixed 'x' hours each week for a project
for say some 4 months.
For example if employee 2 enters a begin date of 01/01/2006 and an end
date of 05/31/2006 and enters 2 for store__hrs, the code checks for the
latest date for employee 2 available in the database which is
01/09/2006 < 05/31/2006 (end date) in this case and then the code adds
dates from 01/09/2006 until 05/31/2006 in the increments of 7 like
01/15/2006, 01/22/2006 etc until 05/31/2006 and then inputs the number
2 in the store_hrs category for each date created.
Basically, this is inputting blanket hours (the same hours for a
particular category over a period of time) instead of creating a new
record each time and then inputting the same hours for each week
manually.
In case the end date is less than the latest date in the database for
that particular employee, only the hours for that particular category
need updated for each date.

Is this something that can be done using VBA? Or Am I pushing my luck?

Any help would be greatly appreciated.

Thank you.

 

[Pragv]

Sorry I see the formating is screwed up
Here is a better format of the table I'm talking about
EmployeeiD ProjectID Store_Hrs Cleaning_Hrs Date
1 1 2 1
01/02/2006
1 1 1 3
01/09/2006
2 2 2 2
01/02/2006
2 1 0 1
01/09/2006

Thanks again for thr help.

 

[Pragv]

A better format...sorry!
EmpiD ProjectID Store_Hrs Clea_Hrs Date
1 1 2 1 01/02/2006
1 1 1 3 01/09/2006
2 2 2 2 01/02/2006
2 1 0 1 01/09/2006

 

[John Vinson]

I would like to have a form where the employee enters a begin date and
an end date in two text boxes. If the end date is greater than the max
date available for that employee in the database, then the dates should
be added by increments of weeks until the end date and the hours
entered for a particular category for every date. This is because the
employee is assigned to work a fixed 'x' hours each week for a project
for say some 4 months.

One way you can do this is with the help of a general-purpose
auxiliary table. I'll routinely create a table Num with one Long
Integer field N, filled with values 0 through 10000 or so.

You can create an Append query like:

INSERT INTO Schecule(EmployeeiD, ProjectID, Store_Hrs, Cleaning_Hrs,
Cashier_Hrs, [Date])
SELECT [Forms]![FormName]![cboEmployeeID],
[Forms]![FormName]![cboProjectID],
[Forms]![FormName]![txtStore_Hrs],
[Forms]![FormName]![txtCleaning_Hrs],
DateAdd("d", 7*[N], [Forms]![FormName]![txtStartDate]
FROM Num
WHERE DateAdd("d", 7*[N], [Forms]![FormName]![txtStartDate]) <=
[Forms]![FormName]![txtEndDate];


John W. Vinson[MVP]

 

[Pragv]

Thank you very much for the reply.
Sorry for the repost. I only did it because I felt my messy formatting
would drive away viewers. My apologies.

I haven't tried you code yet but would it be posible to check for the
latest available date in the database and add the increments from
there?
I mean many times, the start date can be less than an existing date in
the table and so can be rewritten.
I'm also planning to allow users to chose only one category of hours at
a time when making bulk updates.
Would that help with?
I'll try this code.
Thank you for the help!

I would like to have a form where the employee enters a begin date and
an end date in two text boxes. If the end date is greater than the max
date available for that employee in the database, then the dates should
be added by increments of weeks until the end date and the hours
entered for a particular category for every date. This is because the
employee is assigned to work a fixed 'x' hours each week for a project
for say some 4 months.

One way you can do this is with the help of a general-purpose
auxiliary table. I'll routinely create a table Num with one Long
Integer field N, filled with values 0 through 10000 or so.

You can create an Append query like:

INSERT INTO Schecule(EmployeeiD, ProjectID, Store_Hrs, Cleaning_Hrs,
Cashier_Hrs, [Date])
SELECT [Forms]![FormName]![cboEmployeeID],
[Forms]![FormName]![cboProjectID],
[Forms]![FormName]![txtStore_Hrs],
[Forms]![FormName]![txtCleaning_Hrs],
DateAdd("d", 7*[N], [Forms]![FormName]![txtStartDate]
FROM Num
WHERE DateAdd("d", 7*[N], [Forms]![FormName]![txtStartDate]) <=
[Forms]![FormName]![txtEndDate];


John W. Vinson[MVP]

 

[John Vinson]

Thank you very much for the reply.
Sorry for the repost. I only did it because I felt my messy formatting
would drive away viewers. My apologies.

I saw the two formatting corrections - that was find - I was replying
about the third repeat posting, two hours after the first. Don't worry
about it!

I haven't tried you code yet but would it be posible to check for the
latest available date in the database and add the increments from
there?

Sure, you can use DMax() to find the maximum existing date in a range.

I mean many times, the start date can be less than an existing date in
the table and so can be rewritten.

That I don't understand. The query I suggest will add new records to
the table; it will NOT update existing records. To do so would require
a different query, an Update query without using the Num table.

I'm also planning to allow users to chose only one category of hours at
a time when making bulk updates.

I do not know what a "category" is in your database but I would
presume that you could have an additional control on your Form to
select the category, just as my suggestion would use combos for the
ProjectID.


John W. Vinson[MVP]

 

[Pragv]

Thank you Mr. Vinson for your help.
I tried using the following code to update my hours based on a begin
date and an end date entered by the user.
I have a combo box to select the workhrs ategory (like clea_hrs or
store_hrs) then text boxes to enter begin date, end date and the hours
for the selected category on my form. I also have text boxes that
obtain the person id and project id from a different form
My requirement here is that if a user enters a begin date that is
greater than the maximum date in the database for this particular
criteria (projectid, personid..), then the code creates dates by
incrementing them by 7 days at a time and inputting the hours entered
for all the dates. If the begin date is less than the max date and the
end date is less than the max date too, only update hrs takes place. If
the end date is greater than max date an dthe begin date is less than
max date, update hours takes place until the max date and the dates and
hours are added from then on.
But my code gives me compile errors. I guess it has to do with the SQL
statements spanning multiple lines. I tried using the _ at the end of
each line but no use.
Can you please help me here?

Dim db As DAO.Database
Dim dbegindate As Date
Dim denddate As Date
Dim dmaxdate As Date
Set db = CurrentDb()
myField = cbadminhrs.Value
dbegindate = Txtbegindate.Value
denddate = Txtenddate.Value
dmaxdate = DMax(Wdate, qryAdmindata)
stadminhrs = Forms![frmbulkhrs]![cbadminhrs]
If DMax(Wdate, qryAdmindata) <= dbegindate Then
strqryinsert = "INSERT INTO tblworkhrs(stadminhrs, [WDate])" & _
"SELECT Forms![frmbulkhrs]![Txthrs]" & _
"DateAdd("d", 7*[N], dbegindate" & _
"FROM Num" & _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
"AND DateAdd("d", 7*[N], dbegindate) <=" & _
"denddate;"
db.Execute strqryinsert, dbFailOnError
Else
If DMax(Wdate, qryAdmindata) >= denddate Then
RunSQL ("Update [tblworkhrs] SET [" & myField &
"]=Forms![frmbulkhrs]![Txthrs]"& _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
"AND WDate>=dbegindate AND Wdate<=denddate");
Else
If DMax(Wdate, qryAdmindata) < denddate Then
RunSQL ("Update tblworkhrs SET ["& myField
&"]=Forms![frmbulkhrs]![Txthrs]" & _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
"AND WDate>=dbegindate AND denddate<=Wdate");
RumSQL ("INSERT INTO tblworkhrs(stadminhrs, [WDate])" & _
"SELECT Forms![frmbulkhrs]![Txthrs]" & _
"DateAdd("d", 7*[N], dmaxdate" & _
"FROM Num" & _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
" AND DateAdd("d", 7*[N], dmaxdate) <=" & _
"denddate;"
End Sub

Thank you very much....

 

[Pragv]

I noticed I did not have End If's . I added the End If's but I get a
syntax error where the SQL statements are...

Thank you

 

[John Vinson]

I noticed I did not have End If's . I added the End If's but I get a
syntax error where the SQL statements are...

Please post your current actual code.

John W. Vinson[MVP]

 

[Pragv]

Here is my current code..

I have compile errors where ever there is a SQL statement in the code..
Thank you for the help..

Dim stadminhrs As String
Dim stqryinsert As String

Option Compare Database

'
Private Sub cmdupdate_Click()
Dim db As DAO.Database
Dim dbegindate As Date
Dim denddate As Date
Dim dmaxdate As Date
Set db = CurrentDb()
myField = cbadminhrs.Value
dbegindate = Txtbegindate.Value
denddate = Txtenddate.Value
dmaxdate = DMax(Wdate, qryAdmindata)
stadminhrs = Forms![frmbulkhrs]![cbadminhrs]
If DMax(Wdate, qryAdmindata) <= dbegindate Then
strqryinsert = "INSERT INTO tblworkhrs(stadminhrs, [WDate])" & _
"SELECT Forms![frmbulkhrs]![Txthrs]" & _
"DateAdd("d", 7*[N], dbegindate" & _
"FROM Num" & _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
"AND DateAdd("d", 7*[N], dbegindate) <=" & _
"denddate;"
db.Execute strqryinsert, dbFailOnError
Else
If DMax(Wdate, qryAdmindata) >= denddate Then
RunSQL ("Update [tblworkhrs] SET [" & myField &
"]=Forms![frmbulkhrs]![Txthrs]"& _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
"AND WDate>=dbegindate AND Wdate<=denddate");
Else
If DMax(Wdate, qryAdmindata) < denddate Then
RunSQL ("Update tblworkhrs SET ["& myField
&"]=Forms![frmbulkhrs]![Txthrs]" & _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
"AND WDate>=dbegindate AND denddate<=Wdate");
RumSQL ("INSERT INTO tblworkhrs(stadminhrs, [WDate])" & _
"SELECT Forms![frmbulkhrs]![Txthrs]" & _
"DateAdd("d", 7*[N], dmaxdate" & _
"FROM Num" & _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
" AND DateAdd("d", 7*[N], dmaxdate) <=" & _
"denddate;"
End If
End If
End If

End Sub

 

[John Vinson]

Here is my current code..

I have compile errors where ever there is a SQL statement in the code..
Thank you for the help..

Well, I'd really recommend creating String variables for you SQL code,
and using the variables rather than big composite RunSQL commands; the
other likely problem is that you're concatenating across lines but
leaving out the important blank characters within the string. You may
also want to use the Querydef Execute method (which lets you trap
errors) rather than RunSQL, which doesn't. Not sure why you're setting
stadminhrs and stqueryinsert as global variables, but I'll leave
them... and there were quite a few other errors, not sure I caught
them all (comments after the affected lines).


Try:

Option Explicit
Option Compare Database

Dim stadminhrs As String
Dim stqryinsert As String

'
Private Sub cmdupdate_Click()
Dim db As DAO.Database
Dim qd As DAO.Querydef ' added JWV
Dim dbegindate As Date
Dim denddate As Date
Dim dmaxdate As Date
Dim strQryUpdate as String
Set db = CurrentDb()
myField = Me.cbadminhrs ' you don't need .Value, it's the default
dbegindate = Me.Txtbegindate
denddate = Me.Txtenddate
dmaxdate = DMax("Wdate", "qryAdmindata") ' Arguments to DMax must be
' strings
stadminhrs = Forms![frmbulkhrs]![cbadminhrs]
' Or Me!cbadminhrs if that's the current form
If dmaxdate <= dbegindate Then ' you already looked this up!
' added blanks before the continuations, and inserted the
' values of form controls rather than their names:
strqryinsert = "INSERT INTO tblworkhrs(stadminhrs, [WDate]) " & _
"SELECT " & Forms![frmbulkhrs]![Txthrs] & ", #" & _ ' date delimiter
DateAdd("d", 7*[N], dbegindate)" & _
"# FROM Num " & _ ' blank added
"WHERE personid=" & forms![frmbulkhrs]!personid & _
" AND projectid=" & forms![frmbulkhrs]!projectid & _
" AND DateAdd("d", 7*[N], dbegindate) <= #" & _
denddate & "#;"
db.Execute strqryinsert, dbFailOnError
Else
If DMax("Wdate", "qryAdmindata") >= denddate Then
strQryUpdate = "Update [tblworkhrs] SET [" & myField & _
"]= " & Forms![frmbulkhrs]![Txthrs] & _
" WHERE personid=" & forms![frmbulkhrs]!personid & _
" AND projectid=" & forms![frmbulkhrs]!projectid & _
" AND WDate>=# & dbegindate & "# " & _
" AND Wdate<=# & denddate & #");"
db.Execute strQryUpdate, dbFailOnError
Else

<Left for you as an exercise>

If DMax(Wdate, qryAdmindata) < denddate Then
RunSQL ("Update tblworkhrs SET ["& myField
&"]=Forms![frmbulkhrs]![Txthrs]" & _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
"AND WDate>=dbegindate AND denddate<=Wdate");
RumSQL ("INSERT INTO tblworkhrs(stadminhrs, [WDate])" & _
"SELECT Forms![frmbulkhrs]![Txthrs]" & _
"DateAdd("d", 7*[N], dmaxdate" & _
"FROM Num" & _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
" AND DateAdd("d", 7*[N], dmaxdate) <=" & _
"denddate;"
End If
End If
End If

End Sub


John W. Vinson[MVP]

 

[Pragv]

Hello Mr Vinson,
Thank you very much for the code.
I used your code and made changes to the last portion based on the
sytax you gave me. I still get the compile error : syntax error where
ever there is a SQL statement.
I tried to compile it before I ran it. Here is my new code. I use
Access 2003, if that helps. I'm new to VBA and the syntax is all greek
and latin to me. I did some coding before in Fortran and C. Please
help!

Dim stadminhrs As String
Dim stqryinsert As String

Option Compare Database

'
Private Sub cmdupdate_Click()
Dim db As DAO.Database
Dim qd As DAO.QueryDef ' added JWV
Dim dbegindate As Date
Dim denddate As Date
Dim dmaxdate As Date
Dim strQryUpdate As String
Set db = CurrentDb()
myField = Me.cbadminhrs ' you don't need .Value, it's the default
dbegindate = Me.Txtbegindate
denddate = Me.Txtenddate
dmaxdate = DMax("Wdate", "qryAdmindata") ' Arguments to DMax must be
' strings
stadminhrs = Forms![frmbulkhrs]![cbadminhrs]
' Or Me!cbadminhrs if that's the current form
If dmaxdate <= dbegindate Then ' you already looked this up!
' added blanks before the continuations, and inserted the
' values of form controls rather than their names:
strqryinsert = "INSERT INTO tblworkhrs(stadminhrs, [WDate]) " & _
"SELECT " & Forms![frmbulkhrs]![Txthrs] & ", #" & _ ' date delimiter
DateAdd("d", 7*[N], dbegindate)" & _
"# FROM Num " & _ ' blank added
"WHERE personid=" & forms![frmbulkhrs]!personid & _
" AND projectid=" & forms![frmbulkhrs]!projectid & _
" AND DateAdd("d", 7*[N], dbegindate) <= #" & _
denddate & "#;"
db.Execute strqryinsert, dbFailOnError
Else
If DMax("Wdate", "qryAdmindata") >= denddate Then
strQryUpdate = "Update [tblworkhrs] SET [" & myField & _
"]= " & Forms![frmbulkhrs]![Txthrs] & _
" WHERE personid=" & forms![frmbulkhrs]!personid & _
" AND projectid=" & forms![frmbulkhrs]!projectid & _
" AND WDate>=# & dbegindate & "# " & _
" AND Wdate<=# & denddate & #";"
db.Execute strQryUpdate, dbFailOnError
Else


If DMax("Wdate", "qryAdmindata") < denddate Then
StrQryinsupdate = "Update [tblworkhrs] SET [" & myField & _
"]= " & Forms![frmbulkhrs]![Txthrs] & _
" WHERE personid=" & Forms![frmbulkhrs]!PersonID & _
" AND projectid=" & forms![frmbulkhrs]!projectid & _
" AND WDate>=# & dbegindate & "# " & _
" AND denddate<=# & Wdate & #";" ' Im here checking for a variable
date
'less than Wdate(a field in table)-is the syntax same
db.Execute StrQryinsupdate, dbFailOnError
StrQryupinsert= "INSERT INTO tblworkhrs(stadminhrs, [WDate]) " & _
"SELECT " & Forms![frmbulkhrs]![Txthrs] & ", #" & _
DateAdd("d", 7*[N], dmaxdate)" & _
"# FROM Num " & _
"WHERE personid=" & forms![frmbulkhrs]!personid & _
" AND projectid=" & forms![frmbulkhrs]!projectid & _
" AND DateAdd("d", 7*[N], dmaxdate) <= #" & _
denddate & "#;"
db.Execute StrQryupinsert, dbFailOnError
End If
End If
End If

End Sub

 

[John Vinson]

I used your code and made changes to the last portion based on the
sytax you gave me. I still get the compile error : syntax error where
ever there is a SQL statement.

Could you indicate exactly which line of the code is giving a syntax
error?

John W. Vinson[MVP]

 

[John Vinson]

I used your code and made changes to the last portion based on the
sytax you gave me. I still get the compile error : syntax error where
ever there is a SQL statement.

Ok... some missing close quotes. You may need to build these long
complex SQL strings up bit by bit, and use the Debugger to be sure
they're coming out right:

Dim stadminhrs As String
Dim stqryinsert As String

Option Compare Database

'
Private Sub cmdupdate_Click()
Dim db As DAO.Database
Dim qd As DAO.QueryDef ' added JWV
Dim dbegindate As Date
Dim denddate As Date
Dim dmaxdate As Date
Dim strQryUpdate As String
Set db = CurrentDb()
myField = Me.cbadminhrs ' you don't need .Value, it's the default
dbegindate = Me.Txtbegindate
denddate = Me.Txtenddate
dmaxdate = DMax("Wdate", "qryAdmindata") ' Arguments to DMax must be
' strings
stadminhrs = Forms![frmbulkhrs]![cbadminhrs]
' Or Me!cbadminhrs if that's the current form
If dmaxdate <= dbegindate Then ' you already looked this up!
' added blanks before the continuations, and inserted the
' values of form controls rather than their names:
strqryinsert = "INSERT INTO tblworkhrs(stadminhrs, [WDate]) " & _
"SELECT " & Forms![frmbulkhrs]![Txthrs] & ", #" & _
DateAdd("d", 7*[N], dbegindate)" & _
"# FROM Num " & _
"WHERE personid=" & forms![frmbulkhrs]!personid & _
" AND projectid=" & forms![frmbulkhrs]!projectid & _
" AND DateAdd("d", 7*[N], dbegindate) <= #" & _
denddate & "#;"
db.Execute strqryinsert, dbFailOnError
Else
If DMax("Wdate", "qryAdmindata") >= denddate Then
strQryUpdate = "Update [tblworkhrs] SET [" & myField & _
"]= " & Forms![frmbulkhrs]![Txthrs] & _
" WHERE personid=" & forms![frmbulkhrs]!personid & _
" AND projectid=" & forms![frmbulkhrs]!projectid & _
" AND WDate>=#" & dbegindate & "#" & _
" AND Wdate<=#" & denddate & "#;"
db.Execute strQryUpdate, dbFailOnError
Else ' If you have the ELSE you don't need the other IF!
StrQryinsupdate = "Update [tblworkhrs] SET [" & myField & _
"]= " & Forms![frmbulkhrs]![Txthrs] & _
" WHERE personid=" & Forms![frmbulkhrs]!PersonID & _
" AND projectid=" & forms![frmbulkhrs]!projectid & _
" AND WDate>=#" & dbegindate & "#" & _


" AND denddate<=#" & Wdate & "#;" ' Im here checking for a variable
date
'less than Wdate(a field in table)-is the syntax same

No, it isn't. Variables must be OUTSIDE the quotes - you're passing
the *value* of the variable, not its name, into the query; the JET
database engine has no access to the names or values of variables.

If dbenddate is less than or equal to WDate, then WDate is greater
then dbenddate. Don't you want to update for records where WDate is
between dbegindate and dbenddate though??

db.Execute StrQryinsupdate, dbFailOnError
StrQryupinsert= "INSERT INTO tblworkhrs(stadminhrs, [WDate]) " & _
"SELECT " & Forms![frmbulkhrs]![Txthrs] & ", #" & _
DateAdd("d", 7*[N], dmaxdate) & _
"# FROM Num " & _
"WHERE personid=" & forms![frmbulkhrs]!personid & _
" AND projectid=" & forms![frmbulkhrs]!projectid & _
" AND DateAdd("d", 7*[N], dmaxdate) <= #" & _
denddate & "#;"
db.Execute StrQryupinsert, dbFailOnError
End If
End If
End If

End Sub


John W. Vinson[MVP]

 

[Pragv]

Dear Vinson, Thank you very much for the reply.
I finally got the code to compile after building it bit by bit. I got a
syntax error always on the DateAdd function where there were quotation
marks on d
It worked after I removed the quotations. I get a run time error on the
db. execute strqryinsert saying 'Too few parameters. Expected 4' I
doubt it's because of the DateAdd function syntax. I checked in the
debugger and all values are correctly fed into the variables.
Here is my new code. Can you please help me on the syntax of the
DateAdd function? If I have the quotation marks on the "d" in the
function, I get syntax error only there....Thanks

Dim stadminhrs As String
Dim stqryinsert As String

Option Compare Database

'
Private Sub cmdupdate_Click()
Dim db As DAO.Database
Dim qd As DAO.QueryDef '
Dim dbegindate As Date
Dim denddate As Date
Dim dmaxdate As Date
Dim strQryUpdate As String
Set db = CurrentDb()
myField = Me.cbadminhrs 'Because we are currently in the form
dbegindate = Me.Txtbegindate
denddate = Me.Txtenddate
dmaxdate = DMax("WDate", "QryAdmindatasub") 'Inputs to the DMax
function are strings
stadminhrs = Forms![frmbulkhrs]![cbadminhrs]
' Or Me!cbadminhrs if that's the current form
If dmaxdate <= dbegindate Then '
' added blanks before the continuations, and inserted the
' values of form controls rather than their names:
strqryinsert = "INSERT INTO tblworkhours(" & stadminhrs & ", [WDate]) "
& _
" SELECT " & Forms![frmbulkhrs]![txthours] & _
" , DateAdd(d, 7*[N], dbegindate) " & _
" FROM NUM " & _
" WHERE personid=" & Forms![frmbulkhrs]!PersonID & _
" AND projectid=" & Forms![frmbulkhrs]!ProjectId & _
" AND DateAdd(d, 7*[N], dbegindate) <= #" & denddate & _
"#;"

db.Execute strqryinsert, dbFailOnError
Else
If DMax("Wdate", "qryAdmindata") >= denddate Then
strQryUpdate = "Update [tblworkhrs] SET [" & myField & _
"]= " & Forms![frmbulkhrs]![txthours] & _
" WHERE personid=" & Forms![frmbulkhrs]!PersonID & _
" AND projectid=" & Forms![frmbulkhrs]!ProjectId & _
" AND WDate>=#" & dbegindate & "# " & _
" AND Wdate<=#" & denddate & "#;"
db.Execute strQryUpdate, dbFailOnError

Else

If DMax("Wdate", "qryAdmindata") < denddate Then
StrQryinsupdate = "Update [tblworkhrs] SET [" & myField & _
"]= " & Forms![frmbulkhrs]![txthours] & _
" WHERE personid=" & Forms![frmbulkhrs]!PersonID & _
" AND projectid=" & Forms![frmbulkhrs]!ProjectId & _
" AND WDate>=#" & dbegindate & "# " & _
" AND WDate>=#" & denddate & "#;"

db.Execute StrQryinsupdate, dbFailOnError
StrQryupinsert = "INSERT INTO tblworkhrs(" & stadminhrs & ", [WDate]) "
& _
" SELECT " & Forms![frmbulkhrs]![txthours] & ", # " & _
" DateAdd(d, 7*[N], dmaxdate) " & _
"# FROM Num " & _
"WHERE personid=" & Forms![frmbulkhrs]!PersonID & _
" AND projectid=" & Forms![frmbulkhrs]!ProjectId & _
" AND DateAdd(d, 7*[N], dmaxdate) <= #" & _
denddate & "#;"
db.Execute StrQryupinsert, dbFailOnError
End If
End If
End If

End Sub

 

[John Vinson]

If I have the quotation marks on the "d" in the
function, I get syntax error only there

aha!!

Since the DateAdd function is within the quotation marks, the "d"
quotation marks are being misinterpreted as ending (and starting) the
quoted string.

Use 'd' instead, using singlequotes.

John W. Vinson[MVP]

 

[Pragv]

Thank you Mr. Vinson,

I now used single quotation marks but I still get this error
'Too few parameters, expected 2'
Can you please have a quick look at the following query and see if you
can find anything wrong here? (The entire code is posted in the
previous post)

There are two parameters that I'm passing into two fields in a table.
But why does it not recognize the parameters here?


strqryinsert = "INSERT INTO tblworkhours(" & stadminhrs & ", [WDate]) "
& _
" SELECT " & Forms![frmbulkhrs]![txthours] & _
" , DateAdd('d', 7*[N]," & dbegindate & ") " & _
" FROM NUM " & _
" WHERE personid=" & Forms![frmbulkhrs]!PersonID & _
" AND projectid=" & Forms![frmbulkhrs]!ProjectId & _
" AND DateAdd('d', 7*[N]," & dbegindate & ") <= #" &
denddate & _
"#;"
db.Execute strqryinsert, dbFailOnError

Also, do you know if I can put up an undo button on the form with some
quick code to undo the records if they click save by mistake.
I can't tell you how much I appreciate your help. Thank you.

 

[John Vinson]

Thank you Mr. Vinson,

I now used single quotation marks but I still get this error
'Too few parameters, expected 2'
Can you please have a quick look at the following query and see if you
can find anything wrong here? (The entire code is posted in the
previous post)

There are two parameters that I'm passing into two fields in a table.
But why does it not recognize the parameters here?


strqryinsert = "INSERT INTO tblworkhours(" & stadminhrs & ", [WDate]) "
& _
" SELECT " & Forms![frmbulkhrs]![txthours] & _
" , DateAdd('d', 7*[N]," & dbegindate & ") " & _
" FROM NUM " & _
" WHERE personid=" & Forms![frmbulkhrs]!PersonID & _
" AND projectid=" & Forms![frmbulkhrs]!ProjectId & _
" AND DateAdd('d', 7*[N]," & dbegindate & ") <= #" &
denddate & _
"#;"

I'm too tired at midnight to disentangle this. Could you please run
this with a breakpoint set, step through this statement, and post the
actual resulting value of strqryinsert that results? It may also help
to know the datatypes of all the relevant fields.

db.Execute strqryinsert, dbFailOnError

Also, do you know if I can put up an undo button on the form with some
quick code to undo the records if they click save by mistake.
I can't tell you how much I appreciate your help. Thank you.

Me.Undo

will undo the current record. If you've run an Append query you'll
need to run a Delete query to delete the appended records. If you've
run an Update query... you'll need to run another Update query. Gets
nasty!

I guess I never really did address the issue: WHY all this complexity?
Can you not work with bound forms?

John W. Vinson[MVP]

 

[Pragv]

I'm too tired at midnight to disentangle this. Could you please run
this with a breakpoint set, step through this statement, and post the
actual resulting value of strqryinsert that results? It may also help
to know the datatypes of all the relevant fields.

I used debug.print statement to get the value of the query for my
inputs
Here it is

INSERT INTO tblworkhours(Admin_CR_Hrs, [WDate]) SELECT 1 ,
DateAdd('d', 7*[N],1/1/2007) FROM NUM WHERE personid=1 AND
projectid=1 AND DateAdd('d', 7*[N],1/1/2007) <= #1/15/2007# ;

The query looks good to me . But when the next staement : db.Execute
strqryinsert, dbFailOnError is executed, I get the error 'Too few
parameters, expected 2.

I guess I never really did address the issue: WHY all this complexity?
Can you not work with bound forms?

I have bound forms for everything. But my users donot want to update
records one by one all the time. Some times they want to update their
hours for say a couple of months at a time.
They don't want to use the bond forms to update hours for each week.
They just want to be able to enter a date range and update a particular
category hours.
They still work with bound forms for updating records one by one.
Since I'm not sure what date ranges they would enter (whether an
existing date in the database or something for the future or just an
update), I had to do all this.
I hate the complexity too and as you can see, I'm struggling a little
too....

Thank you for all the help. I really appreciate your time for me.

 

[Douglas J. Steele]

I doubt this is the error, but you're missing the # delimiters in your
DateAdd statements:

INSERT INTO tblworkhours(Admin_CR_Hrs, [WDate]) SELECT 1 ,
DateAdd('d', 7*[N],#1/1/2007#) FROM NUM WHERE personid=1 AND
projectid=1 AND DateAdd('d', 7*[N],#1/1/2007#) <= #1/15/2007# ;

The error message you're getting usually implies that you've misspelled the
names of fields that you're using in the SQL, but it's pretty hard to
misspell N! <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm too tired at midnight to disentangle this. Could you please run
this with a breakpoint set, step through this statement, and post the
actual resulting value of strqryinsert that results? It may also help
to know the datatypes of all the relevant fields.

I used debug.print statement to get the value of the query for my
inputs
Here it is

INSERT INTO tblworkhours(Admin_CR_Hrs, [WDate]) SELECT 1 ,
DateAdd('d', 7*[N],1/1/2007) FROM NUM WHERE personid=1 AND
projectid=1 AND DateAdd('d', 7*[N],1/1/2007) <= #1/15/2007# ;

The query looks good to me . But when the next staement : db.Execute
strqryinsert, dbFailOnError is executed, I get the error 'Too few
parameters, expected 2.

I guess I never really did address the issue: WHY all this complexity?
Can you not work with bound forms?

I have bound forms for everything. But my users donot want to update
records one by one all the time. Some times they want to update their
hours for say a couple of months at a time.
They don't want to use the bond forms to update hours for each week.
They just want to be able to enter a date range and update a particular
category hours.
They still work with bound forms for updating records one by one.
Since I'm not sure what date ranges they would enter (whether an
existing date in the database or something for the future or just an
update), I had to do all this.
I hate the complexity too and as you can see, I'm struggling a little
too....

Thank you for all the help. I really appreciate your time for me.

John W. Vinson[MVP]