Query Criteria from Unbound Calendar

  • Thread starter Thread starter Mark Warren
  • Start date Start date
M

Mark Warren

I've searched the newsgroup, but I can't find exactly what
I'm looking for. I know somebody out here can tell me how
to do this.

I have an unbound ActiveX MSCAL.Calendar.7 object in a
form. I have a query based on a table with the first
field being the date. I want criteria in the date field
to be based on control "Value" in the calendar.

What would my VB code look like in a module for this.

Many thanks.
 
Mark Warren said:
I've searched the newsgroup, but I can't find exactly what
I'm looking for. I know somebody out here can tell me how
to do this.

I have an unbound ActiveX MSCAL.Calendar.7 object in a
form. I have a query based on a table with the first
field being the date. I want criteria in the date field
to be based on control "Value" in the calendar.

What would my VB code look like in a module for this.
Click to expand...
Hi Mark,

In my code, I need to use date selected
in various forms/reports, so I declared a
global var in a module (you may not need
to do that...but may see how to adapt this,
I hope).....

Option Compare Database
Option Explicit
Global datCalDate As Variant

Then, in code of form where set:

Dim ctl As Control, strSQL As String
Dim strEmployeeID As Long

'Name of control is "objCalendar"
Set ctl = Me!objCalendar
'pass date user selected to global datCalDate.
datCalDate = ctl.Value
strEmployeeID = Me.txtEmployeeID

strSQL = "INSERT INTO tblWork (EmployeeID, WorkDate) "_
& "VALUES (" & strEmployeeID & ", #" & datCalDate & "#)"
CurrentDb.Execute strSQL, dbFailOnError

Your code might instead look like:

Dim ctl As Control, strSQL As String
Dim datCalDate As Variant

'Name of control is "objCalendar"
Set ctl = Me!objCalendar
'assign date user selected to datCalDate.
datCalDate = ctl.Value

strSQL = "SELECT * FROM mytable WHERE " _
"[Datefield] = #" & datCalDate & "#;"

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Hi Mark,

(after just reading discussion from Michel...)
Just in case may not be US regional settings,

Dim ctl As Control, strSQL As String
Dim datCalDate As Variant

'Name of control is "objCalendar"
Set ctl = Me!objCalendar
'assign date user selected to datCalDate.
datCalDate = ctl.Value

strSQL = "SELECT * FROM mytable WHERE " _
"[Datefield] = " & FORMAT(datCalDate, "\#mm\/dd\/yyyy\#")

Good luck,

US-centric Gary Walter
 
Back
Top