Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2013
    Posts
    3

    Unanswered: Multiple input boxes query

    Hello,

    Are there any ways to create multiple input boxes (form modules)
    and then run a query, based on their values?

    extract data matching:
    Code:
     ("value 1" AND "value 2" AND "value 3" AND ...)
    If the form/input box is empty, its value won't be used in the query.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need to build a query on the fly, its all down to the where clause eg:-
    WHERE astringcolumn = 'avalue' and anumericcolumn = 1234 and a datecolumn = #2103/12/15#
    if you use a filter rather than a query then drop the WHERE bit

    numeric literals (values) should be undelimited
    string/text literal sshould be quoted eg = 'blah' or = "blah", but dojn't mix delimters, 'OK', "NOT OK'
    dates must be in ISO yyyy/mm/dd or US format mm/dd/yyyy and delimted with a #

    building a where clause
    Code:
    strWhereClause = "1=1" ' takes advantage of a quirk of SQL 1=1 is always true so the where clause will alwasy be valid. if you are using as a filter then you can't do this
    
    if len(mystringcontrol)>0 then 
      strWhereclause =   strWhereclause & " AND mystringcolumn = '" & mystringcontrol & "'"
    endif
    if isnumeric(mynumericcontrol) then
      strWhereclause =   strWhereclause & " AND mynumericcolumn = " & mynumericcontrol
    endif
    if isdate(mydatecontrol) then
      strWhereclause =   strWhereclause & " AND mydatecolumn = #" & format(mydatecontrol,"yyyy/mm/dd") & "#"
    endif
    'build the SQL statement
    strSQL = "SELECT my,column,list from my table " & strwhereclause
    ..then do whatever with your sql

    you could put other validation code in eg:-
    Code:
    if isnumeric(mynumericcontrol) AND mynumericcontrol > 100 then
      strWhereclause =   strWhereclause & " AND mynumericcolumn = " & mynumericcontrol
    endif
    building a filter clause
    Code:
    strfilter = ""
    
    if len(mystringcontrol)>0 then 
      strfilter =   strfilter & " AND mystringcolumn = '" & mystringcontrol & "'"
    endif
    if isnumeric(mynumericcontrol) then
      strfilter =   strfilter & " AND mynumericcolumn = " & mynumericcontrol
    endif
    if isdate(mydatecontrol) then
      strfilter =   strfilter & " AND mydatecolumn = #" & format(mydatecontrol,"yyyy/mm/dd") & "#"
    endif
    'however assuming the user has set some values we have a statement 
    starting with ' AND '
    'so check if we have anything valid in our filter
    if len(strfilter) > 0 then ' we have something in our filter
      'chop off the leading 5 characters (the ' and ')
      strfilter = mid(strfilter,6)
      me.filter = strfilter
      me.fitleron = vbtrue
    else 'the filter was blank so turn it off
      me.filteron = false
      me.filter = ""
    endif
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2013
    Posts
    3
    thank you very much for your detailed reply.

    anyway, it reminded me of PHP + MySQL coding!

    what would be the difference between using Access or PHP+Mysql,
    considering my actual needs already explained here ?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    access uses vba, php uses php
    access usually targets pc via a copy of Access or Access runtime
    PHP usually targets computers via a web client
    Access, like |PHP can talk to mnay different types of database not just the default storage mechanism

    accesss talking to its default internal db has probelms with around 15..30 concurrent users

    Access doesn't scale well, especially over a WAN.

    Access has a complet IDE that can do a lot of the grunt work in small applications. PHP yu have to do virtually everything yourself from scratch althgough some of the frameworks aree supposed to do some of the donkey work. a well crafter Access application talking to a server db is scaleable but you loose a lot of the dinky features that make it attractive to small scale application designers


    as suggested here. if it were me I'd use Access, to keep costs down I'd have one copy of Access and use the runtime to deploy the finished app. but whether thats right for you I dunno as it depends on your skills, knowledge and enthusiasm to learn.

    but you have hardly identified your requirements. at the current time virtually any language, virtually any datastorage will satisfy what you have identified so far.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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