Results 1 to 11 of 11
  1. #1
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    Syntax problem converting Access Query Builder SQL to VBA


    Access 2007, Sql Server 2008 r2.

    This query functions as a validation rule. I have created the query in Access and it works perfectly. I want to convert it to a SQL Statement to run in VBA.

    The end user enters a Page Number for a piece of real property. The Page number corresponds to a county map of properties. For example if you enter the Page no = 450 the query checks to see if the page number entered falls within a range of page numbers lets say 427-513. The query will only return one record and will indicate "In Range" or "Not In Range." If the page entered by the end user is "Not in range" the data is rejected. I have two possible solutions. Based on the results the end user will receive a message box indicating that the results are not valid and to try again.

    1. I could convert the Access query to a SQL statement and run it in VBA. I've worked on it for hours and can't get it.

    2. Run the Access query from VBA. I need to return all the results from the query to VBA and store them in variables.

    Here is the Access Query Builder statement which works perfectly.


    SELECT dbo_VolumeLookup.Township, [Forms]![FrmNewProp]![PageNO] AS PGEntered, dbo_VolumeLookup.VolStart, dbo_VolumeLookup.VolEnd, IIf([PGEntered] Between [VolStart] And [VolEnd],"In Range","Not In Range") AS Range
    FROM dbo_VolumeLookup
    WHERE (((dbo_VolumeLookup.Township)=[Forms]![FrmNewProp]![CBOSelTown]));


    All the example on the web deal with situations far more complex then this. I have only returned very simple queries to VBA.

    All help is appreciated.

    Fred

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't 'run' a SELECT query - only SQL actions (UPDATE, INSERT, DELETE) are 'run'. You can open an Access SELECT query object or open a recordset in VBA.

    Why do you need to set variables? What are you really trying to accomplish?
    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
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    My customer would like the end user notified when the Page number they are entering is outside of the Page range supplied by the County. The county data comes with the Page range as a concatenated text field "042-050". I have split the numbers out, converted them to integers, eliminating the leading zeros. This way a true range can be established numerically and the "Between" function will work. The reason I am doing this is that the customer has requested it. The customer wants the end user notified that the data they are trying to enter is incorrect. 2. They want the Correct Range they should be entering for a property displayed to the end user so they can resolve why they are entering the wrong data and 3. To reject the bad data and take them back to the field. They would like to see this working by Wednesday morning.


    The reason I am placing them in variables is so that I can display them to the end user, concatenated together in the way they are used to seeing it. Also I am much more comfortable working in VBA with the message boxes as there will be several working along with this code. The row sets and cascading combo boxes you helped me with earlier had a select statement in them so that confused me and I pushed to try and get it. Now I remember no select queries in VBA. So I need to run the query from VBA which I know how to do. I have never returned all of the fields returned by a query into individual variables. All the examples I looked at today were far more complex.

    If you see an easier way to do this I would appreciate knowing.

    This is what I am really trying to accomplish. I hope this is enough explanation but I don't know what else to provide.

    Thanks for all your help.

    Fred

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    One way is to limit the user input with restricted combobox. If it isn't in the dropdown list then they can't enter it into the combobox. Also, ValidationRule in textbox. These properties can be dynamically modified with code.

    If the allowable range is dynamic, what determines the valid range?
    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
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    Hi June:

    Sorry for the delay. I met with the client yesterday and they want the function as they stated it. For example:

    TownshipName = Fullerton
    Township page range = 203-256 (These are the huge books at the County office that have all the Properties on pages of a giant map book)
    The end user enters 207
    This is acceptable, and is a valid entry within the County range.

    I pointed out to them that the property might really be on page 210 and not on 207. Consequently the Page entered is Acceptable but not Valid, if I have your language above correct.

    So according to the Page range 203-256 the data entry is accepted.

    But according to reality the data is incorrect as they have the wrong page number in an acceptable range.

    So I go back to my Access query above which functions fine.

    I can launch this query from VBA. Can you show me how to capture the fields returned from the query above and place them in variables?

    Thanks

    Fred

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Review http://allenbrowne.com/ser-29.html

    Example of opening a reccordset object and pulling values.

    Dim rs AS DAO.Recordset
    Dim var1, var2, var3
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tableORqueryname WHERE ID=somevalue;")
    var1 = rs!field1
    var2 = rs!field2
    var3 = rs!field3

    Alternative to opening a recordset is domain aggregate functions.

    var1 = DLookup("field1", "tableORqueryname", "ID=somevalue")
    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.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I don't know your process for validation and I'm not sure of the data types for "Township", "VolStart" And "VolEnd". I am guessing they are all numbers (Long).

    So I modified your SQL to be executed in VBA.
    (warning: untested code)
    Code:
    Function IsValidPage(pPageNO As Long, pTownship As Long) As String
        Dim rst As DAO.Recordset
        Dim sSQL As String
    
        'set default return value
        IsValidPage = "In Range"
    
        sSQL = "SELECT dbo_VolumeLookup.Township, dbo_VolumeLookup.VolStart, dbo_VolumeLookup.VolEnd, "
        sSQL = sSQL & " FROM dbo_VolumeLookup"
        sSQL = sSQL & " WHERE dbo_VolumeLookup.Township = " & pTownship
        sSQL = sSQL & " AND " & pPageNO & " Between [VolStart] And [VolEnd];"
        '    Debug.Print sSQL
        Set rst = CurrentDb.OpenRecordset(sSQL)
    
        If rst.BOF And rst.EOF Then
            'no records found
            IsValidPage = "Not In Range"
        End If
    
        rst.Close
        Set rst = Nothing
    
    End Function
    In an unbound text box, you would use
    Code:
    =IsValidPage(Me.PageNO, Me.CBOSelTown)

  8. #8
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    Thank you both for your responses. I appreciate the help.

    I will need to go with June's response because I have no experience whatsoever with Functions. However I have saved your code so I can pull it apart as a learning experience.

    Thanks all. I would like to leave this open for a day or two more in case I have trouble.

    With appreciation, Fred

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Custom functions are not really difficult. They are just code procedures. The difference from a Sub is that a function returns a value to the entity that calls it. Same as an intrinsic function which you probably use all the time. Date() is an intrinsic function, when called it returns the current date.

    The function written by ssanfu will return one of two string values "In Range" or "Not In Range". Alternatively, it could be structured to return a Boolean (True or False).

    Calling the function would be like:

    If IsValidPage(Me.tbxPage, Me.tbxTown) = "In Range" Then
    'do this
    Else
    'do this
    End If
    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.

  10. #10
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    Having trouble with end of statement.

    I am struggling with the SELECT statement. Here is what I have so far. The Township Name is a string, Volume Begin and End are both integers. If I understand this the Select statement returns:

    Field1=Township string
    Field2=VolStart integer
    Field3=VolEnd integer

    Then after you get the three fields from the query you transfer them to Variables

    Var1= rs!Field1 = Township Name
    Var2=rs!Field2 = VolStart Integer
    Var3=rs!Field3 = VolEnd Integer

    Code:
    Private Sub Volume_AfterUpdate()
    
    Dim stTownshipNM As String   'comes from the form.
    Dim intPageNO As Integer        'comes from the form and I manipulate it after the results return from the query.
    
    stTownshipNM = Me.CBOSelTown.Column(0) Gives me the Township name from the form.
    intPageNo = Me.Volume Gives me the page number the end user entered.
    
    Dim rs As DAO.Recordset
    Dim var1, var2, var3
    
    Set rs = CurrentDb.OpenRecordset("SELECT * Field1, Field2, Field3 FROM VolRange_Qry WHERE Field1 = '" & stTownshipNM & "' )" ;"
    
    var1 = rs!field1 'Township string
    var2 = rs!Field2 'Start Page Range integer
    var3 = rs!field3 'End Page Range integer
    
    End Sub
    The end of the SELECT is where I am getting hung up. I don't understand how to end the statement. Field1 should = stTownshipNM entered by the end user.

    Does the name of the query VolRange_Qry need to be in quotes? What tells the statement that this is a query?

    I have so many questions about the Function but I don't want to mix them into this. Can you help me end this statement correctly?

    Thanks

    Fred

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Have you run Debug > Compile? It should error on the lines that set stTownshipNM and intPageNo variables because the commentary text is not delimited with apostrophe.

    Your field names are actually Field1, Field2, Field3?

    The SELECT should end with: & "';")
    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. Replies: 1
    Last Post: 07-26-2014, 01:05 PM
  2. Replies: 3
    Last Post: 05-14-2014, 06:07 AM
  3. Replies: 7
    Last Post: 06-28-2013, 12:15 PM
  4. Expression Builder Syntax Problem
    By maxmaggot in forum Queries
    Replies: 7
    Last Post: 06-14-2013, 08:29 AM
  5. Expression Builder Problem
    By benthamq in forum Forms
    Replies: 3
    Last Post: 10-07-2011, 01:02 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