automate date increments by weeks and add records

[Pragv]

Thank you Mr. Douglas,

I now included the # delimiters.
Here's my original query in VBA code behind my form

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 & _
"#;"
Debug.Print strqryinsert
db.Execute strqryinsert, dbFailOnError

Here is what the debug.print gives when I'm debugging for some input
values

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

I still get the error about too few parameters expected 2.

The table that I'm inserting data into (tblworkhrs) has additional
fields in it with an auto number as primary id. But I'm inserting data
into only two fields. The default value for the rest of them is 0. Do
you think that's causing a problem?
Do I need to specifically input zeros into the rest of my fields (about
13-14 of them) through my query to get this to work?

Any help is greatly appreciated.

Thanks

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]

 

[Douglas J. Steele]

So table tblworkhours does have fields Admin_DocPrep_Hrs and WDate (with
Admin_DocPrep_Hrs being a numeric field, and WDate being a Date/Time field),
and table NUM has fields N, personid and projectid (where all three of those
fields are numeric)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you Mr. Douglas,

I now included the # delimiters.
Here's my original query in VBA code behind my form

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 & _
"#;"
Debug.Print strqryinsert
db.Execute strqryinsert, dbFailOnError

Here is what the debug.print gives when I'm debugging for some input
values

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

I still get the error about too few parameters expected 2.

The table that I'm inserting data into (tblworkhrs) has additional
fields in it with an auto number as primary id. But I'm inserting data
into only two fields. The default value for the rest of them is 0. Do
you think that's causing a problem?
Do I need to specifically input zeros into the rest of my fields (about
13-14 of them) through my query to get this to work?

Any help is greatly appreciated.

Thanks

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]

 

[Pragv]

So table tblworkhours does have fields Admin_DocPrep_Hrs and WDate (with
Admin_DocPrep_Hrs being a numeric field, and WDate being a Date/Time field),

Yes it does. Along with these, it also has personid, projectid and 10
other fields like Adim_storehrs etc (different categories of hours)

and table NUM has fields N, personid and projectid (where all three of those
fields are numeric)?

Table NUM has only one field N that is a number (long integer)
....basicall numbers from 0 to 7000 (according to Mr. Vinson's
suggestion). The fields personid and projectid belong to tblworkhours.

Thank you.

 

[Pragv]

So table tblworkhours does have fields Admin_DocPrep_Hrs and WDate (with
Admin_DocPrep_Hrs being a numeric field, and WDate being a Date/Time field),

Yes it does. Along with these, it also has personid, projectid and 10
other fields like Adim_storehrs etc (different categories of hours)

and table NUM has fields N, personid and projectid (where all three of those
fields are numeric)?

Table NUM has only one field N that is a number (long integer)
....basicall numbers from 0 to 7000 (according to Mr. Vinson's
suggestion). The fields personid and projectid belong to tblworkhours.

Thank you.

 

[Douglas J. Steele]

You don't need to refer to personid and projectid in your SELECT statement,
then:

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

If you're trying to populate those two fields in tblworkhours, you need

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

 

[Pragv]

Thank you Douglas,

here is my new query

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

Here is what debug.print gives during run time

INSERT INTO tblworkhours(personid1, projectid1,[Admin_Mtgs_Hrs],
[WDate]) SELECT 1, 90, 1 , DateAdd('d', 7*[N], #1/1/2007#) FROM NUM
WHERE DateAdd('d', 7*[N], #1/1/2007#) <= #1/15/2007# ;

The error I get is 'Error evaluating CHECK constraint' I don't even
know what that means.
There is no info in help too.

personid1, projectid1 are long integers, WDate is Date/Time, N is Long
Number, Admin_Mtgs_Hrs is Double and nhrs (variable bound to a text box
on the form is double too.

Any ideas on why I'm getting this?
Thank you for your amazing patience.

 

[John Vinson]

I had a validation rule on WDate (date field) as CVDate for some
reason.
I took that out and yahoo! my code is working...

Thanks a lot! to you and to Mr. Vinson!!

Wonderful! Thanks, Douglas, for stepping in - I was getting really
flummoxed!

John W. Vinson[MVP]