Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Chatham, Ontario, Canada
    Posts
    86
    Thanks
    5
    Thanked 1 Time in 1 Post

    form values inaccessible to queries

    I have a problem with values assigned to text boxes in a form becoming inaccessible? I really don't understand what is happening.

    In a form I select opening and closing dates for a set of reports and assign the dates to text boxes. From that form I open a second form with several quick lookups. One of those requires emptying a table then repopulating it with the following query.
    INSERT INTO tempWhoWentWhere ( siteID, clientID, eventID, event_date ) SELECT DISTINCT tblEvent.siteID, qryFirstEvent.clientID, tblEvent.eventID, tblEvent.event_date FROM qryFirstEvent INNER JOIN tblEvent ON qryFirstEvent.MinOfevent_date = tblEvent.event_date WHERE (((tblEvent.event_date)>=[Forms]![frmReportMenu]![program_start_date] And (tblEvent.event_date)<=[Forms]![frmReportMenu]![program_end_date]));

    The start and end dates are those mentioned earlier.

    If I create a stand alone query and test it after I set the dates then it produces the expected results. I can then open the second form and the query still works properly.

    However after I click on the button which activates the following code the query produces no results.
    Dim curDb As Database, strAttend As String, strNewPreNat As String, stdat As Date, endat As Date, strQry As String
    Dim recAttend As Recordset, recNewPreNat As Recordset, recStats As Recordset
    Set curDb = CurrentDb()
    strQry = "Delete * FROM tempWhoWentWhere"
    curDb.Execute (strQry)
    strQry = (as above)
    curDb.Execute (strQry)

    Whats even more curious is that it fails even if I stop the script prior to executing the query. This script fails on the second execute command with 'not enough parameters - expected 2'.

    After a little more investigation it appears that the problem is not restricted to this query. The dates are not available to any other query.

    I tried assigning the date to foo in the aforementioned script and that works OK.

    Can anyone shed some light on this?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    When you include a reference to a form in an SQL string, the reference to the form needs to be outside the string double quotes so it is just the value of the form control that is used in the SQL.

    Code:
    Dim strQRY as string
    strQRY= "INSERT INTO tempWhoWentWhere ( siteID, clientID, eventID, event_date )  SELECT DISTINCT tblEvent.siteID, qryFirstEvent.clientID,  tblEvent.eventID, tblEvent.event_date FROM qryFirstEvent INNER JOIN  tblEvent ON qryFirstEvent.MinOfevent_date = tblEvent.event_date WHERE  (((tblEvent.event_date)>=" & [Forms]![frmReportMenu]![program_start_date]  & " And  (tblEvent.event_date)<=" & [Forms]![frmReportMenu]![program_end_date] & "))"
    Here is Australia that still does not work as Dates used in SQL need to be in mm/dd/yyyy format and we use dd/mm/yyyy. So for us it needs to be:

    Code:
    Dim strQRY as string
    strQRY= "INSERT INTO tempWhoWentWhere ( siteID, clientID, eventID, event_date )  SELECT DISTINCT tblEvent.siteID, qryFirstEvent.clientID,  tblEvent.eventID, tblEvent.event_date FROM qryFirstEvent INNER JOIN  tblEvent ON qryFirstEvent.MinOfevent_date = tblEvent.event_date WHERE  (((tblEvent.event_date)>=#" & format([Forms]![frmReportMenu]![program_start_date],"mm/dd/yyyy")  & "# And  (tblEvent.event_date)<=#" & format([Forms]![frmReportMenu]![program_end_date],"mm/dd/yyyy") & "))"
    #
    Regards
    John



  3. The Following User Says Thank You to johnhutchison For This Useful Post:

    fjbr (2011-04-17)

  4. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Chatham, Ontario, Canada
    Posts
    86
    Thanks
    5
    Thanked 1 Time in 1 Post
    I knew that or at least I used to/should have. Thanks for the format tip. We have the same issue here compounded by the fact that half the machines I come across are set to US date format.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •