Date selection from form VBA

[Pat Backowski]

Hi Everyone,
I'm trying to run an append query with a criteria on a date/time field based

=[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.

 

[Michel Walsh]

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

Hi Everyone,
I'm trying to run an append query with a criteria on a date/time field
based

=[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.

 

[Pat Backowski]

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.