Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Unanswered: Using variable in a FORMS listing

    Hello everybody,
    is it possible to use variable in the FORMS listing?

    I made global varialble to store the form name [glb_AnyFormName]

    how can I use something like that in a SELECT statement

    SELECT paz.paz_pgd_join_id, paz.paz_serial, paz.paz_tarif_std, paz.paz_tz_art, paz.paz_von, paz.paz_bis
    FROM paz
    WHERE (((paz.paz_bis) Between [forms]![my_FormName]![txt_ausVon] And [forms]![my_FormName]![txt_ausBis]));

    Note that my_FormName is here not a variable but the name of the form.

    My question here, can I substitute my_FormName with the global variable glb_AnyFormName

    I tried something like that but invain

    SELECT paz.paz_pgd_join_id, paz.paz_serial, paz.paz_tarif_std, paz.paz_tz_art, paz.paz_von, paz.paz_bis
    FROM paz
    WHERE (((paz.paz_bis) Between forms(glb_AnyFormName)![txt_ausVon] And forms(glb_AnyFormName)![txt_ausBis]));

    Thanks for yur help....

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Hi,

    What you need to do is to concatenate the value of your variable into the SQL string, and preferably put that into a variable too. However, you will NOT be able to do this if you are simply using the SQL in a Select query. But you can construct the SQL string in VBA and then use it as a RecordSource for a form or report, or the RowSource for a ComboBox or ListBox.

    For example:

    Dim MySQL as String

    MySQL = "SELECT paz.paz_pgd_join_id, paz.paz_serial, paz.paz_tarif_std, paz.paz_tz_art, paz.paz_von, paz.paz_bis
    FROM paz
    WHERE (((paz.paz_bis) Between [forms]!" & my_FormName &"![txt_ausVon] And [forms]![my_FormName]![txt_ausBis]));"

    You now have an SQL string that has the name of your form, stored in my_FormName, embedded in it. The advantage of putting the SQL into a string variable is that you can Debug.Print it or display it in a messagebox in order to check its value. I hope this helps.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Thumbs up

    Thanks allot, I'll try it now, I think it will functions.
    Thanks again

  4. #4
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Sorry, I forgot the second variable. Amended version:

    Dim MySQL as String

    MySQL = "SELECT paz.paz_pgd_join_id, paz.paz_serial, paz.paz_tarif_std, paz.paz_tz_art, paz.paz_von, paz.paz_bis
    FROM paz
    WHERE (((paz.paz_bis) Between [forms]!" & my_FormName &"![txt_ausVon] And [forms]!" &my_FormName &"![txt_ausBis]));"


    Good luck!!
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

Posting Permissions

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