Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opening form with whereocndition (2000)

    Having a little trouble with this form

    I have two unbound fields on the form, one call reqdate which is a shortdate field and one called salesman which is a combo box linked to the tblsalesman table, bound to the first field (autonumber) oly to display field two

    I then have a command button with the following code:

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim varsalesid As Variant
    Dim strlogon As String

    'Find logon information
    strlogon = Environ("Username")
    'varsalesid = DLookup("[SALESID]", "tblsalesman", "Winlogon=" & Chr(34) & strlogon & Chr(34))
    varsalesid = Me.Salesman
    varDate = Me.ReqDate

    stDocName = "frmViewingsPurch"
    DoCmd.OpenForm stDocName, , , wherecondition:="ViewingsQuery.CreatDate=" & varDate & " And ViewingsQuery.SalesmanID=" & varsalesid

    The form opens but no filter criteria is set even though I set it to date 21/03/06 and salesman is 7 which looking at the raw table records do exist.

    If I look at the filter criteria once the form opens i get

    CreatDate=21/03/2006 And SalesmanID=7

    If I delete this and open the form manually and manually enter the filter criteria the string is:

    ((ViewingsQuery.CREATDATE=#3/21/2006#) AND (ViewingsQuery.SALESMANID=7))

    Any ideas, or do you need a cut down version of the db?
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening form with whereocndition (2000)

    The date must be converted to US date format (mm/dd/yyyy) and it must be enclosed in # characters. So use

    ..., WhereCondition:="ViewingsQuery.CreatDate=#" & Format(varDate, "mm/dd/yyyy") & "# And ViewingsQuery.SalesmanID=" & varsalesid

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening form with whereocndition (2000)

    How would i add another condition on that the FBDate field is blank (null)?
    Best Regards,

    Luke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening form with whereocndition (2000)

    ... & varsalesid & " AND FBDate Is Null"

    You don't need # characters here since you are not comparing FBDate to a specific date.

Posting Permissions

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