Results 1 to 6 of 6
  1. #1
    rchtan is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    7

    Vba string

    Hi



    Fairly new to using vba in access. The coding was workng fine until i decided to add in a new form text field where user can input a date and return a query where it list all items on and before. The code in red are my new addition.


    strSQL = "SELECT qryprintlist.* FROM qryprintlist " & "WHERE qryprintlist.[item_Description] " & stritemStatus & strStatusCondition & "qryprintlist.[Category_Status] " & strcategoryStatus & strOrigCondition & "qryprintlist.[VendorID] " & strOriginator & strvendorCondition & "qryprintlist.[Area_ID] " & strAreaName & " AND " & "qryprintlist.[Date_Initiated] & " <= " & #" & txtDateTo.Value & "# ";"

    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,621
    I don't understand the query. All those concatenated fields and variables in the WHERE clause don't have a parameter. Some have space separators and some don't. The sql statement was incomplete before the change. I don't see how it was working. Too much concatenation in the new part and missing a quote mark, simplify.

    " AND qryprintlist.[Date_Initiated] <= #" & txtDateTo.Value & "#;"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rchtan is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    7
    Thanks June7 for your ealier reply. I went back and rework on it
    strSql = "qryprintlist.[item_Description] " & stritemStatus & strOrigCondition & "qryprintlist.[Category_Status] " & strCategory & strcatCondition & "qryprintlist.[VendorID] " & strvendorName & " AND qryprintlist.[itemDate] Between #" & Format(txtDateFrom.value, "dd-mmm-yyyy") & "#" & " AND #" & Format(txtDateTo.value, "dd-mmm-yyyy") & "#".
    How do i get null date into this extract? I tried adding OR WHERE "qryprintlist.[itemDate]" is null.
    Sorry my understand of VBA is really basic

    Thanks

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,621
    It isn't really VBA you are struggling with here. It is the syntax of SQL statements and how to build them in VBA. The SQL statement still doesn't make sense. Need some operators (=, <=, >=, <, >, <>) in there somewhere. Would VendorID = strVendorName?

    Parameters for text fields need apostrophe delimiters.

    Maybe:

    strSql = "qryprintlist.[item_Description] ='" & stritemStatus & strOrigCondition & "' AND qryprintlist.[Category_Status] ='" & strCategory & strcatCondition & "' qryprintlist.[VendorID] ='" & strvendorName & "' AND qryprintlist.[itemDate] Between #" & Me.txtDateFrom & "# AND #" & Me.txtDateTo & "# AND qryprintlist.[ItemDate] Is Null"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    rchtan is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    7
    Thanks for your quick reply. I will work on the statemet.
    Hope you dont mind if i have another question. I have created a union between 2 crosstab queries
    Below is how the result look like
    Area_ID ItemID Town1Cost Town2Cost Town1Qty Town2Qty
    -----------------------------------------------------------------
    6036 Sugar 50 60 0 0
    6036 Sugar 0 0 25 30
    6036 Salt 80 120 0 0
    6036 Salt 0 0 20 30

    What do i need in the sql query to get sum that i dont need another query to do that?

    Area_ID ItemID Town1Cost Town2Cost Town1Qty Town2Qty
    -----------------------------------------------------------------
    6036 Sugar 50 60 25 30
    6036 Salt 80 120 20 30

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,621
    Sorry, you need another query.

    Do a GROUP BY (totals) query with the UNION as the data source. Or use the UNION as the RecordSource for a report that uses Grouping & Sorting with summary calcs.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. String Query
    By Caysteve in forum Queries
    Replies: 7
    Last Post: 12-02-2011, 04:28 AM
  2. SQL + VBA String
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 08-22-2011, 08:44 AM
  3. String functions
    By posstrap in forum Access
    Replies: 1
    Last Post: 08-12-2011, 09:08 PM
  4. VBA string with OR expression
    By hmcquade in forum Forms
    Replies: 3
    Last Post: 06-24-2011, 07:59 AM
  5. String Matching
    By tuna in forum Programming
    Replies: 1
    Last Post: 05-16-2010, 12:22 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