Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    139

    Answered: Check Box Refreshing

    I have a strange one for you. I am working on a data base for the local food bank. One form records the items that a client has taken in the last 7 days ( because clients are not suppose to come back every day) in a list box. I added a check box to the form to allow a listing in the list box of all instances that a client has received something(without any date restriction).

    Opened by the form on load::

    Private Sub Form_Open(Cancel As Integer)
    Dim aCount As Integer
    Dim anSQL As String

    'anSQL = "select ID, adate, sac, col, couche, bebe, pannage, owing, comments from distribution where menage = "
    'anSQL = anSQL & Me.OpenArgs & " AND adate > #" & Now() - 5 & "# order by adate desc

    aCount = Nz(DCount("id", "distribution", " menage = " & Me.OpenArgs & " AND adate > #" & Now() - 6 & "#"), 0)

    If aCount = 0 Then
    txtLast_Visit.Value = "AUCUNE DOSSIER!"
    lstVisits.Visible = False
    txtLast_Visit.Visible = True
    Else


    anSQL = "SELECT distribution.ID, format([adate],'dd mmm') as aDt, IIf([sac]=-1,'X',' ') AS Distr, IIf([col]=-1,'X','') AS aCol, IIf([couche]=-1,'X','') AS aCouche, "
    anSQL = anSQL & " IIf([Bebe]=-1,'X','') AS aBebe, iif([amonthlybag] = -1, 'X', '') as SacDeMois, IIf([pannage]=-1,'X', '') AS aPanage, "
    anSQL = anSQL & " distribution.comments, distribution.Menage, distribution.aDate "
    anSQL = anSQL & "FROM distribution WHERE menage = " & Me.OpenArgs & " AND adate > #" & Now() - 7 & "# order by adate desc "

    lstVisits.RowSource = anSQL

    End If
    End Sub

    When I use the check box to see all:

    Private Sub chkAllVisits_Click()
    If chkAllVisits.Value = -1 Then



    anSQL = "SELECT distribution.ID, format([adate],'dd mmm') as aDt, IIf([sac]=-1,'X',' ') AS Distr, IIf([col]=-1,'X','') AS aCol, IIf([couche]=-1,'X','') AS aCouche, "
    anSQL = anSQL & " IIf([Bebe]=-1,'X','') AS aBebe, iif([amonthlybag] = -1, 'X', '') as SacDeMois, IIf([pannage]=-1,'X', '') AS aPanage, "
    anSQL = anSQL & " distribution.comments, distribution.Menage, distribution.aDate "
    anSQL = anSQL & "FROM distribution WHERE menage = " & Me.OpenArgs & " order by adate desc "

    lstVisits.RowSource = anSQL
    Me.Refresh
    End If

    End Sub

    When I click the "chkallvisits" check box, I see nothing UNLESS there is an entry in the last 7 days. If there is such an entry, I will see all past visits. I have tried refresh, requery. No difference. This seems to be very strange behavior.

    Any ideas?

    John S
    Aylmer, Quebec

  2. Best Answer
    Posted by ranman256

    "Date math is not done that way: (adate > #" & Now() - 7)
    you use DateAdd(), or DateDiff() function
    7 days would be:

    between DateAdd("d",-7,Date()) and date()"


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    470
    Provided Answers: 22
    Date math is not done that way: (adate > #" & Now() - 7)
    you use DateAdd(), or DateDiff() function
    7 days would be:

    between DateAdd("d",-7,Date()) and date()

  4. #3
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    139
    Thanks for the response. I'm not sure that this will solve my problem, but it addresses another problem: date calculations being dependent on the Windows local date formats. i had a problem because one machine was set up for "France" as opposed to "Canadien - Francais". The approach I am using (i.e. (adate > #" & Now() - 7)# )seems to work BUT it may be dependent on translating a date from a string (which would bring into play the localization date formats). I will change the date functions as you suggest.

    PROBLEM SOLVED:

    I solved the problem by adding, in the chkAllvisits_click function after lstVisits.Rowsource = anSQL:

    lstVisits.visible = true

    I think this problem has to do with the logic of the form opening that dictates that lstVisits.visible is "false" if no transaction has occurred in the last 7 days.

    Thanks again for the reply (it inspired me) , and I think I will change over to the proper date functions to avoid localization problems.

    Regards

    John S
    Aylmer, QC

Posting Permissions

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