Extracting records based on date in control on form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PRH - 03 Apr 2006 16:26 GMT

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

Reply to this Message

Allen Browne - 03 Apr 2006 16:37 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
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
[quoted text clipped - 21 lines]
>
> Thanks

Reply to this Message

PRH - 03 Apr 2006 17:31 GMT

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.
>
[quoted text clipped - 29 lines]
> >
> > Thanks

Reply to this Message

Allen Browne - 04 Apr 2006 02:24 GMT

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)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Many thanks for the guidance. I've updated the parameter as you suggested.
>
[quoted text clipped - 43 lines]
>> >
>> > I can't work out why this is happening and would appreciate any help.

Reply to this Message

Joshua A. Booker - 03 Apr 2006 16:39 GMT

Try to insert the pound signs like this:

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

HTH,
Josh

> I am trying to create a recordset using a date held in a control on the form
> to which the event procedure applies.
[quoted text clipped - 19 lines]
>
> Thanks

Reply to this Message