Results 1 to 3 of 3
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Snytax error

    Hi guy's i am getting a runtime error 3075 on this code and can't see why ?? Syntax error missing operator



    Highlighted red is the error and whatever i need to change, i guess i would need to change the lines below

    Thank you


    Code:
    Dim InvDate As Date, DateNow As Date, mStart As Date, mEnd As Date, lkStart As Date, lkEnd As Date
    Dim mTotal As Currency, PaidNo As Currency, PaidYes As Currency
    
    Cust = Me.cboList1Options.Column(0)CAdd1 = Me.cboList1Options.Column(1)
    CAdd2 = Me.cboList1Options.Column(2)
    CTown = Me.cboList1Options.Column(3)
    cPostcode = Me.cboList1Options.Column(4)
    If IsNull(lkStart = DMin("InvoiceDate", "tblInvoices", "[Customer] = '" & Cust & "'")) Then
    lkStart = ""
    Else
    lkStart = Format(DMin("InvoiceDate", "tblInvoices", "[Customer] = '" & Cust & "'"), "dd-mmm-yyyy")
    End If
    If IsNull(lkEnd = DMax("InvoiceDate", "tblInvoices", "[Customer] = '" & Cust & "'")) Then
    lkEnd = ""
    Else
    lkEnd = Format(DMax("InvoiceDate", "tblInvoices", "[Customer] = '" & Cust & "'"), "dd-mmm-yyyy")
    End If
    mStart = InputBox("Enter Start Date ?" & Chr(10) & Chr(10) & _
    "The Default Date Is The Earliest Date On The System For," & Chr(10) & Chr(10) & _
    Me.cboList1Options, "ENTER START DATE", lkStart)
    mEnd = InputBox("Enter End Date ?" & Chr(10) & Chr(10) & _
    "The Default Date Is The Latest Date On The System For," & Chr(10) & Chr(10) & _
    Me.cboList1Options, "ENTER END DATE", lkEnd)
    
    
    If Not IsNull(PaidYes = DSum("Price", "tblInvoices", "[Customer] = '" & Cust & "'" & " And [InvoiceDate] Between #" & mStart & "# And #" & mEnd & "#" & " [Paid] = Yes")) Then
    PaidYes = DSum("Price", "tblInvoices", "[Customer] = '" & Cust & "'" & " And [InvoiceDate] Between #" & mStart & "# And #" & mEnd & "#" & " [Paid] = Yes")
    Else
    PaidYes = ""
    End If
    If Not IsNull(PaidNo = DSum("Price", "tblInvoices", "[Customer] = '" & Cust & "'" & " And [InvoiceDate] Between #" & mStart & "# And #" & mEnd & "#" & " [Paid] = No")) Then
    PaidNo = DSum("Price", "tblInvoices", "[Customer] = '" & Cust & "'" & " And [InvoiceDate] Between #" & mStart & "# And #" & mEnd & "#" & " [Paid] = No")
    Else
    PaidNo = ""
    End If
    mTotal = DSum("Price", "tblInvoices", "[Customer] = '" & Cust & "'" & " And [InvoiceDate] Between #" & mStart & "# And #" & mEnd & "#")

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    By using a Dsum you will always get either the result you want or 0 so no need to perform the DSum twice. Checking for null won't work.

    Code:
    PaidYes = DSum("Price", "tblInvoices", "[Customer] = '" & Cust & "' And [InvoiceDate] Between #" & mStart & "# And #" & mEnd & "# AND [Paid] = Yes")
    If PaidSum <> 0 then .... do what ever you where going to do.
    Also PaidYes and PaidNo are set to currency data types so trying to set them to "" will probably fail.
    Finally they are mutually exclusive it's either paid or it's not, so you don't really need both ?
    Last edited by Minty; 02-13-2019 at 05:01 AM. Reason: Typo's
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The error is probably in the WHERE part of the DSum, and a couple of things might be causing the error.

    First, either mStart or mEnd might be Null.

    But is is more likely to be [Paid] = Yes. You can't use "Yes" in that context - you have to use [Paid] = True.

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

Similar Threads

  1. simple snytax (lookup most recent record)
    By markjkubicki in forum Programming
    Replies: 9
    Last Post: 06-20-2017, 02:50 PM
  2. Query Field Name Snytax Error
    By Vol71 in forum Queries
    Replies: 2
    Last Post: 07-20-2015, 01:28 PM
  3. Replies: 3
    Last Post: 01-23-2014, 07:49 AM
  4. Replies: 0
    Last Post: 07-16-2012, 05:42 AM
  5. Snytax Error - Counting a Not Null field
    By Huddle in forum Access
    Replies: 2
    Last Post: 06-29-2012, 12:30 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