VB query help (1 Viewer)

[Benno]

Hi

I have a calendar on a form, when a user chooses a date from the calendar, I want to check whether the date chosen already exists within an append table that I have under the 'Date' field.

I thought the code below would do it but unfortunately not....any ideas?

Thanks in advance
Benno


Sub GenFunctions()

Dim x As Integer
Dim CountThisDate As Date
Dim dbs As Database
Dim rst As Recordset

CountThisDate = [Forms]![form1].[script_date]
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Count(Ericsson.Date) As CountOfDate" & "FROM Ericsson WHERE Date = #" & CountThisDate & "#;")
x = rst!CountOfDate
MsgBox x

End Sub

 

[Fizzio]

2 things.

1. Don't call a field DATE as it is a protected function in Access - it will throw up all sorts of problems - rename your field
2. consider using a DCount (unless you plan to do something else with the recordset) ie
DCount ("[ID]", "Ericsson","[MyDate] = #" & [Forms]![form1]![script_date] & "#")
I'm not sure if you will need to format the date though into mm/dd/yyyy as you do in dynamic SQL strings.