Results 1 to 3 of 3
  1. #1
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453

    tempvar and query criteria

    Select m m



    Hi

    just need some clarification.

    I set up 2 temporary variable
    via a button and then opened a form based on a query using the variables as criteria


    Code:
    SELECT tbl_Parish.Parish, tbl_Church.Church, tbl_Banns.FicheRef, tbl_Banns.PageNo, tbl_Banns.EntryNo, tbl_Banns.DateOfBanns, tbl_Banns.YearOfBanns, tbl_Banns.GroomForenames, tbl_Banns.GroomSurname, tbl_Banns.GroomOccupation, tbl_Banns.GroomAbode, tbl_Banns.Minister, tbl_Banns.BrideForenames, tbl_Banns.BrideSurname, tbl_Banns.BrideAbode, tbl_Banns.BrideCondition, tbl_Banns.BrideOccupation, tbl_Banns.BannsId
    FROM tbl_Parish INNER JOIN (tbl_Church INNER JOIN tbl_Banns ON tbl_Church.ChurchID = tbl_Banns.ChurchId_fk) ON tbl_Parish.ParishID = tbl_Church.ParishID_fk
    WHERE (((tbl_Banns.BrideForenames) Like [TempVars]![varBrideForenames] & "*") AND ((tbl_Banns.BrideSurname) Like [TempVars]![varBrideSurname] & "*"))
    ORDER BY tbl_Banns.YearOfBanns;
    Ok if I set varBrideForenames to A and varBrideSurname to M and then run the query it seems to work but

    All the surnames begin with M or contain M but the forenames list contains five entries starting or containing M but it also included the forenames Eliza and Sarah.

    Is this because the Surname begins with M? So it satisfies one of the criteria?

    thanks

    Ian

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ??? Not sure I'm following, but if
    [TempVars]![varBrideForenames] = "A"
    and
    [TempVars]![varBrideSurname] ="M"

    Then you should get names such as

    Annie McDonald, Agnus Middleton, Angelina Malhuish, Arlene Mackelhenny

    You should not be getting Eliza McDonald nor Sarah Shultz (in fact you shouldn't get Sarah anything)

    unless there is more in you tempvar, or your query than we can see.

  3. #3
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Hi

    thanks

    your commets made me think and after searching I realised the vaiable was not being set.

    I deleted and re created and it all works.

    weird

    thanks

    Ian

Please reply to this thread with any new information or opinions.

Similar Threads

  1. tempvar issue
    By Jen0dorf in forum Access
    Replies: 2
    Last Post: 04-24-2016, 03:37 AM
  2. Replies: 7
    Last Post: 04-29-2015, 10:57 AM
  3. Wildcard / TempVar Criteria
    By mrmims in forum Queries
    Replies: 3
    Last Post: 04-08-2015, 04:12 PM
  4. tempvar update query date field
    By raffi in forum Queries
    Replies: 2
    Last Post: 09-27-2014, 11:51 PM
  5. Condition if there is a tempvar
    By Ruegen in forum Programming
    Replies: 1
    Last Post: 11-18-2013, 05:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums