Hi Alicia,
IMHO, It appears that you are missing the CONSTRAINT clause while attemping
to create your Primary Key field.
Try:
CREATE TABLE Weeks (Weekstart datetime Not Null CONSTRAINT Weekstart Primary
Key, Weekend DateTime Not Null);
--------------------------------------------------------------
I'm a little confused by the rest of the code / SQL that follows.
Do segments of it run in seperate processes or procedures? I can't see how
it could possibly run continuously...
Here is some code that I created messing around with this a little this
afternoon.
I think I have succeeded in combining the first 2 operations ... (make the
table and populate it with data in the pattern that I noticed in your
sample.)
Is this what you have in mind?
*************** *************** ***************
Private Sub Command0_Click( )
'IsTableQuery function from
http://support.microsoft.com/default...b;en-us;113549
'Checks to see if the table named "Weeks" already exists.
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim MySQL As String
Dim i As Integer
Dim intRecords As Integer
intRecords = InputBox("How many records would you like to create?", "Create
Records", 1)
Dim dteStart As Date
Dim dteEnd As Date
dteStart = InputBox("Enter start date ... in mm/dd/yyyy format.", "Please
supply a start date", Format(Date, "mm\/dd\/yyyy"))
'This InputBox prompts you for a date, and uses the current system date as a
default.
If Not IsTableQuery("" , "Weeks") Then
'The IsTableQuery function checks to see if the table already exists.
'If it does, it skips this portion of the code ... which prevents an error.
MySQL = ""
MySQL = MySQL & "CREATE TABLE Weeks "
MySQL = MySQL & "(Weekstart datetime Not Null CONSTRAINT Weekstart
Primary Key,"
MySQL = MySQL & " Weekend DateTime Not Null);"
'Debug.Print MySQL ' I used this to check the SQL output, and made sure
that it works
MyDB.Execute MySQL, dbFailOnError
End If
If IsDate(dteStart ) Then
'According to the pattern that you posted, the
'"weekend" value was always 7 days after "weekstart"
dteEnd = DateAdd("d", 7, dteStart)
End If
For i = 0 To intRecords - 1
'This For / Next loop inserts the records, counts how many have been
created so far,
'increments the "weekstart" / "weekend" values,
'and stops when the count reaches the specified number
'insert into weeks(weekstart , weekend) values (#09/30/04#, #10/07/04#)
MySQL = ""
MySQL = MySQL & "INSERT INTO Weeks (weekstart, weekend) values "
MySQL = MySQL & "(#"
MySQL = MySQL & dteStart
MySQL = MySQL & "#, #"
MySQL = MySQL & dteEnd
MySQL = MySQL & "#);"
MyDB.Execute MySQL, dbFailOnError
'Also according to the pattern that you posted, the
'new "weekstart" value was always the same as the
'last record's "weekend" value, so ...
dteStart = dteEnd
dteEnd = DateAdd("d", 7, dteEnd)
Next i
Set MyDB = Nothing
End Sub
*************** *************** *************** **
--
HTH,
Don
=============== ==============
Use
My*****@Telus.N et for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)
I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.
Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop
=============== =============== ==
"Alicia" <al******@hotma il.com> wrote in message
news:d3******** *************** ***@posting.goo gle.com...
Does anyone know why I am getting a "Syntax error in Create Table
statement". I am using Microsoft Access SQL View to enter it. Any
other problems I may run into?
CREATE TABLE weeks
(
weekstart datetime not null primary key,
weekend datetime not null
)
insert into weeks(weekstart , weekend) values (#09/30/04#, #10/07/04#)
insert into weeks(weekstart , weekend) values (#10/07/04#, #10/14/04#)
insert into weeks(weekstart , weekend) values (#10/14/04#, #10/21/04#)
insert into weeks(weekstart , weekend) values (#10/21/04#, #10/28/04#)
insert into weeks(weekstart , weekend) values (#10/28/04#, #11/04/04#)
select w.weekstart, sum(a.[Count]) as newCount
from weeks as w
left outer join alicia as a on w.weekstart > a.[Date]
and w.weekend <= a.[Date]
group by w.weekstart
