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

    shorttxt variable issue and dates

    Hi



    I have created a temporary variable

    called varYear

    I use it to call on e of two forms using

    Code:
    Private Sub btn_Search_Click()
    TempVars.Add "varYear", Me.txtYearOfBaptism.Value
        If DCount("*", "tbl_Baptism", "YearOfBaptism= '" & Me.txtYearOfBaptism & "'") > 0 Then
    
            DoCmd.Close acForm, "frm_BaptismYearSearch"
            DoCmd.OpenForm "frm_BaptismYearSearchResults", acNormal, "", "", , acNormal
        Else
             DoCmd.Close acForm, "frm_BaptismYearSearch"
            DoCmd.OpenForm "frm_NoBaptismResults"
        End If
    
    
    End Sub
    It all works perfectly or so I thought

    If I create the var Year via a button on a form using the value 1962 it opens my form perfectly and shows my message No results found.

    However I have discovered that if I input the year 1710 as there is a record with that year the frm_BaptismYearSearchResults opens and shows the details but if I input 1710 for which there is no record then the form frm_NoBaptismResults does not open it white screens.

    In the table the field YearOfBaptism in short text.

    I've searched on the web and found Access does not like dates in the 1700s which is why I made the field short text

    Have I missed something as 1965 works and 1710 does not?

    thanks

    Ian

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I don't see where varYear is used in the code after creating it.

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

    I see I may have missed out a piece if information.
    I have a form with a text box called txtyearofBaptism. A button on the form runs the code in the original post.

    If the dcount is 0 then a form is opene advising the user that no results have been found

    If the dcount value is not 0 then
    frm_BaptismYearSearchResults

    is opened based on the query

    Code:
    SELECT tbl_Parish.Parish, tbl_Church.Church, tbl_Baptism.FicheNo, tbl_Baptism.BirthDate, tbl_Baptism.DateOfBaptism, tbl_Baptism.YearOfBaptism, tbl_Baptism.FullDateOfBaptism, tbl_Baptism.ChildsName, tbl_Baptism.Surname, tbl_Baptism.Sex, tbl_Baptism.Parents, tbl_Baptism.Abode, tbl_Baptism.Occupation, tbl_Baptism.RefNo, tbl_Baptism.PageNo, tbl_Baptism.EntryNo, tbl_Baptism.Minister, tbl_Baptism.Notes, tbl_Baptism.BaptismID
    FROM tbl_Parish INNER JOIN (tbl_Church INNER JOIN tbl_Baptism ON tbl_Church.ChurchID = tbl_Baptism.ChurchID_fk) ON tbl_Parish.ParishID = tbl_Church.ParishID_fk
    WHERE (((tbl_Baptism.YearOfBaptism) Like [TempVars]![varYear] & "*"))
    ORDER BY tbl_Baptism.Surname;
    However as I said before a date of 1965 works perfectly whereas 1700 does not

    cheers

    Ian
    Last edited by Jen0dorf; 07-24-2016 at 04:25 PM. Reason: missed code

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    That's normal behavior having nothing to do with the dates. If your query pulls no data for the recordselector source, there's nothing to show.
    Try adding a hidden textbox to the form with a default value. That will give the form something to show in all cases, regardless of the query.


  5. #5
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Thanks, I'll try that

    cheers

    Ian

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Why not add a combo box to your form that containsl YearOfBaptism from tbl_Baptism, then when they select a year you show that data? This way they only select a valid date that will pull back data.

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

    thats a thought |I'll try it tomorrow

    cheers

    Ian

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

Similar Threads

  1. Replies: 19
    Last Post: 06-08-2016, 07:55 AM
  2. Replies: 3
    Last Post: 02-11-2014, 11:42 AM
  3. syntax issue when searchin dates
    By mike02 in forum Programming
    Replies: 1
    Last Post: 07-10-2013, 10:09 AM
  4. Help with a Null Variable Issue
    By EDNYLaw in forum Access
    Replies: 6
    Last Post: 03-26-2013, 10:56 AM
  5. Access Query Issue with 2012 dates
    By gtrudel in forum Access
    Replies: 2
    Last Post: 04-16-2012, 05:35 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