Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    16

    Unanswered: Sending a variable from a form to a query?

    I have a form that has a date variable searchDate. How do I format it or call it from a query? I saw a similar process happen with a string Like "*" & [Form]![frmSearch]![searchString] & "*" but using that method (Like [Forms]![frmSearch]![searchDate]) doesn't end well (#Error).

    Any ideas?

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    How would you expect to find a match for a Date using Like and Wildcards?

    Code:
    Like "*" & [Form]![frmSearch]![searchString] & "*"
    would find 'barometer,'chrome' and romeo if [searchString] was 'rome.' How would you consider one date to be 'like' another date? Would 1/29/2012 be 'like' 9/2/2012?

    To do this kind of thing with dates is usually done using two dates as part of a date range.

    If you actually wanted to match 1/29/2012 and 1/29/2013, I suppose you could convert both date fields to Strings and go from there.

    Or you could break each date down into components, such as the month and day of the month, and compare them.

    What, exactly, in plain language, are you trying to do here?

    Linq ;0)>
    Last edited by Missinglinq; 01-29-13 at 23:07.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Dec 2012
    Posts
    16
    the Like and Wildcards were from a similar process that was searching for a string. I wanted to do the same thing (pass a variable from a form into a query) using a Date. I didn't use the wildcards in the actual attempt.

    As for what I wanted to do; I wanted to be able to access a variable in a form (searchDate) from a separate query to control a report's output.
    Last edited by RedFredHunter; 01-29-13 at 23:43.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The following function "tries" to compose a valid date in an appropriate format (mm/dd/yyyy) from a received string information that is supposed to contain full or partial date information. I'm not sure that it can be a solution to your problem, though.
    Code:
    Private Function MakeDate(ByVal DateCandidate As Variant, ParamArray Offset()) As Variant
    
        Dim varDateParts As Variant
        Dim i As Integer
    
        If Not IsNull(DateCandidate) Then
            DateCandidate = Replace(DateCandidate, "-", "/")
            DateCandidate = Replace(DateCandidate, " ", "/")
            DateCandidate = Replace(DateCandidate, ".", "/")
            If IsDate(DateCandidate) Then
                varDateParts = Format(DateCandidate, "dd/mm/yyyy")
            Else
                varDateParts = Split(DateCandidate, "/")
                For i = 0 To UBound(varDateParts)
                    If InStr("janfevfebmaravraprmaimayjunjulaouaugsepoctnovdec", varDateParts(i)) Then
                        varDateParts(i) = Nz(Switch( _
                            varDateParts(i) = "jan", 1, _
                            varDateParts(i) = "fev", 2, _
                            varDateParts(i) = "feb", 2, _
                            varDateParts(i) = "mar", 3, _
                            varDateParts(i) = "avr", 4, _
                            varDateParts(i) = "apr", 4, _
                            varDateParts(i) = "mai", 5, _
                            varDateParts(i) = "may", 5, _
                            varDateParts(i) = "jun", 6, _
                            varDateParts(i) = "jul", 7, _
                            varDateParts(i) = "aou", 8, _
                            varDateParts(i) = "aug", 8, _
                            varDateParts(i) = "sep", 9, _
                            varDateParts(i) = "oct", 10, _
                            varDateParts(i) = "nov", 11, _
                            varDateParts(i) = "dec", 12 _
                        ), varDateParts(i))
                    End If
                Next i
                If UBound(varDateParts) > 0 Then
                    If IsDate(varDateParts(0) & "/" & varDateParts(1)) Then
                        varDateParts = Format(varDateParts(0) & "/" & varDateParts(1), "dd/mm/yyyy")
                    End If
                End If
                If IsArray(varDateParts) Then
                    ReDim Preserve varDateParts(0 To 2)
                    If Eval(varDateParts(0) & " Between 0 AND 12") Then
                        If varDateParts(1) = "" Then varDateParts(1) = Year(Now)
                        varDateParts(2) = varDateParts(1)
                        varDateParts(1) = varDateParts(0)
                        varDateParts(0) = "1"
                        varDateParts = CDate(varDateParts(0) & "/" & varDateParts(1) & "/" & varDateParts(2))
                        If UBound(Offset) = 1 Then
                            If Eval("'" & Offset(0) & "' IN ('d', 'm', 'y')") Then
                                varDateParts = DateAdd(Offset(0), Offset(1), varDateParts)
                            ElseIf Offset(0) = "e" Then
                                varDateParts = DateAdd("m", 1, varDateParts)
                                varDateParts = DateAdd("d", -1, varDateParts)
                            End If
                        End If
                    End If
                End If
            End If
        End If
        If IsDate(varDateParts) Then
            MakeDate = Format(varDateParts, "dd/mm/yyyy")
        Else
            MakeDate = DateCandidate
        End If
    
    End Function
    Notes:

    a. As it was written, the function can work with English or French date information, hence the duplicates fragments "fev"-"feb", "mai"-"may", etc.

    b. The use of the second argument received by the function (Offset()) handles special cases that are very specific to the application where this function is used and that would be too long to explain here.

    c. In any cases, try to refrain from converting date fields into formated string values to perform the search (e.g. Format(Table.DateColumn, "mm/dd/yyyy") Like SearchPattern). Although feasible, this solution is terribly slow and un-efficient).

    d. Also refrain from storing date/time values in a Text column under a defined format.
    Have a nice day!

Posting Permissions

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