Extracting records based on date in control on form

[Guest]

I am trying to create a recordset using a date held in a control on the form
to which the event procedure applies.

In essence, the code is:

Select * From Issues Where Issues.LastUpdate = Forms!IssueStatus!CurrentDate

The control source of Forms!IssueStatus!CurrentDate is =Date()

When the event procedure runs, no records are added to the recordset.

However, if I 'hardcode' the date into the code:

Select * From Issues Where Issues.LastUpdate = #04/03/2006#

it extracts the records as expected but only if I reverse the order of the
date i.e. mm/dd/yyyy (04/03/2006) instead of dd/mm/yyyy (03/04/2006).

The format of Issues.LastUpdate is shortdate.

I can't work out why this is happening and would appreciate any help.

Thanks

 

[Allen Browne]

Access is not understanding the data type correctly.

Open the query in design view.
Choose Parameters on the Query menu.
In the dialog, enter:
[Forms]![IssueStatus]![CurrentDate] Date
This will help the query understand the text box correctly.

 

[Joshua A. Booker]

Try to insert the pound signs like this:

"Select * From Issues Where Issues.LastUpdate =#" &
Forms!IssueStatus!CurrentDate & "#"

HTH,
Josh

 

[Guest]

Many thanks for the guidance. I've updated the parameter as you suggested.

The query works fine when I run it from the design grid (with the form open
and active) but if I use the query to populate the recordset as part of the
AfterUpdate event on the form:

Set rstIssuesHistory = dbsCurrent.OpenRecordset("Query8")

then I get an error message "Run time error 3061. Too few parameters.
Expected 1"

Can you offer any further advice?

Access is not understanding the data type correctly.

Open the query in design view.
Choose Parameters on the Query menu.
In the dialog, enter:
[Forms]![IssueStatus]![CurrentDate] Date
This will help the query understand the text box correctly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to create a recordset using a date held in a control on the
form
to which the event procedure applies.

In essence, the code is:

Select * From Issues Where Issues.LastUpdate =
Forms!IssueStatus!CurrentDate

The control source of Forms!IssueStatus!CurrentDate is =Date()

When the event procedure runs, no records are added to the recordset.

However, if I 'hardcode' the date into the code:

Select * From Issues Where Issues.LastUpdate = #04/03/2006#

it extracts the records as expected but only if I reverse the order of the
date i.e. mm/dd/yyyy (04/03/2006) instead of dd/mm/yyyy (03/04/2006).

The format of Issues.LastUpdate is shortdate.

I can't work out why this is happening and would appreciate any help.

Thanks

 

[Allen Browne]

That would be right. When the query is run from the Database window, the
Expresssion Service runs and evaluates the parameter from the form. But the
ES does not run in a code context, so the parameter is not evaluated.

Option 1: Explain to Access how to get the parameter value before you
OpenRecordset:
Dim qdf As QueryDef
Set qdf = dbsCurrent.QueryDefs("Query8")
qdf.Parameters("[Forms]![IssueStatus]![CurrentDate]") =
Forms!IssueStatus!CurrentDate
Set rstIssuesHistory = qdf.OpenRecordset

Option 2: Forget the stored query, and create the SQL string dynamically:
Dim strSql As String
strSql = "SELECT Table1.* FROM Table1 WHERE MyDateField = #" & _
Format(Forms!IssueStatus!CurrentDate, "mm/dd/yyyy") & "#;"
Set rstIssuesHistory = dbsCurrent.OpenRecordset(strSql)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Many thanks for the guidance. I've updated the parameter as you suggested.

The query works fine when I run it from the design grid (with the form
open
and active) but if I use the query to populate the recordset as part of
the
AfterUpdate event on the form:

Set rstIssuesHistory = dbsCurrent.OpenRecordset("Query8")

then I get an error message "Run time error 3061. Too few parameters.
Expected 1"

Can you offer any further advice?

Access is not understanding the data type correctly.

Open the query in design view.
Choose Parameters on the Query menu.
In the dialog, enter:
[Forms]![IssueStatus]![CurrentDate] Date
This will help the query understand the text box correctly.

I am trying to create a recordset using a date held in a control on the
form
to which the event procedure applies.

In essence, the code is:

Select * From Issues Where Issues.LastUpdate =
Forms!IssueStatus!CurrentDate

The control source of Forms!IssueStatus!CurrentDate is =Date()

When the event procedure runs, no records are added to the recordset.

However, if I 'hardcode' the date into the code:

Select * From Issues Where Issues.LastUpdate = #04/03/2006#

it extracts the records as expected but only if I reverse the order of
the
date i.e. mm/dd/yyyy (04/03/2006) instead of dd/mm/yyyy (03/04/2006).

The format of Issues.LastUpdate is shortdate.

I can't work out why this is happening and would appreciate any help.