Run Query from Form

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

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#
Click to expand...

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
 
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
Click to expand...
<CDate([Forms]![FormName]![ControlName] + #6:00:00 AM#)
 
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
Click to expand...
<CDate([Forms]![TEST]![DATE]+#8:00:00 PM#)
Any ideas?

Thanks,

Jeff

John Spencer (MVP) said:
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
Click to expand...
<CDate([Forms]![FormName]![ControlName] + #6:00:00 AM#)
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:


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
Click to expand...
Click to expand...
 
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.
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
Click to expand...
<CDate([Forms]![TEST]![DATE]+#8:00:00 PM#)
Any ideas?

Thanks,

Jeff

John Spencer (MVP) said:
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
Click to expand...
<CDate([Forms]![FormName]![ControlName] + #6:00:00 AM#)
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
Click to expand...
Click to expand...
Click to expand...
 
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) said:
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.
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
Click to expand...
<CDate([Forms]![TEST]![DATE]+#8:00:00 PM#)
Any ideas?

Thanks,

Jeff

John Spencer (MVP) said:
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.
Click to expand...
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.
Click to expand...
right
now I
have to open the query in design mode and manually have to change
Click to expand...
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
Click to expand...
dates.
I
have Access 2002.

Thanks for the help.

Jeff
Click to expand...
Click to expand...
Click to expand...
 
John,

Ok reading my post I think I see something with the SQL. It is adding a
date next to the time (an 1899 date) so i tried fixing directly in the sql
statement but it keeps putting it back in there. I did do as you suggestted
and turned off all auto correct. So know I am really baffled.

Jeff

John Spencer (MVP) said:
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.
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
Click to expand...
<CDate([Forms]![TEST]![DATE]+#8:00:00 PM#)
Any ideas?

Thanks,

Jeff

John Spencer (MVP) said:
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.
Click to expand...
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.
Click to expand...
right
now I
have to open the query in design mode and manually have to change
Click to expand...
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
Click to expand...
dates.
I
have Access 2002.

Thanks for the help.

Jeff
Click to expand...
Click to expand...
Click to expand...
 
You haven't declared the parameter, so it is possible that Access/SQL is not
properly interpreting the type of Forms!Test!Date and is seeing that as
something like: 10 Divided by 1 Divided by 2004 which is a small fraction. It
then adds that small fraction to the datetime.

PARAMETERS [Forms]![TEST]![DATE] DateTime;
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,

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) said:
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.
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

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top