Hi Everyone,
I'm trying to run an append query with a criteria on a date/time field based 
on a start and end date-time that I compose in VBA on a form.

The query has for criteria on the date/time field:

>=[Forms]![oneoclock]![Startdt] And <=[Forms]![oneoclock]![Enddt]

In the form VBA, as an example:
Private Sub Form_Open(Cancel As Integer)
Dim Startdt As String
Dim Enddt As String
Dim dummy As String
Dim stSocName As String
dummy = "31/10/2007"
Startdt = dummy & " 09:00:00"
Enddt = dummy & " 09:15:59"
stDocName = "pull_test"
      DoCmd.OpenQuery stDocName
MsgBox "Done with query"
End Sub


The append query appends nothing, but if I run the query by itself, and 
manually enter the values 31/10/2007 09:00:00 and 31/10/2007 09:15:59  when 
it prompts me for Startdt and Enddt, I get my data.

Where am I going wrong?
Many thanks in advance for your kind assistance,
Pat.

The following is working in Northwind (Access 2003) :


Make a new form, tempForm, with two edit box, text0 and text2. Have the 
code:

--------------
Option Compare Database
Option Explicit

Private Sub Form_Load()
    Me.Text0 = #7/10/1996#
    Me.Text2 = #7/17/1996#
    DoCmd.OpenQuery "query8"
End Sub
---------------


Save. Close the form.

Define a new query, query8, with the SQL view:


--------------
SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, 
Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, 
Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, 
Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry
FROM Orders
WHERE (((Orders.OrderDate)>=[FORMS]![tempForm]![text0] And 
(Orders.OrderDate)<=[FORMS]![tempForm]![text2]));
--------------

Save. Close the query designer.


Open the form.

You have the filtered data.




Vanderghast, Access MVP


"Pat Backowski" <PatBackowski@discussions.microsoft.com> wrote in message 
news:D2F48341-1D97-40E2-9C3E-E86DD8D6482D@microsoft.com...
> Hi Everyone,
> I'm trying to run an append query with a criteria on a date/time field 
> based
> on a start and end date-time that I compose in VBA on a form.
>
> The query has for criteria on the date/time field:
>
>>=[Forms]![oneoclock]![Startdt] And <=[Forms]![oneoclock]![Enddt]
>
> In the form VBA, as an example:
> Private Sub Form_Open(Cancel As Integer)
> Dim Startdt As String
> Dim Enddt As String
> Dim dummy As String
> Dim stSocName As String
> dummy = "31/10/2007"
> Startdt = dummy & " 09:00:00"
> Enddt = dummy & " 09:15:59"
> stDocName = "pull_test"
>      DoCmd.OpenQuery stDocName
> MsgBox "Done with query"
> End Sub
>
>
> The append query appends nothing, but if I run the query by itself, and
> manually enter the values 31/10/2007 09:00:00 and 31/10/2007 09:15:59 
> when
> it prompts me for Startdt and Enddt, I get my data.
>
> Where am I going wrong?
> Many thanks in advance for your kind assistance,
> Pat.
> 

Thanks, Michael.

The clue I needed was using text boxes rather than variables.

For some reason, however, the # got in the way.  With the #, it all worked.

Well Done and Thank you.....
Pat.