Results 1 to 3 of 3

Thread: Query Help

  1. #1
    Join Date
    May 2002
    Posts
    5

    Unanswered: Query Help

    Help Please,
    Here is the problem i have in my
    table i have a list of ticket
    numbers that start with 001 thru
    365 i need a prompt query that the
    user could enter the first 3 digits
    of the ticket. say if he or she
    entered 001 and 004 this would show
    only the tickets that begin with
    001 thru 004 each ticket number has
    8 digits in it.


    Philip

  2. #2
    Join Date
    Nov 2002
    Location
    The Netherlands
    Posts
    61
    In a form with VBA:

    you need 2 textboxes if you want them to query between 2 numbers
    and 1 listbox to list all the results

    So ones TicketStart the others TicketEnd
    Here are the 2 textboxes

    private sub TicketStart_afterUpdate()
    Call sBuildSQL
    end sub

    Private TicketEnd_AfterUpdate()
    Call sBuildSQL
    end sub

    sub sBuildSQL
    Dim strWhere as string
    dim db as database
    dim rs as recordset
    If not isNull(me.ticketstart) and not isnull(TicketEnd) then
    strwhere="select * from yourtable where " & application.buildcriteria(yourTable.[ticket],dbInteger,me.ticketstart) & " And " & application.buildcriteria(yourTable.[ticket],dbInteger,me.ticketend)
    end if
    set db=currentdb()
    With Me.YourListbox
    Set rs = db.OpenRecordset(strWhere)
    If rs.RecordCount > 0 Then
    .RowSourceType = "Table/Query"
    .RowSource = strWhere
    .Enabled = True
    'display * fields
    .ColumnCount = NumberOfCollumns
    .ColumnHeads = True
    Else
    .ColumnCount = 1
    .RowSourceType = "Value List"
    .RowSource = "No records found."
    test1 = 1
    End If
    End With
    End If
    end sub


    If you got a error contact me on MSN: ms_mystic_gohan@hotmail.com

  3. #3
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90

    Re: Query Help

    Hi Philip,

    If the ticket numbers are a text field use: "Ticket >='" & left$(txtTicket.Text & "00000000",8) & "' and Ticket <= '" & left$(txtTicket.Text & "99999999",8) & "'".

    If the ticket numbers are a numeric field use: "Ticket >= " & Val(Left$(txtTicket.Text & "00000000",8)) & " And Ticket <= " & Val(Left$(txtTicket.Text & "9999999",8)).

    Where txtTicket is the user textbox input field.


    Good Luck,




    Last edited by Bruce A. Baasch; 11-08-02 at 12:19.
    Bruce Baasch

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •