Recurring Database glitch + copyright

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I am pleased to discover the recurring database developed by Duane Hookam
and hope to successfully adapt it to bookings for a new conference and
community centre I am volunteering at. I have read the threads on two
questions posted by Kelly S and downloaded the sample database. I have
problem entering test data on the downloaded version. When I click on ‘Build
Temporary Schedule’ I receive an error message: Run-time error ‘3075’: Syntax
error in date in query expression ‘#9:30:00 a.m.#’ . The 9:30 is entered in
the start time control and keyed in as 9.30 then tab. In the control this
auto formats to read 9:30 a.m. When I click on debug, this is the code that
is highlighted: db.Execute strSQL, dbFailOnError What could be wrong with
that is beyond my experience, but I would like to have this sample database
working so I can adapt it!

One more thing – I see the basCalendar module is copyright. Does this mean
it can’t be used without permission – or that the copyright should be
included?

Thanks for insights on these two Qs.
 
Hi AnaP,

Your first port of call is after clicking Debug, you can see that the SQL
query is the problem. To see what strSQL actually is, press ctrl-G to go to
the immediate window and type ? strSQL then press enter. Check that the
syntax of the SQL String is correct. Usually date/time issues come down to a
Regional Setting on your computer. Check where the date/time is being
formatted and force it to format the way you need it to using format(DATE,
"yyyy/mm/dd") or format(TIME, "hh:nn:ss")

Hope this helps. Paste your SQL query here if you can't make sense of it.

Damian.
 
Yes, a conflict with the regional settings on my computer makes sense since
we format date by dd/mm/yyyy here. I followed your instructions and could see
that no matter how I entered the date it was being defined in the syntax
string as dd/mm/yyyy. On the form I changed the format properties for the
date field to dd/mm/yyyy, which didn't help. Presuming I needed to force the
format to be mm/dd/yyyy in the SQL I then tried to insert
format(DATE,"mm/dd/yyyy") into what seemed the two most likely places in the
code syntax and a couple of other places in the code. Just guessing, but no
joy. Thanks for offering more help! Here's what i got in the immediate
window:

?strSQL
INSERT INTO tblTempSchedDates (tscDate, tscActID, tscLocID, tscStartTime,
tscEndTime, tscNotes ) Values(#27/11/2006#,2, 5, #9:30:00 a.m.#, #11:30:00
a.m.#,"changed date format in properties")

Thanks again!
Ana
 
What's the code you're using to generate strSQL?

You need to use the Format statement in that code.

You might find it useful reading to check Allen Browne's "International
Dates in Access" at http://allenbrowne.com/ser-36.html or what I have in my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be downloaded
for free at http://www.accessmvp.com/djsteele/SmartAccess.html)
 
Indeed, it was interesting reading. Thanks. Still stuck though. Here is the
code and below I'll tell you what I tried:
Private Sub cmdBuildSchedule_Click()
Dim datThis As Date
Dim lngActID As Long
Dim lngLocID As Long
Dim varNotes As Variant
Dim strSQL As String
Dim db As DAO.Database
Dim intDOW As Integer 'day of week
Dim intDIM As Integer 'Day in month
If Me.grpRepeats = 2 Then
If Not CheckDates() Then
Exit Sub
End If
End If
If Not CheckTimes() Then
Exit Sub
End If
If IsNull(Me.cboActID) Then
MsgBox "You must select an Activity.", vbOKOnly + vbInformation,
"Enter Activity"
Me.cboActID.SetFocus
Me.cboActID.Dropdown
Exit Sub
End If
If IsNull(Me.cboLocID) Then
MsgBox "You must select a Location.", vbOKOnly + vbInformation,
"Enter Location"
Me.cboLocID.SetFocus
Me.cboLocID.Dropdown
Exit Sub
End If
'strTitle = Me.txtTitle
varNotes = Me.txtNotes
lngLocID = Me.cboLocID
lngActID = Me.cboActID
Set db = CurrentDb
If Me.grpRepeats = 2 Then 'need to loop through dates
For datThis = Me.txtStartDate To Me.txtEndDate
intDIM = GetDIM(datThis)
intDOW = Weekday(datThis)
If Me("chkDay" & intDIM & intDOW) = True Or _
Me("chkDay0" & intDOW) = True Then
strSQL = "INSERT INTO tblTempSchedDates (" & _
"tscDate, tscActID, tscLocID, " & _
"tscStartTime, tscEndTime, tscNotes ) " & _
"Values(#" & datThis & "#," & lngActID & ", " & _
lngLocID & ", #" & Me.txtStartTime & "#, #" & _
Me.txtEndTime & "#," & _
IIf(IsNull(varNotes), "Null", """" & varNotes & """") &
")"
db.Execute strSQL, dbFailOnError
End If
Next
Else 'dates are there, just add the title, notes, times, location,
Activity
strSQL = "Update tblTempSchedDates Set tscActID = " & lngActID & _
", tscLocID = " & lngLocID & ", tscStartTime =#" &
Me.txtStartTime & _
"#, tscEndTime = #" & Me.txtEndTime & "#"

If Len(varNotes & "") > 0 Then
strSQL = strSQL & ", tscNotes = " & IIf(IsNull(varNotes), Null,
"""" & varNotes & """")
End If
db.Execute strSQL, dbFailOnError
End If
Me.sfrmTempScheduleEdit.Requery
MsgBox "Temporary schedule built. " & _
"You can now edit the schedule and " & _
"append to the permanent schedule.", vbOKOnly + vbInformation, "Temp
schedule complete"
End Sub

OK. Since the date is taken from datThis, I tried:

For datThis = Format(Me.txtStartDate, "mm\/dd\/yyyy") To
Format(Me.txtEndDate, "mm\/dd\/yyyy")

That didn't work so in the strSQL I tried:

strSQL = "INSERT INTO tblTempSchedDates (" & _
"tscDate, tscActID, tscLocID, " & _
"tscStartTime, tscEndTime, tscNotes ) " & _
"Values(#" & Format(datThis, "mm\/dd\/yyyy") & "#," &
lngActID & ", " & _
lngLocID & ", #" & Me.txtStartTime & "#, #" & _
Me.txtEndTime & "#," & _

Unfortunately, that didn't work either. I wondered if the wrapping function
would be the best way to go, but had even less ideas on how or where to put
that. The form controls are unbound. I set the formats in the date fields to
'Short Date'

Thanks again
Ana
 
Try:

For datThis = CDate(Me.txtStartDate) To CDate(Me.txtEndDate)

Definitely keep the format in the assignment to strSQL.
 
That worked! The date was being formatted in the American way, but I was
still getting the same error message so using the info given to me thus far I
added:
Format(me.txtStartTime, "hh:nn") to all the code referring to the time and
it worked!! Now I have the demo working I can work on the adaptation. More
challenges ahead, no doubt! Thanks so much.
Ana
 
Back
Top