Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jul 2003
    Posts
    25

    Question Unanswered: Append and parameter queries

    Hi,

    I have an append query and a parameter that asks for an id to query. However, the query is not able to find any matches, but when I do it manually i.e put it in the query design grid , it works fine.

    Can anyone please advise - plus how do you use a text box on a form as the criteria for the query?

    Any help welcome

    Thanks

    Phil

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    in your design grid, click the criteria box once and then the magic wand builder in the main menu... navigate thru forms; all forms; the form with the text box; and dbl-click the text box. your query now has your textbox as criteria.

    you do not need to mess with parameters!

    izy

  3. #3
    Join Date
    Jul 2003
    Posts
    25

    Unhappy Append query

    Hi,

    Thanks for the reply.

    I have done what you said, but when I put the relevant text to search the query and then run the query using a command button, the append query still finds 0 rows to add. But when I enter in manualy in the criteria it works fine.

    WHAT DO I DO ------------ HELP...................................?

    Regards

    Phil

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    strange.....................

    going from here to anywhere sensible is going to be tough without some more info.

    open your query in SQL view and copy/paste the string into the forum.
    also tell us:
    -the name of the form (parent...child names if it's a subform)
    -what datatype is the criteria field (longInteger??)
    -the textbox value that works as an immediate criteria and fails from a form
    -a screen dump of msgbox "{" & yourTextBox.value & "}" with the above value entered.

    idle thought: do you have any spaces in field/form/textbox names?

    izy

  5. #5
    Join Date
    Jul 2003
    Posts
    25

    Exclamation Append Query

    Hi,

    As I have links to other DBs on the Network, is it possible for you to attach an example so that I can see what you have done to make it work.


    (Sorry for the late reply, I have just come back from an hoilday)

    Thanks, much appreciated,


    Regards


    Phil

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    see .ZIP

    izy

  7. #7
    Join Date
    Jul 2003
    Posts
    25

    Unhappy Append Query

    Thanks for the example.

    Having tried the same with my DB it works fine when I search for a number i.e. 8773, but it doesn’t recognise it when I enter any text e.g. help even though I know it is there. Any ideas, thanks.


    Regards,


    Phil

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Phil,

    Please give us the information izyrider requested:


    open your query in SQL view and copy/paste the string into the forum.
    also tell us:
    -the name of the form (parent...child names if it's a subform)
    -what datatype is the criteria field (longInteger??)
    -the textbox value that works as an immediate criteria and fails from a form
    -a screen dump of msgbox "{" & yourTextBox.value & "}" with the above value entered.

    Also, are you sure you are using a TEXTBOX and not a COMBO BOX or LIST BOX? The latter two can contain multiple columns and you may not be referencing the correct one.

    blindman

  9. #9
    Join Date
    Jul 2003
    Posts
    25

    Append Query

    Here we go blindman,

    Cheers


    Phil
    Attached Files Attached Files

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here's my zip again, this time using the text field.

    izy

  11. #11
    Join Date
    Jul 2003
    Posts
    25

    Thumbs up Append Query

    Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you,

    ....... it now WORKS.

    everyone who has contributed this is much appreciated


    Phil

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your SQL code includes an * in the select list, though your query designer example does not. This may be causing the problem, though it would normally generate an error.
    By including the * you are trying to insert more than three values into three fields, and you are also duplicating field names. Try removing the * from your code and see if it works. It will at least remove one possible issue.

    Also, from your Query designer window, view your SQL code directly and make sure it matches the code in your form.

    Also, try substituting [Forms]![FRM_TBLFAIR_TREE]![Test] for your "PLY4369" criteria. (You will need to have the form open in the background when you run the query.)

    blindman

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    withdrawn for some edits: back later. izy
    Last edited by izyrider; 07-15-03 at 14:01.

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    OK Phil, good we got something working.

    as you probably saw in the second zip, the problem is that your textbox entry doesn't have the padding blanks that are in the field data.

    the demo
    Like "*" & yourText & "*"
    is dangerous! a foolish user might type "LY" and then your appends will include:
    PLYanything
    FLYanything
    Mildly
    sly and the family stone
    ...etc

    this may not be the effect you are looking for.

    safer is to pad yourText with spaces.

    see another (& final ???) zip using space-padding.

    izy

  15. #15
    Join Date
    Jul 2003
    Posts
    25

    Question Append Query

    Blindman,

    I have tried to delete the * but all I get is an error message.

    IZYrider,

    I have tried your code and it seems ok, but I have noticed you have included “mytext” within it. As I am viewing the data through a datasheet subform format, I do not know what I should replace “myText” with. Any advice is welcome.


    Thanks again guys

Posting Permissions

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