Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Entering date from form into query (2002)

    Hi,

    I have a query that I am going to use as an append query. I would like the query to include the date range that is on frmStrategicPlanning. I tried putting in [txtStartDate] into the query and then used [forms]![frmStrategicPlanning]![txtStartDate] to defined what it should be but I keep gettting parameter questions. I defined it as a date but still get the question. Before I play with this longer, is there a way to pull in the date on a form into the query, not just use it to filter the query?

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Entering date from form into query (2002)

    It should be possible to use a parameter referring to a control on a form in an append query. Some questions:
    - Are you sure that txtStartDate is the name of the date field in the table?
    - Are you sure that frmStrategicPlanning is open when you try to run the query?
    - If so, is frmStrategicPlanning open as a "main form", or is it a subform on another form?

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Entering date from form into query (2002)

    Hi Hans,

    To answer your questions, txtStartDate is the name of the textbox on frmStrategicPlanning. It's format is shortdate. I put a field in the query called txtStartDate, put [forms]![frmStrategicPlanning]![txtStartDate] as the criteria and then defined it as a date parameter. I still got parameter questions. Yes, the form is open and it is a main form, not a subform. I thought I had my bases covered. I did check to see if the spelling was correct as it appears to be.

    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Entering date from form into query (2002)

    You write "txtStartDate is the name of the textbox on frmStrategicPlanning (...) I put a field in the query called txtStartDate". That doesn't answer my first question, so I will repeat it:

    Are you sure that txtStartDate is the name of the date field in the table?

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Entering date from form into query (2002)

    The field in the table is called txtStartDate as well. I thought consistency was best.

    I had a field in the query written this way ....txtStartDate: [txtStartDate] and the criteria was set to the strategic planning form. I changed it to txtStartDate:[forms]![frmStrategicPlanning]![txtStartDate] and that got rid of the [parameter question and fixed the problem.

    Leesha

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Entering date from form into query (2002)

    I was confused about the names because it is rather unusual to give a date field a name starting with txt. The prefix txt is used to designate text boxes on forms and reports. But the important thing is that you solved the problem (I hope that you have checked that the query operates as intended now.)

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Entering date from form into query (2002)

    Giggling.............I started it off with txt due to someone (can't remember who or when) in the lounge advising me to do so when giving names to text boxes and since the text box on the form is txtStartDate, I named everything that way in an effort to try to narrow down where the problem was coming from.

    I am now trying to limit/protect the table from duplicate data using ..Not in (SELECT DISTINCT txtStartDate FROM tblPIHours

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Entering date from form into query (2002)

    Can you post the full sql of your query?
    Regards
    John



  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Entering date from form into query (2002)

    Hi!

    I was just about to post back to Hans that I figured out the issue. The code works in other queries and I rechecked and rechecked the spelling, which was correct. Then I wondered if the % in the table name could be throwing things off. So on a whim I took out % and changed it to the word percent and then fixed the Select Distinct code accordingly and it worked! I figured I'd post what I found in case it helps someone else.

    Thanks for the offer to look at the code.

    Leesha

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Entering date from form into query (2002)

    It is very important to avoid punctuation and characters like *, ?, % and # in your object names. These have special meanings in VBA and SQL and you will save yourself a great deal of headache by sticking to straight alphanumeric characters, with no punctuation.
    Charlotte

Posting Permissions

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