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.Net 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******@hotmail.com> wrote in message
news:d3**************************@posting.google.c om...
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
