Access Calendar issue

Soegstad

Registered User.
Local time
Today, 21:10
Joined
Dec 3, 2003
Messages
53
Hi Guys,
I have just recently downloaded a calendar from the forum, but I have some date problems (as far as I know).
The calendar form itself runs smoothly. However, when I try to enter a new appointment I get an error:
Syntax error in date in query expression (([tblInput].InputDate=#" & f.[InputDate] & "#));"

Full code is:
Private Sub OK_Click()
On Error GoTo Err_OK_Click

If ConvertNulls(Me!InputText, "") = "" And ConvertNulls(Me!original_text, "") <> "" Then
Delete_Click
Exit Sub
End If

Dim db As DAO.Database
Dim rs As DAO.Recordset
'Dim cr As String
Dim sql As String
Dim f As Form
Dim g As Form
Dim mr As Integer
Set f = Forms!frmInputBox
Set g = Forms!frmCalendar
Set db = CurrentDb()
sql = "SELECT * FROM [tblInput] WHERE (([tblInput].InputDate=#" & f.[InputDate] & "#));"
Set rs = db.OpenRecordset(sql)
If rs.RecordCount = 0 Then
If IsNull(f!InputText) Then
'mr = MsgBox("No text to save", 48, "Try Again")
DoCmd.Close acForm, Me.NAME
Exit Sub
End If
rs.AddNew
rs!InputDate = f!InputDate
rs!InputText = f!InputText
rs.Update
g("Text" & f!InputDay) = f!InputText
Else
If IsNull(f!InputText) Then
rs.Delete
g("Text" & f!InputDay) = Null
DoCmd.Close
Exit Sub
End If
rs.Delete
rs.AddNew
rs!InputDate = f!InputDate
rs!InputText = f!InputText
rs.Update
g("Text" & f!InputDay) = f!InputText
End If
DoCmd.Close

Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click

End Sub

I'm also recieving an error when opening a month that has appointments (the actual date should in theory change color):
Run Time error 3077. Syntax error in date expression.
The error occurs at this line:
rs.FindFirst "InputDate = #" & myDate & "#"
The full code is:
Public Sub PutInData()
Dim sql As String
Dim f As Form
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim myDate As Date

Set f = Forms!frmCalendar

'Empty out the previous month
For i = 1 To 37
f("text" & i) = Null
f("text" & i).BackColor = 10944511
Next i

'Construct a record source for the month
sql = "SELECT * FROM [tblInput] WHERE ((MONTH(InputDate) = " & f!month & " AND YEAR(InputDate)= " & f!year & ")) ORDER BY InputDate;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

'Populate the calendar
If rs.RecordCount > 0 Then
For i = 1 To 37
If IsDate(f("date" & i)) Then
myDate = Format((f("date" & i)), "dd/mm/yyyy")
rs.FindFirst "InputDate = #" & myDate & "#"
If Not rs.NoMatch Then
f("text" & i) = rs!InputText
f("text" & i).BackColor = 12058551
Else
f("text" & i).BackColor = 10944511
End If
End If
Next i
End If

End Sub
I'm also uploading the database.
Hope somebody will be able to help. Any suggestions would be greatly apprechiated.
 

Attachments

Dates are always tricky. Depending on the regional setting on a certain computer dates are getting a bit garbled. In the netherlands we use dd-mm-yyyy and in the states they use mm-dd-yyyy that's why i switched to store dates as Longs. Time will be stored as Doubles.

I believe you have a similar problem. My advice is to switch to numeric values.
Both are easily returned to their original state using the CDate statement.

And instead of your ConvertNulls function you could use the built in Nz function which does the same.
Enjoy!
 
Thanks Guus for your reply!

I've now switched the table to hold numeric values. My coding skills aren't all that good, so I havent managed to alter the code to handle numerics instead of dates. Does this require alot of recoding or is it only a couple of changes. Thanks again.
 
Nevermind, managed to change dates to numerical. That made the trick. Thanks again
 

Users who are viewing this thread

Back
Top Bottom