Results 1 to 5 of 5

Thread: syntex (97 sr2)

  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    syntex (97 sr2)

    Edited by HansV to correct problem with field name caused by Lounge software

    Good Morning,
    Can you give me the correct syntex to combine these two statements into one sql statement. Everytime I try to add an and statement I get an error.
    Account and startdate at both variables that are assigned earlier in the module. NFSTradeRevenue is a table that I have defined it as a recordset, should I be using that instead?

    strSQL = "SELECT count(*) as RecCount from [NFSTradeRevenue] where [accountNo] = " & Chr(34) & account & Chr(34)
    strSQL = "SELECT count(*) as RecCount from [NFSTradeRevenue] where <!t>[tradedate] >= #" & Format(startdate, "mm/dd/yy") & "#"

    Thanks in advance
    Cathy

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

    Re: syntex (97 sr2)

    Since you're returning counts for two different conditions, you cannot simply combine them into one SQL statement. You could use this:

    Dim lngCount1 As Long
    Dim lngCount2 As Long

    lngCount1 = DCount("*", "NFSTradeRevenue", "accountNo = " & Chr(34) & account & Chr(34))
    lngCount2 = DCount("*", "NFSTradeRevenue", "tradedate >= #" & Format(startdate, "mm/dd/yy") & "#")

    Does that help?

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntex (97 sr2)

    No because there can be multiple records with the same account number and multiple records with the same trade date. I only want to know if the account number and trade date are on a record. LngCount1 could be >0 but may or may not have a match on the trade date. I would have no way to ID it unless I can check each condition.

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

    Re: syntex (97 sr2)

    If you want to combine the conditions, use this:
    <code>
    strSQL = "SELECT count(*) as RecCount from NFSTradeRevenue] where accountNo = " & _
    Chr(34) & account & Chr(34) & " AND tradedate >= #" & Format(startdate, "mm/dd/yy") & "#"</code>

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntex (97 sr2)

    That's it. Thanks so very much, 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
  •