Results 1 to 5 of 5
  1. #1
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    44

    How to adapt VBA codes to make a search in a text box in the form for integer charachters

    Hello everyone,



    I have some areas in my forms which is searched by users. I have written two different codes which are working, when the searched fields become string like below. But they are not worked for integer values.

    ==> First coding is below worked for string fields.

    Private Sub searchbutton_Click()
    On Error GoTo Err searchbutton_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Cableform"
    stLinkCriteria = "[txtline]=" & "'" & Me![linesearchtxtbox] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_searchbutton_Click:
    Exit Sub

    Err_searchbutton_Click:
    MsgBox Err.Description
    Resume Exit_searchbutton_Click

    End Sub



    ==> Second coding is below worked for string fields.

    Private Sub searchbutton_Click()
    If linesearchtxtbox <> "" Then
    cabledataformquery = "SELECT * FROM tblcabledata WHERE line ='" & linesearchtxtbox & "'" & ""
    Me.RecordSource = cabledataform
    Me.Refresh

    Else
    MsgBox "Please enter line number first to search"
    Me.linesearchtxtbox .SetFocus
    End If
    End Sub


    So the question first is, for the first and second coding. How should I adapt the both code structure, if the searched field is integer instead of string.

    The second question, I would like to ask if there is any difference regarding searching speed of both coding? I also welcome to another coding to search offer with same way in the forms.

  2. #2
    Welshgasman is online now Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    621
    I would use the IsNumeric() function to determine if it is numeric or not, then construct the sql accordingly.
    However you should know what is numeric and what is not, as you have to search that particular field?
    Please, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  3. #3
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    44
    Hello,
    I did not get your answer. Because I know already if the field is numeric or not. I just need to understand how to adapt my existing code that currently works for string charachters

  4. #4
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    506
    Numeric values doesn't need single quotes.

    Try:
    Code:
    cabledataformquery = "SELECT * FROM tblcabledata WHERE line =" & linesearchtxtbox

  5. #5
    Welshgasman is online now Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    621
    Quote Originally Posted by DTO. View Post
    Hello,
    I did not get your answer. Because I know already if the field is numeric or not. I just need to understand how to adapt my existing code that currently works for string charachters
    You are searching on a text field?, so the sql would be like "textfield = 'DTO'"
    Your are searching on a numeric field, so the sql would be like "numberfield = 9"

    If you were to swap the syntax around, it would not work, as you need to compare like for like.?

    So if the field is text, you add quotes, if it is number you do not, and is a date then use #
    The fact that you know what field you are searching, indicates what syntax you should use for that field?
    Please, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



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

Similar Threads

  1. ORDER BY Sorting Integer Field as Text
    By Kluaoha in forum Queries
    Replies: 3
    Last Post: 08-21-2017, 11:01 AM
  2. how much text in long integer field?
    By accessmatt in forum Database Design
    Replies: 10
    Last Post: 12-22-2014, 03:54 PM
  3. Replies: 3
    Last Post: 09-02-2013, 04:33 PM
  4. Adding column as INTEGER makes it a long integer?
    By luckycharms in forum Programming
    Replies: 2
    Last Post: 10-20-2010, 02:47 PM
  5. Replies: 3
    Last Post: 04-12-2009, 05:11 PM

Tags for this Thread

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 - Senior Forums