Results 1 to 14 of 14
  1. #1
    Budatlitho is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Mar 2016
    Posts
    36

    User input to WHERE

    Hello!



    Here's my SQL query:
    Dim DivisionInputBox as string
    DivisionInputBox = InputBox("DivisionInputBox", "Division input", "Enter 2 digit division:")

    strSql = "SELECT Right$([Divisions]![Unit],2) AS [No], [2ColumnTags]![DivisionName] & [Office] & ""<0x0009>"" & [MemberName] AS OfficeAndName, [2ColumnTags]![DivisionAddress] & [ADD1] AS Street_1, [9CRRoster].ADD2 AS Street_2, [CITY] & "", "" & [STATE] & "" "" & [ZIPCODE] AS [City-ST-ZIP], [9CRRoster].HOME_NM, [9CRRoster].WORK_NM, [9CRRoster].FAX_NM, [9CRRoster].CELL_NM, [2ColumnTags]![PMTag] AS Expr2 " & vbCrLf & _
    "FROM 2ColumnTags, Divisions INNER JOIN 9CRRoster ON Divisions.MemberNumber = [9CRRoster].EMP_ID " & vbCrLf & _
    "WHERE (((Right$([Divisions]![Unit],2))= @DivisionInputBox)) " & vbCrLf & _
    "ORDER BY Divisions.ID;"

    Running gives error
    Obviously, @DivisionInputBox throws the error. How should that variable be referenced here?

    TIA

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    dont use code.
    make a query and put the inputbox in the critia in brackets as:

    [Enter 2 digit Division#]

    queries dont get syntax wrong.

  3. #3
    Budatlitho is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Mar 2016
    Posts
    36
    renman:

    Needs to be in code, as I'm creating output to import into InDesign. Exporting from a Report using a query introduces blank lines that even if controls are set to can shrink still doesn't eliminate them, The complete function follows:
    ... and yes, I'm pretty sure this isn't nearly as concise or elegant code (I am listed as a beginner after all!).

    Public Function exportDivision() 'revised 01-07-2021 WORKS except for allowing user input fo division number
    Dim db As Database
    Dim rs As Recordset
    Dim rs1 As Recordset
    Dim a As Variant
    Dim fso As Variant
    Dim ASCII As String
    Dim GetDBPath As String
    Dim strSql As String
    Dim Unit As String
    Dim Expr1 As String
    Dim ID As String
    Dim Office As String
    Dim MemberName As String
    Dim OfficeAndName As String
    Dim LAST_NM As String
    Dim Street_1 As String
    Dim Street_2 As String
    Dim City_ST_ZIP As String
    Dim EMAIL As String
    Dim HOME_NM As String
    Dim WORK_NM As String
    Dim FAX_NM As String
    Dim CELL_NM As String
    Dim UnitNo As Integer
    Dim ExportFileName As String
    Dim DivisionInputBox As String
    DivisionInputBox = InputBox("DivisionInputBox", "Division input", "Enter 2 digit division:")
    ASCII = "<ASCII-WIN>"
    GetDBPath = CurrentProject.Path & "\ToIndesign" & "Division" & DivisionInputBox & "_to_Indesign"
    ExportFileName = GetDBPath & ".txt"
    'Debug.Print ExportFileName
    'Call CreateNewFile(ExportFileName)
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Divisions")
    strSql = "SELECT [2ColumnTags]![DivisionName] & [Office] & ""<0x0009>"" & [MemberName] AS OfficeAndName, [2ColumnTags]![DivisionAddress] & [ADD1] AS Street_1, [9CRRoster].ADD2 AS Street_2, [CITY] & "", "" & [STATE] & "" "" & [ZIPCODE] AS [City-ST-ZIP], [9CRRoster].EMAIL, [9CRRoster].HOME_NM, [9CRRoster].WORK_NM, [9CRRoster].FAX_NM, [9CRRoster].CELL_NM, [2ColumnTags]![PMTag] AS Expr2 " & vbCrLf & _
    "FROM 2ColumnTags, Divisions INNER JOIN 9CRRoster ON Divisions.MemberNumber = [9CRRoster].EMP_ID " & vbCrLf & _
    "WHERE (((Right$([Divisions]![Unit], 2)) = @DivisionInputBox)) " & vbCrLf & _
    "ORDER BY Divisions.ID;"
    'Debug.Print strSql
    Set rs1 = db.OpenRecordset(strSql)
    rs1.MoveFirst
    ' print column names
    Dim i As Integer
    'Debug.Print rs1.Fields.Count
    'For i = 0 To rs1.Fields.Count - 2
    ' Debug.Print rs(i).Name; ' & vTab print col names separated with a tab one from each other
    'Next i
    'Debug.Print rs1(rs.Fields.Count - 1).Name 'last one without ; so it adds the newline
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set a = fso.CreateTextFile(ExportFileName, True)
    a.WriteLine ASCII
    Do While Not rs1.EOF
    For i = 0 To rs1.Fields.Count - 2
    If rs1(i) > "" Then
    'Debug.Print rs1(i) & vbCr; 'print values separated with a carriage return from
    a.WriteLine rs1(i)
    End If
    Next i
    rs1.MoveNext
    Loop
    a.Close
    rs.Close 'Close the recordset
    Set rs = Nothing 'Clean up

    End Function

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You need to concatenate the value of DivisionInputBox into the query string

    Try
    Code:
    "WHERE (((Right$([Divisions]![Unit], 2)) = " & DivisionInputBox & "))  " & vbCrLf & _
    HTH
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Budatlitho is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Mar 2016
    Posts
    36
    Thanks! Works like a charm.
    Bud :-)

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've looked at the code and have some suggestions.

    You should not begin an object name with a number. (2ColumnTags, 9CRRoster)
    Do not use spaces, special characters or punctuation (exception is the underscore) in object names (City-ST-ZIP / better would be City_ST_ZIP)
    '~~~~~~~~~~~~~~~~~

    You do not check to see if "DivisionInputBox" has two numbers. What happens if someone enters "ZZ" instead of numbers?

    I changed Public Function exportDivision() to Public Sub ExportDivision() and moved the code to a standard Module. I would have a form with a text box to enter the division and button that calls Public Sub ExportDivision()
    Code:
    Private Sub btnTest_Click()
        ' if the entry is a number then 
             Call ExportDivision
        'else
        '   exit sub
        'end if
    End Sub
    '~~~~~~~~~~~~~~~~~

    This
    Code:
    GetDBPath = CurrentProject.Path & "\ToIndesign" & "Division" & DivisionInputBox & "_to_Indesign"
    ExportFileName = GetDBPath & ".txt"
    can be replaced with
    Code:
    ExportFileName = CurrentProject.Path & "\ToIndesignDivision" & DivisionInputBox & "_to_Indesign.txt"
    '~~~~~~~~~~~~~~~~~

    Why do you have "[2ColumnTags]![DivisionName]" in the SQL string? (several places). You should use the DOT (.) instead of the BANG (!)
    '~~~~~~~~~~~~~~~~~

    The FROM clause worries me. You have
    Code:
    FROM 2ColumnTags, Divisions INNER JOIN 9CRRoster ON Divisions.MemberNumber = [9CRRoster].EMP_ID
    You have an inner join "Divisions INNER JOIN 9CRRoster ON Divisions.MemberNumber = [9CRRoster].EMP_ID" then a CARTESIAN JOIN with table "2ColumnTags".
    '~~~~~~~~~~~~~~~~~

    I made some changes to the code (in BLUE)
    Code:
    Public Sub ExportDivision()
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim rs1 As DAO.Recordset
        Dim DivisionInputBox As String
        Dim ASCII As String
        Dim GetDBPath As String
        Dim ExportFileName As String
        Dim strSql As String
        Dim fso As Variant
        Dim a As Variant
        Dim i As Integer
        
    '===================================================================
    'These variables are not used in the code and can be deleted
    '    Dim Unit As String
    '    Dim Expr1 As String
    '    Dim ID As String
    '    Dim Office As String
    '    Dim MemberName As String
    '    Dim OfficeAndName As String
    '    Dim LAST_NM As String
    '    Dim Street_1 As String
    '    Dim Street_2 As String
    '    Dim City_ST_ZIP As String
    '    Dim EMAIL As String
    '    Dim HOME_NM As String
    '    Dim WORK_NM As String
    '    Dim FAX_NM As String
    '    Dim CELL_NM As String
    '    Dim UnitNo As Integer
    '===================================================================
        
        Set db = CurrentDb
        
        DivisionInputBox = InputBox("DivisionInputBox", "Division input", "Enter 2 digit division:")
        ASCII = "<ASCII-WIN>"
        
        '    GetDBPath = CurrentProject.Path & "\ToIndesign" & "Division" & DivisionInputBox & "_to_Indesign"
        '    ExportFileName = GetDBPath & ".txt"
        'the following line can used this instead of the two lines above
        ExportFileName = CurrentProject.Path & "\ToIndesignDivision" & DivisionInputBox & "_to_Indesign.txt"
        'Debug.Print ExportFileName
        
        'Call CreateNewFile(ExportFileName)
        Set rs = db.OpenRecordset("Divisions")
          
        strSql = "SELECT [2ColumnTags].[DivisionName] & [Office] & ""<0x0009>"" & [MemberName] AS OfficeAndName,"
        strSql = strSql & " [2ColumnTags].[DivisionAddress] & [ADD1] AS Street_1, [9CRRoster].ADD2 AS Street_2,"
        strSql = strSql & " [2ColumnTags].[PMTag] AS Expr2, [CITY] & "", "" & [STATE] & "" "" & [ZIPCODE] AS [City-ST-ZIP], [9CRRoster].EMAIL,"
        strSql = strSql & " [9CRRoster].HOME_NM, [9CRRoster].WORK_NM, [9CRRoster].FAX_NM, [9CRRoster].CELL_NM"
        strSql = strSql & " FROM 2ColumnTags, Divisions INNER JOIN 9CRRoster ON Divisions.MemberNumber = [9CRRoster].EMP_ID"
        strSql = strSql & " WHERE Right([Divisions]![Unit], 2)) = " & DivisionInputBox
        strSql = strSql & " ORDER BY Divisions.ID;"
        'Debug.Print strSql
             
        Set rs1 = db.OpenRecordset(strSql)
    
        If Not rs1.BOF And Not rs1.EOF Then
            rs1.MoveLast
            rs1.MoveFirst
            ' print column names
            'Debug.Print rs1.Fields.Count
            'For i = 0 To rs1.Fields.Count - 2
                ' Debug.Print rs(i).Name; ' & vTab print col names separated with a tab one from each other
            'Next i
            'Debug.Print rs1(rs.Fields.Count - 1).Name 'last one without ; so it adds the newline
            Set fso = CreateObject("Scripting.FileSystemObject")
            Set a = fso.CreateTextFile(ExportFileName, True)
            a.WriteLine ASCII
            Do While Not rs1.EOF
                For i = 0 To rs1.Fields.Count - 2
                    If rs1(i) > "" Then
                        'Debug.Print rs1(i) & vbCr; 'print values separated with a carriage return from
                        a.WriteLine rs1(i)
                    End If
                Next i
                rs1.MoveNext
            Loop
        End If
        
        'Clean up
        On Error Resume Next
        a.Close  'Close the recordset
        rs.Close 'Close the recordset
        rs1.Close 'Close the recordset
        
        Set fso = Nothing
        Set rs = Nothing
        Set rs1 = Nothing
        Set db = Nothing
    End Sub


    Good luck with your project......

  7. #7
    Budatlitho is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Mar 2016
    Posts
    36
    Steve:
    For about twenty years, I have been producing a directory for the U.S. Coast Guard Auxiliary, 9th Central Region using, first, PageMaker and then InDesign. Getting input started with hand-written FAXes, prone to a myriad of errors. I had (and still have) access to our roster which has a unique member number. I decided to create a PDF that users can submit the member name, the name as they'd like it in the directory, and the office they hold. That submission is directed to a CGI script that creates a CSV file that is then imported into Access, where that content is matched to the roster, resulting in accurate member details, address, phone, etc. Access then provides an export that contains the complete member entry, formatted with paragraph styles included, that is imported as tagged text into InDesign.
    Until now, the export of the report from Access contained many extra carriage returns that then had to be removed in InDesign. Probably the most irritating step in this process is that InDesign requires the tag <ASCII-WIN> as the first line in order for the file to be recognized by InDesign as a tagged-text file. So, each export had to be opened with a text editor to get the beginning blank line removed and saving. Only then can the file be imported into InDesign. I could not get rid of the extra linefeeds no matter what I tried from the exported Access report.
    THANK YOU for your suggested changes; I learn a lot from those.
    I can supply you with a sample of the directory if you want, but cannot post it here because of the Privacy Act of 1974. PM me if you're interested. :-)
    Last edited by Budatlitho; 01-09-2021 at 06:30 AM.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First, Thank you for your service. I was raised in Juneau and there was a large (it seemed to me) contingent of USCG there.

    Next, I suggest you edit your last post and remove your phone number. I would send a private message (PM) for things like that.

    Is the export/import working the way you want/need?


    Lastly, my brother-in-law was stationed at Kodiak Island for around 10 years. Could be more, could be less - I wasn't really keeping track..
    Then he (the family) was transferred to the Pat Center in Topeka, Ks. That always gives me a laugh.... USCG in the center of the US...... He retired around 10(??) years ago..

    PS Where are you located? Time zone? I'm in Alaska, sooooo, I don't want to wake you up or interrupt meal time if you want to talk...

  9. #9
    Budatlitho is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2016
    Posts
    36
    Eastern time. Would be great to talk!

  10. #10
    Budatlitho is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2016
    Posts
    36
    Steve:

    statement threw an error....this line...

    strSql = strSql & " WHERE Right([Divisions]![Unit], 2)) = " & DivisionInputBox

    needs to be BLUE added, RED deleted

    strSql = strSql & " WHERE Right$([Divisions]![Unit], 2)) = " & DivisionInputBox

    Proud of myself for finding it! Am I smug or not???

    Bud

  11. #11
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would call it a good job of troubleshooting....

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry to burst your bubble slightly but Right was right although Right$ would work as well
    However you were right to remove the red bracket ...if you see what I mean
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Thanks Collin. Both issues were covered in a PM (my extra ")" goof up and the deprecated "$").

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    That's alRight!. I was just playing around getting Right into my answer as many times as possible
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Crosstab User Input
    By MSAccess_user in forum Queries
    Replies: 1
    Last Post: 10-24-2019, 12:39 PM
  2. Replies: 1
    Last Post: 02-16-2018, 03:38 AM
  3. using wildcards for user input
    By koncreat in forum Queries
    Replies: 4
    Last Post: 01-05-2017, 08:19 PM
  4. Replies: 1
    Last Post: 11-04-2014, 12:07 PM
  5. How to take a user input
    By iamraja5 in forum Access
    Replies: 1
    Last Post: 05-12-2011, 10:17 AM

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