Select Query in Module

[boborsipho]

I've been struggling for a while now.

How do I run a Select query in a module in MS Access?

I tried everything:

Dim sql As String
Dim rs As Recordset
Dim db As Database
Set f = Forms!frmCalender
sql = <<SQL Statement>>
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

This code gives me: &quot;Too few parameters. Expected 1&quot; - Error

What am I doing wrong?

Thank you
Bob

 

[grnzbra]

Ah, yes, the old &quot;too few parameters&quot; problem. Check your SQL statement. Dates must show up with # on either side of the date, text must have &quot; on either site (or perhaps it's '). Numerics don't need anything. Anyway, put a break right after the SQL statement is created and check the statement in the debugger (view>debugger) to see that these variables have the proper characters surrounding them.

 

[boborsipho]

Ok, I sort of understand what you mean. Here's my SQL statement:

sql = &quot;SELECT [Entertainment]![Name] & [Entertainment]![FirstName] & ' ' & [Entertainment]![Surname] AS TheName, EventCalendar.Date, EventCalendar.Time, EventCalendar.Highlighted FROM Entertainment, EventCalendar WHERE Entertainment.UniqueEntertainmentNumber=EventCalendar.UniqueEntertainmentNumber AND EventCalendar.Date=[Forms]![frmCalender]![Date&quot; & mynum & &quot;];&quot;

What should I do with the part where I refer to a Form. I'm not really very familiar with Vb, but unfortunately I have to use it.

Thanks

 

[grnzbra]

The last line should look like:
...AND EventCalendar.Date= #&quot; & [Forms]![frmCalender]![Date&quot; & mynum & &quot;] & &quot;#;&quot;
Or something like that. I'm not sure about the criteria before the AND but uset the same rules. It must have eithe a single or double quote around text, a # around dates and nothing around numerics.
Check Access Help button for &quot;Using Variables in SQL&quot;. They have a good explanation.

 

[grnzbra]

Or, perhaps it should be:
...AND EventCalendar.Date= #&quot; & [Forms]![frmCalender]![Date] #&quot; & mynum & &quot;;&quot;
I'm not exactly sure. Play with it a little.

 

[grnzbra]

That doesn't look quit right either. What kind of field is EventCalendar.Date? Is it a date, text or numeric?

 

[boborsipho]

I tried this now, but now I have another error: Object 'Times' already exist. This is driving me nuts.

Dim rs As Recordset
Dim db As Database
Dim qdf As QueryDef
Dim temp As String

temp = &quot;Date&quot; & mynum & &quot;&quot;
//mynum is a parameter of the function

sql = &quot;SELECT [Entertainment]![Name] & [Entertainment]![FirstName] & ' ' & [Entertainment]![Surname] AS TheName, EventCalendar.Date, EventCalendar.Time, EventCalendar.Highlighted FROM Entertainment, EventCalendar WHERE Entertainment.UniqueEntertainmentNumber=EventCalendar.UniqueEntertainmentNumber AND EventCalendar.Date=&quot; & &quot;[Forms]![frmCalender]!#[&quot; & temp & &quot;]#;&quot;

Set qdf = db.CreateQueryDef(&quot;Times&quot;, sql)
Set rs = qdf.OpenRecordset(dbOpenDynaset)