Re: Run Query from Form

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jeff (jefffby_at_yahoo.com)
Date: 10/01/04 

Date: Fri, 1 Oct 2004 14:20:41 -0500

John,

Here is what my sql reads as requested. Just wondering (not that it is now
working even the first time that i push the button) but my code of the
button is Docmd.OpenQuery "Delivered" would there need to be anything to
tell it to requery if i change the info in the text box since the query was
already opened once? Also under the format for the text box should it be
blank or should it be set to Short? I have tried both ways with the same
result. I know we have in code to change to date.

SELECT dbo_HistoryOrderSerial.cPartNo, dbo_HistoryOrderSerial.cSerial,
dbo_HistoryOrderSerial.DateDelivered, dbo_HistoryOrderSerial.cPurchaseOrder,
dbo_HistoryOrderSerial.nInvoiceCost, dbo_HistoryOrderSerial.cInvoiceNo,
dbo_Inventory.nInvoiceCost, dbo_HistoryOrderSerial.dCreate,
dbo_HistoryOrderSerial.cSystemUser
FROM dbo_HistoryOrderSerial LEFT JOIN dbo_Inventory ON
dbo_HistoryOrderSerial.cPartNo = dbo_Inventory.cPartNo
WHERE
(((dbo_HistoryOrderSerial.dCreate)>CDate([Forms]![TEST]![DATE]+#12/30/1899
6:0:0#) And
(dbo_HistoryOrderSerial.dCreate)<CDate([Forms]![TEST]![DATE]+#12/30/1899
20:0:0#)))
ORDER BY dbo_HistoryOrderSerial.cPartNo;

"John Spencer (MVP)" <spencer4@comcast.net> wrote in message
news:415DA0E7.B3E6065E@comcast.net...
> Hmm, that is strange. If it ran once, I would expect it to run the next
time
>
> Try switching to the SQL view and post your SQL statement of your query.
> Perhaps someone will see what is going on.
>
> You might try explicit declaration of your parameter.
>
> Open the query in design mode
> Select Query: Parameters from the Menu
> Fill in the EXACT name of the parameter in column 1
> Select the data type of the parameter in column 2
>
> Also, you might try turning off (if it is on)
> - Track name AutoCorrect should be off
> (Tools: Options: General Uncheck all name AutoCorrect features)
>
> It sometimes seems to cause this type of wierd problem.
>
> Other than that, post the SQL statement.
>
> Jeff wrote:
> >
> > Thanks John,
> >
> > I put this into the criteria field of my query and run it from the form.
It
> > ran correctly once the it started giving me this error code:
> > RUN TIME ERROR '2001:
> > YOU CANCELED THE PREVIOS OPERATION.
> > If i try to run it from the query window with the form open, I get the
> > following error message.
> > THE EXPRESSION IS TYPED INCORRECTLY, OR IT IS TOO COMPLEX TO BE
EVALUATED.
> > I am not sure why it would work correctly once then start giving me
these
> > errors, i did not change anything between the different attempts except
for
> > the date in the text box. I tried closing the Application and
restarting
> > but it is still giving me the same error. I can type into the immediate
> > widow and it returns the right value that is in the text box. Here is
the
> > code that I have in the criteria field:
> > >CDate([Forms]![TEST]![DATE]+#6:00:00 AM#) And
> > <CDate([Forms]![TEST]![DATE]+#8:00:00 PM#)
> > Any ideas?
> >
> > Thanks,
> >
> > Jeff
> >
> > "John Spencer (MVP)" <spencer4@comcast.net> wrote in message
> > news:415C8F00.CCF521A@comcast.net...
> > > If you have the date on a form in a control, you should be able to use
> > something
> > > like the following
> > >
> > > >CDate([Forms]![FormName]![ControlName] + #6:00:00 AM#) AND
> > > <CDate([Forms]![FormName]![ControlName] + #6:00:00 AM#)
> > >
> > > Jeff wrote:
> > > >
> > > > Hi,
> > > >
> > > > I am not sure if I am posting to the right group so if I am not
please
> > > > direct me to the right group. I have a query that I made in query
> > design
> > > > mode and it works fine. the query is based on a Date/Time field.
the
> > code
> > > > in the criteria field reads:
> > > >
> > > > >#9/29/2004 6:00:00 AM# And <#9/29/2004 8:00:00 PM#
> > > >
> > > > I always run this query by changing the Date and not the time.
right
> > now I
> > > > have to open the query in design mode and manually have to change
this,
> > what
> > > > I would like to do is run this from a form, but I am not sure how to
 do
> > > > this. I would like to put a date picker on a form to choose the
dates.
> > I
> > > > have Access 2002.
> > > >
> > > > Thanks for the help.
> > > >
> > > > Jeff

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)